none
Seeking to restore a single database from a set of several bak files

    Question

  • Hello - we have a large SQL db that we split into 4 separate bak files in a nightly backup so it can be more easily sent offsite. We use a statement like this (db names changed)

    BACKUP DATABASE [Data] TO DISK = 'd:\back\data1.bak', DISK = 'd:\back\data2.bak', DISK = 'd:\back\data3.bak', DISK = 'd:\back\data4.bak' WITH  INIT ,  NOUNLOAD ,  NAME = N'Data backup',  NOSKIP ,  STATS = 10,  NOFORMAT

    I want to be able to restore these four backups into a different database for testing.  I found a t-sql script on stack overflow but don't know if this would work.  Can someone help?

    The adapted script would be as follows

    RESTORE DATABASE Data_test from
    DISK = 'd:\back\data1.bak',
    DISK = 'd:\back\data2.bak',
    DISK = 'd:\back\data3.bak',
    DISK = 'd:\back\data4.bak'
    with
    move 'Data_Test1' to 'D:\SQLDb\Data_Test1.mdf',
    move 'Data_Test2' to 'D:\SQLDb\Data_Test2.ndf',
    move 'Data_Test3' to 'D:\SQLDb\Data_Test3.ndf',
    move 'Data_Test4' to 'D:\SQLDb\Data_Test4.ndf',
    move 'Data_Testlog1' to 'C:\SQLtlogs\Data_test1.ldf'

    Do you think this would work?  And would the file names be those for the est db, not the production db from which the backups are made?  Any help would be great, thanks.


    Wednesday, August 21, 2013 2:24 PM

Answers

  • Hello,

    Yes, looks like a correct restore statement for a "striped backup" as a new database with new database filenames.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 21, 2013 2:51 PM
  • Hi,

    Yes, it should work.  The database will be restored as 'Data_test' and the files on disk will be called Data_Test1.mdf, etc.

    In SSMS, if you start a restore, select all the files from the backup set, do the renaming of physical files, etc. tick the right boxes, you can then click the 'Script' button at the top of the window to see what the TSQL is like.  You can then edit it from there.


    <br/> Thanks, Andrew <br/>

    Wednesday, August 21, 2013 2:51 PM

All replies

  • Hello,

    Yes, looks like a correct restore statement for a "striped backup" as a new database with new database filenames.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 21, 2013 2:51 PM
  • Hi,

    Yes, it should work.  The database will be restored as 'Data_test' and the files on disk will be called Data_Test1.mdf, etc.

    In SSMS, if you start a restore, select all the files from the backup set, do the renaming of physical files, etc. tick the right boxes, you can then click the 'Script' button at the top of the window to see what the TSQL is like.  You can then edit it from there.


    <br/> Thanks, Andrew <br/>

    Wednesday, August 21, 2013 2:51 PM