I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transaction log backup is added to the backup device.
When restoring this onto a new server using SSMS I first create a new database, then add the backup device and then restore from that backup device. I then have to manually select the applicable files:
- The Full backup (always the first one in the list)
- The latest differential backup
- All transaction log backups that came after the last differential backup.
This works fine for the occassional restore, but I need to automate this in T-SQL.
There is some logic on which files to pick, but I don't even know how to get to the content of the backup device yet.
I would like to have a stored procedure that takes the name of the database and the backup device and restores the latest version. Anybody knows of such a thing?
Or do I miss something here and there is a simple command for this?
Best Answer
This procedure should do what you want:
execution goes:
get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device
Then build up a union query consisting of:
The last full database backup (type=D) in the device
the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.
the transaction logs (type=L) taken after the above diff.
returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)
Loop through the result restoring them individually, and then a final restore with recovery to finish it off.
Call it as:
I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.
Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.