Sunday, February 19, 2012

Error attempting restore of full/differential backup

I am running the following script to attempt a restore of a differential backup:

RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'
WITH
NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH RECOVERY
GO

I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:

Msg 3136, Level 16, State 1, Line 1

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")

The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'

The script for the differential backup is:

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT

All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. (Sorry, I'm primarily a programmer, not a DBA) Can anyone help?

Thanks

Are you running SQL 2000 or 2005, because you can't restore AdventureWorks on SQL 2000?|||I am running SQL 2005 -- sorry, forgot to mention.|||I discovered the problem. In my differential backup

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT

I should not be using "INIT".

I thought that since you just use the last differential backup file, it would make sense to overwrite it every time, so I put in the INIT. However, it doesn't work. You have to have all the differential files saved, even though you only use the last one.

It doesn't make sense to me, but it appears to be the answer, because when I do it that way the restore works.

No comments:

Post a Comment