Script to Restore Diffential Backup...

fiduntonfidunton Posts: 5
edited November 22, 2016 7:32AM in SQL Backup
Hello -

I have a scripting question for Restore Diffential Backups....

If I have a backup set consisting of 1 full backup and 2 differential backs like this:

FULL_BARNEY_20161115_090000.SQB
DIFF_BARNEY_20161116_090000.SQB
DIFF_BARNEY_20161117_090000.SQB

What syntax do I use in my restore script to specify the FULL and each Differential backup file ?

Here is sample script that would use for a restore if I had no differential files:

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [BARNEY]
FROM DISK = ''G:FULL_BARNEY.sqb'' WITH PASSWORD = ''Fred'', RECOVERY, DISCONNECT_EXISTING,
MOVE ''BARNEY_data01'' TO ''F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATABARNEY_data01.mdf'',
MOVE ''BARNEY_log01'' TO ''F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATABARNEY_log01.ldf'',
REPLACE"'

How does the script differ when there are differential backups? (The wizard parses the backup files to automatically determine what other files are needed according to the help docs..)

Thank you.
· Share on Twitter

Comments

  • fiduntonfidunton Posts: 5
    edited November 22, 2016 6:55AM
    This seemed to work well:

    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [testDiff2] FROM DISK = ''C:MSSQLBackupFULL_(local)_testDiff1_20161117_150411.sqb''
    WITH PASSWORD = '''', NORECOVERY,
    MOVE ''testDiff1'' TO ''F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData estDiff2.mdf'',
    MOVE ''testDiff1_log'' TO ''F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData estDiff2_log.ldf''"'
    GO

    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [testDiff2] FROM DISK = ''C:MSSQLBackupDIFF_(local)_testDiff1_20161117_150802.sqb''
    WITH PASSWORD = '''', RECOVERY, ORPHAN_CHECK"'
    GO
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You only need to restore the full backup and the latest differential backup. SQL Backup offers you a shortcut to that. Using your examples, you could do this:

    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [testDiff2] FROM DISK = [C:MSSQLBackup*.sqb] SOURCE = [testDiff1] LATEST_ALL WITH PASSWORD = [], NORECOVERY, MOVE [testDiff1] TO [F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData estDiff2.mdf],
    MOVE [testDiff1_log] TO [F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData estDiff2_log.ldf], ORPHAN_CHECK"'


    [C:MSSQLBackup*.sqb] - this makes SQL Backup scan all the files in the C:MSSQLBackup folder with the sqb extension. If you store all your backup files in that folder, it might take a while, so what we recommend is that you store each databases' backups in its each folder. E.g. EXEC master..sqlbackup '-sql "BACKUP DATABASE ... TO DISK = [C:MSSQLBackup<database><AUTO>] ...

    SOURCE = [testDiff1] - by default, SQL Backup will look for testDiff2 backups because that is the database we are restoring. To tell SQL Backup we want to use the backups from another database, use the SOURCE option.

    LATEST_ALL - we want to restore the database to the latest possible state, so using LATEST_ALL tells SQL Backup to look for the combination of full, differential, and transaction log backups that can restore the database to the latest possible state.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • fiduntonfidunton Posts: 5
    Thank you - I will give this a try. Thanks again!
    · Share on Twitter
  • fiduntonfidunton Posts: 5
    The script worked well. Thx.

    I did change NORECOVERY to RECOVERY so that the DB is left in an operational state:

    EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [testDiff2] FROM DISK = [C:MSSQLBackup*.sqb] SOURCE = [testDiff1] LATEST_ALL WITH PASSWORD = [], RECOVERY, MOVE [testDiff1] TO [F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData estDiff2.mdf],
    MOVE [testDiff1_log] TO [F:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData estDiff2_log.ldf], ORPHAN_CHECK"'

    Thank you again.
    · Share on Twitter
  • jackson321jackson321 Posts: 3 New member
    There is an example at the bottom of that page
    · Share on Twitter
Sign In or Register to comment.