Conference amcucs::ms-sqlsvr

365.0. "Restoring a MSSQL database - how does it work" by HLFS00::ERIC_S (Eric Sonneveld MCS - B.O. IS Holland) Wed Apr 23 1997 23:56

    When reading the MSsql admin books about backup/restore they warn about
    the sizing of the DB devices (chapter 12, page 391). They tell us that
    when rstoring a backup it's importent to crerated the database in the
    same order as it was initial created.
    Example: 
    Create db datasize 10 Mb, logsize 10 MB
    Extend Db datasize 10 MB
    
    When restoring this DB we you need to create the db in the same way as
    above, that's what the books and training courses tell us.
    
    BUT:
    - I've had examples were an intial creation of a db on a have used
    database device did result on the first creation already in fragments.
    How should we recover this ? Recreate according the results of the
    SYSUSAGES table ?
    - I've tried to recreate a db using 5 db devices with all very small
    fragments were as the orginal db only had one data fragment and one log
    fragment. When restoring it all worked ok. The only thing to keep in
    mind is that the total recreated db size is greater or equal than the
    orginal db size.
    The restored database was acccesable and did contain all the data.The
    used data portion of the database was bigger than the first fragment
    created, so I'm sure it did span database device fragments.
    How can this have worked ?
    
    Talking about MSSQL V6.5
    
    Eric

>>    - I've had examples were an intial creation of a db on a have used
>>    database device did result on the first creation already in fragments.
>>    How should we recover this ? Recreate according the results of the
>>    SYSUSAGES table ?
    
    I believe Microsoft recommends that you use a device to contain space
    for only one database.  I think multiple databases per device is really
    necessary only when using raw partitions.
    
>>    - I've tried to recreate a db using 5 db devices with all very small
>>    fragments were as the orginal db only had one data fragment and one log
>>    fragment. When restoring it all worked ok. The only thing to keep in
>>    mind is that the total recreated db size is greater or equal than the
>>    orginal db size.
>>    The restored database was acccesable and did contain all the data.The
>>    used data portion of the database was bigger than the first fragment
>>    created, so I'm sure it did span database device fragments.
>>    How can this have worked ?
  
    This works because the backup utility dumps and restores on a page by
    page basis without regard to what is contained in the pages.  The
    issue of the fragments is that if you have log fragments mixed with
    data fragments the log pages will get restored to data fragments and
    vice versa.  If you carefully assign fragments so the sizes work out it
    is possible for it to restore correctly.
      
    	Al