Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Differential Backup issue

Author  Topic 

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-26 : 07:46:52
Hi,

This week I created a new backup strategy for one of our DBs. This includes a full backup every Monday and friday, a differential every other night, and log backups every hour during the day between 0800-2300.

I wanted to restore the DB to a test DB for the state on Tuesday night so I restored using the full backup from Monday night. This worked fine, but when I tried restoring the Tuesday differential I get an error saying it can't be restored becase it's out of sequence.

Is there anything obvious that could cause this?

It is definately the correct diff, so I'm a bit bemused as to why I'm getting the error. I can restore the first log backup from Monday morning (and the rest as well if I wanted to), but I thought the diff would contain every change since the last backup?

Any help would be much appreciated!

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-26 : 09:08:43
Sorry, the actual error message is:

The differential backup cannot be restored because the database has not been restored to the correct earlier state.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-26 : 09:24:16
Seems someone ran a full backup since Monday night.

What is the result of:
SELECT	MAX(backup_finish_date)
FROM msdb..backupset
WHERE database_name = 'Your Database Name'
And type = 'D'


Also, you did restore the full backup with NORECOVERY, right?
Go to Top of Page

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-26 : 09:35:07
They did indeed!

I was never told our online backup people do a full backup every night at 6.

Thanks for your help
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-26 : 09:50:26
Glad to help. You may want to have them use the WITH COPY ONLY option, as that will prevent it from breaking your backup chain.
Go to Top of Page
   

- Advertisement -