Sql-server – How to restore multiple backups

backuprestoresql serversql-server-2008

I'm using ExpressMaint and Windows Scheduled Tasks to create weekly full and daily differential backups of some MS SQL Server 2008 R2 Express databases

Weekly fulls 

expressmaint -S (local)\SQLExpress -D ALL -T DB -R D:\Backup\Reports -RU WEEKS -RV 1 -B D:\Backup\Data -BU WEEKS -BV 4 -V -C

Daily Diffs 

expressmain -S (local)\SQLExpress -D ALL -T DIF -R D:\Backup\Reports -RU WEEKS -RV 1 -B D:\Backup\Data -BU days -BV 7 -V -C

When I come to restore these to a certain point, I have to restore each backup individually. Is there any way I can chain a series of backups into a single restore that will be re-played in the correct order?

When I try this, I get the error

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

The media loaded on "C:\Foo\Bar_FullBackup_20110130_2346.bak" is formatted to support 1 media families, but 2 media families are expected according to the backup device specification.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3231)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3231&LinkId=20476

Edit

I am using the restore dialog from the tasks menu when you right click on a database. From there, I select "Restore from device" and Add the .bak files I wish to restore from.

If I only add a single .bak file, I'm OK, if I add multiple files, I receive the error above.

Best Answer

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx

Related Question