October 1, 2009 at 10:26 am
Hi ALL,
Our database get's a total backup at the weekend then each week day has an incremental backup. One of the developers accidently deleted the Database.
We have been able to restore from the total backup and apply the first incremental backup taken after the full backup. Each time we try to restore another incremental to bring us closer to today if fails. Not being a DBA, we thought that perhaps an incremental backup takes a snapshot based on the Last full backup, so we tried again from scratch by applying the Full backup followed by the latest incremental, but the incremetal still failed. We didn't receive any fail messages at the time the backups where being taken.
The error message we receive is:
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 xx
Is there anyway we could pull ot individual SP's and Functions from the incremental backups on a worse case scenario as long as we know the object names?
Any help would be great.
October 1, 2009 at 10:56 am
william.crawley (10/1/2009)
The error message we receive is:
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 xx
When you do your restore from the first full backup, are you using the RESTORE DATABASE....WITH NORECOVERY clause? If you refresh your db view in SSMS after the restore, do the words (In Recovery) appear, along with the database cannister being grey? If you restore your database (and recover it) immediately after the first full backup is restored no further backups can be applied.
Make sure you apply all diffs/transaction logs, etc (each with the NORECOVERY keyword at the end) and then when all of those have been applied successfully, use the RESTORE DATABASE foo WITH RECOVERY; statment to get it back online and available.
IIRC the error message you're reporting correlates to the above-described scenario.
MJM
October 1, 2009 at 12:09 pm
william.crawley (10/1/2009)
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 xx
Sounds like you're either restoring the wrong full backup or there was an unexpected full backup made before the differential that's failing (SQL doesn't have incremental backups).
A differential is based on the last full backup that ran before the differential. That full backup is the one that has to be restored. To restore diffs you restore the full backup and then the last differential. There's no need to restore them one by one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2009 at 12:15 pm
Mark Marinovic (10/1/2009)
Make sure you apply all diffs/transaction logs, etc (each with the NORECOVERY keyword at the end) and then when all of those have been applied successfully, use the RESTORE DATABASE foo WITH RECOVERY; statment to get it back online and available.IIRC the error message you're reporting correlates to the above-described scenario.
Trying to restore atop a DB restored with recovery gives the error message (at least in SQL 2008)
Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2009 at 1:12 pm
You could run something like this to get a listing of your backup history. Then you can see if you are missing any differentials or transaction logs:
SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type
,(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago'
,ceiling(bkup.backup_size /1048576) as 'Size Meg'
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig'
,server_name, user_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours'
, first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -30, (getdate())) -- Last 30 days
AND sysdb.name = 'My_DB_Name'
ORDER BY sysdb.name, bkup.backup_finish_date desc
October 1, 2009 at 1:19 pm
GilaMonster (10/1/2009)
Trying to restore atop a DB restored with recovery gives the error message (at least in SQL 2008)Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Thank you kindly. I will cease writing responses immediately before meetings 🙂
MJM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply