Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you show an example of the restore statement you are using? –  Eric Humphrey - lotsahelp Feb 2 '11 at 14:58
    
@Eric added a little more detail –  Greg B Feb 2 '11 at 15:01

3 Answers 3

up vote 8 down vote accepted

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

share|improve this answer

--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 
share|improve this answer

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.

share|improve this answer
    
How does this work when I'm restoring on a different server? Do I need to tell the database which files it considers it's backups? –  Greg B Feb 2 '11 at 16:37
    
You won't be able to use SSMS in that way, I don't believe. You will need to script out the restore commands as Eric has suggested above. –  SQLRockstar Feb 2 '11 at 18:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.