SQL Server, Restore Latest Differential

Question

We have a development server that has a job that runs overnight which restores backed up databases. Our primary database server backs up the databases overnight and our development server then restores these databases to keep them up-to-date with one another on a daily basis (not transactional basis).

As our databases are pushing many gigabytes in size we now face the problem that restores take many hours overnight. Instead we're wanting to push out the use of differential database backups and restores.

The problem I have is that the development server does a restore, but how can I tell which file to pick out in the differential backup? You have to specify a file i.e. FILE = 9 in the restore statement, but what if I don't know which file it is? What if I just want the latest one?

So how do you specify the latest file from a differential database backup?

http://msdn.microsoft.com/en-us/library/ms186858.aspx

Cheers

Answer 1

There are a couple of different options for automating this using a stored procedure / TSQL or a powershell script (I'd recommend the powershell script). If you want to write your own automation for doing this, you'll basically want to look into the "restore filelist only" statement which will give you a listing of all files in a given backup set (and in this case figure out which filenumber is the most recent diff backup for the full backup you have).

Note that if you want to restore the differential, you'll have to have restored the most recent FULL backup that is associated with the given DIFF backup as well in order for this to work, or potentially have left the restored dev database in a recoverable state.

For example, this would work:

• Restore full backup from Sunday on DEV
• Restore diff backup from Monday on DEV

this would work as well:

• Restore full backup from Sunday on DEV
• Restore diff backup from Tuesday or Wednesday or Thursday on DEV

however, this will not work:

• Restore full backup from Sunday on DEV
• Take full backup on Monday on PROD
• Restore diff backup from Tuesday on DEV

In this final case, you'd also have to restore the full backup from Monday on DEV before restoring the Diff backup from Tuesday would be allowed. There are exceptions (for example if the full backup on Monday was a COPY_ONLY backup and didn't break the diff backup chain). See this article for more details on using DIFF backups and the restore chain required for use.

Answer 2

You should be able to get the record of the latest differential backup and the path to it out of the msdb backup tables. But as noted above, if you want to restore a diff, you'll have to restore the full first. At this point, you're probably better off reconsidering a different solution like log shipping or snapshot replication, or reconsidering the requirements.