May 12, 2011 at 4:07 am
Hi,
I took a differntial backup of my Live DB. There was already a differential backup and I took one more differential backup on top of it.
The device shows it has 2 differential backup .
I retored my database with the differential backup , but my DB did not got updated with the latest backup.
Any way I can update my database with the latest differntial backup without retoring again.
Thanks,
Harsha
May 12, 2011 at 4:24 am
What exactly did you do (all the steps)?
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
May 12, 2011 at 4:41 am
1. Database was restored with full backup and then left in loading state.
2. There was a differential backup taken yesterday night and I took one more differential backup on the same device.
3. Restored database with the differential backup. But the database got restored with the one what was taken last night and not with the latest one.
May 12, 2011 at 4:55 am
harsha.bhagat1 (5/12/2011)
2. There was a differential backup taken yesterday night and I took one more differential backup on the same device.
Have you checked the filelistonly ? How many files are there ?
Which one is latest ?
Have you given the latest one?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 12, 2011 at 5:20 am
muthukkumaran (5/12/2011)
harsha.bhagat1 (5/12/2011)
2. There was a differential backup taken yesterday night and I took one more differential backup on the same device.Have you checked the filelistonly ? How many files are there ?
Which one is latest ?
Have you given the latest one?
i don't think filelistonly will give you the backup files info rather it will show you the files which sql server has backed up means the data files and the log files of the first backup taken in the backupset .
You should rather use headeronly option with restore command to check the number of backup files lying in the backup set and then look for backup type 5 which refers to differential backups.
The REstore headeronly command refers one Position field which refers to the positiion of your backup files within the backupset.
Restore the file with latest position.
like if the position refers to 3 then use the below query to restore from the latest differential backup.
restore database TEst
from disk ='backuppath'
with file=3
I think it should resolve your problem.
Sachin
May 12, 2011 at 5:30 am
Did you get any errors when you restored the second one?
Check the restore history (msdb) and make sure that you did restore the backups that you think you restored, not maybe the wrong diff.
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
May 12, 2011 at 6:23 am
Hi,
My problem is how will I apply the differential backup to my database once its restored .
The database is resotred with recovery .
At this moment I wan to update the DB with the latest differentital backup. Is there any way to apply the differential backup to my database if its not in Loading state?
Thanks,
Harsha
May 12, 2011 at 6:32 am
I'm reading the OPs problem differently here.
I think the OP did a full backup, then a differential (call it diff1) and another differential later (call it diff2).
He then restored the full backup followed by diff1.
He's now asking if he can just apply diff2 without having to restore the full backup again.
If I'm understanding the problem correctly, then the answer is NO if diff1 was restored WITH RECOVERY (which it presumably is because he's verified that data is missing).
You need to restore the full backup WITH NORECOVERY, followed by diff2.
May 12, 2011 at 7:44 am
Thanks Ian Scarlett, you understood my problem.
And thanks everybody for looking into my problem
By the way I am not HE I am SHE... 😀
May 12, 2011 at 8:08 am
harsha.bhagat1 (5/12/2011)
Is there any way to apply the differential backup to my database if its not in Loading state?
Nope. Recovering the database means that no more backups can be restored.
If you want to restore, check data and restore more, try restoring WITH STANDBY rather than WITH NORECOVERY. That allows read-only access to the DB.
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
May 12, 2011 at 8:44 am
harsha.bhagat1 (5/12/2011)
By the way I am not HE I am SHE... 😀
:blush:
May 13, 2011 at 2:08 am
You should rather use headeronly option with restore command to check the number of backup files lying in the backup set and then look for backup type 5 which refers to differential backups.
Yep you are right.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply