How to restore multiple backups

Question

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.

Answer 1

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

Answer 2

--Restore the most recent full backup

RESTORE DATABASE <DATABASE NAME>
FROM DISK = <Path to full backup>
WITH NO RECOVERY

--Restore the most recent differential backup

RESTORE DATABASE <DATABASE NAME>
FROM DISK = <Path to differential backup>
WITH NORECOVERY

--Restore all the transactional log backups which was taken after the most recent differential backup

RESTORE DATABASE <DATABASE NAME>
FROM DISK = <Path to first transactional log backup>
WITH NORECOVERY

. . . . .

RESTORE DATABASE <DATABASE NAME>
FROM DISK = <Path to last transactional log backup>
WITH RECOVERY 

Answer 3

You are trying to restore from a device, but what you really want to do is specify the 'From database' option in SSMS. The GUI should bring up the available backup files and you can select the full and differential files.