Script to Restore Diffential Backup...

fidunton

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.

fidunton

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

petey

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.

fidunton

Thank you - I will give this a try. Thanks again!

fidunton

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.

jackson321

There is an example at the bottom of that page