March 4, 2009 at 3:57 am
Hi,
I'm creating full DB backup at first day of each month. then I'm creating differential backup everyday. Now suppose its 14th day of month and I want to restore backup of 7th day to a new DB. I restore the full backup to a new DB with NORECOVERY option first. Then when I want to restore the differential backup of 7th day on the same DB, I'm getting following error:
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Could you tell me what I'm doing wrong?
March 4, 2009 at 4:08 am
have you restored the 6 earlier differential backups?
March 4, 2009 at 4:12 am
actually my main question is this, shall I choose the previous 6 backups and restore them one by one?
Then what if I want to restore the 29th differential backup? is there anything wrong with my backup plan?
March 4, 2009 at 4:17 am
I feel that there must be a FULL backup taken between the 1st and the 7th day. Please check the Backup History tables in MSDB to verify the same. The below query should help you to find that.
USE MSDB
DECLARE @DBNAME VARCHAR(25)
SET @DBNAME='DatabaseName'
SELECT A.BACKUP_FINISH_DATE,
B.Physical_Device_Name
FROM MSDB..BACKUPSET A, MSDB..BACKUPMEDIAFAMILY B
WHERE A.DATABASE_NAME=@DBNAME AND
A.TYPE='D' AND
A.MEDIA_SET_ID = B.MEDIA_SET_ID
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 4, 2009 at 4:24 am
yep. as far as i'm aware, the differntial backup only records data changes since the last differential. so if you need to restore to the 29th differential, you need to restore the full backup in norecovery, the previous 28 differentials in norecovery and then the 29th differential (in norecovery if you want to restore transaction logs or recovery if you want the database recovered). books online has a lot of info and describes it all in much better detail than i can...
March 4, 2009 at 4:33 am
I got my answer
Thanks all for your comments 🙂
March 4, 2009 at 4:36 am
peace2007 (3/4/2009)
actually my main question is this, shall I choose the previous 6 backups and restore them one by one?Then what if I want to restore the 29th differential backup? is there anything wrong with my backup plan?
If you predicting that kind of situation yes, you should change your backup plan and take frequent differential backups. If you want to go back to specific point in time, start taking log backups too.
If its a production system, the DR plan really depends on your business needs.
March 4, 2009 at 5:25 am
iainthepitman (3/4/2009)
http://msdn.microsoft.com/en-us/library/ms188230.aspx%5B/quote%5D
iainthepitman ...you are conveying wrong information!!!!!!!
Differential backup backs up data from last full backup...
if u have full backup at 1st of month and 2 diff backups one is at 2nd and another at 3rd of month then ur diff backup of 2nd is of no use coz 3rd backup hass all info ...u can try doing this on test server...
March 4, 2009 at 6:21 am
iainthepitman (3/4/2009)
yep. as far as i'm aware, the differntial backup only records data changes since the last differential. so if you need to restore to the 29th differential, you need to restore the full backup in norecovery, the previous 28 differentials in norecovery
Nope. That's how transaction log backups behave. Differentials store the changes since the last full, so only the last one needs restoring, then all the log backups in sequence.
Peace: Most likely someone took a full backup somewhere in between. Full backups reset the differential base, so you have to make sure you use the correct full backup. Best option, when taking ad-hoc full backups is to use the COPY_ONLY option, as that ensures that the full you're taking does not reset the differential base.
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
March 4, 2009 at 6:23 am
Yes, that is incorrect ... pls check http://msdn.microsoft.com/en-us/library/ms345448.aspx
At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.
March 4, 2009 at 10:08 pm
Peace: Most likely someone took a full backup somewhere in between. Full backups reset the differential base, so you have to make sure you use the correct full backup. Best option, when taking ad-hoc full backups is to use the COPY_ONLY option, as that ensures that the full you're taking does not reset the differential base.
Thanks Gail. That might be the problem, I'm sure developers take backups in between I have to ask them to do that with my coordination.
By the way, then why I get an error in restoring those differentials? Could you please take a look at my question at first message of this thread?
March 4, 2009 at 10:37 pm
peace2007 (3/4/2009)
Peace: Most likely someone took a full backup somewhere in between. Full backups reset the differential base, so you have to make sure you use the correct full backup. Best option, when taking ad-hoc full backups is to use the COPY_ONLY option, as that ensures that the full you're taking does not reset the differential base.
Thanks Gail. That might be the problem, I'm sure developers take backups in between I have to ask them to do that with my coordination.
By the way, then why I get an error in restoring those differentials? Could you please take a look at my question at first message of this thread?
A second full backup was taken between the 1 on the first and the differential taken on the seventh day.
March 4, 2009 at 10:40 pm
maybe;)
March 4, 2009 at 10:59 pm
Maybe you could run the backup set query that was posted above and know for sure.
March 4, 2009 at 11:10 pm
I'll do that:)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply