Restore Differential without Full Backup
-
I have a SQL2008 database that was recently created. The full backups run once a week and did not run yet this week. The differential backups run everyday except the day the full backups run. So a few days after the database was created, some of the datafiles were accidentally deleted while trying to move them around on drives. The only backup I have of the DB is a differential. Is there any way to restore to a NEW database with the differential backup?
I've tried creating a new database then trying to restore to it and it did not work.
-
You're toasted.
anyway you have the corrupt datafiles? That's pretty much you're only shot of getting back online.
-
I hope you have off-site tapes. The scenerio you described will not result in recovery
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum! -
A restore sequence must always start with a full backup. All the differential has is the extents modified since the full. It's not restorable on its own.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
For an amazing crash-course on recovery (75 minutes by the guy who wrote the code for microsoft) :
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2008/DAT375
-
What happened to the full backups? A differential backup cannot have run unless a full had already run, so there must be one somewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
dwh-1095413 (8/10/2011)
So a few days after the database was created, some of the datafiles were accidentally deleted while trying to move them around on drives.That's the exact reason why I always recommend to copy the files. Instead of move. That can't happen with copy.
That being said, you say only a few days after creating the db. Do you still have the scripts that created the db? Maybe you could recover from hard copies of the data and reenter everything.
Very annoying but it works...
-
I thought the same thing but I have 2 days of differentials and no full.
GilaMonster (8/10/2011)
What happened to the full backups? A differential backup cannot have run unless a full had already run, so there must be one somewhere. -
Luckily there is a version of the DB on DEV. A few changes need to be applied to it again so it's not too bad. I told the developers to make sure they run a full backup when they create new databases now.
Ninja's_RGR'us (8/10/2011)
dwh-1095413 (8/10/2011)
So a few days after the database was created, some of the datafiles were accidentally deleted while trying to move them around on drives.That's the exact reason why I always recommend to copy the files. Instead of move. That can't happen with copy.
That being said, you say only a few days after creating the db. Do you still have the scripts that created the db? Maybe you could recover from hard copies of the data and reenter everything.
Very annoying but it works...
-
Well if the diffs ran it means you took at least 1 full. Track that untill you hear it's deleted by x on y date and it's not anywhere on tape or other backup procedure.
-
dwh-1095413 (8/10/2011)
Luckily there is a version of the DB on DEV. A few changes need to be applied to it again so it's not too bad. I told the developers to make sure they run a full backup when they create new databases now.Ninja's_RGR'us (8/10/2011)
dwh-1095413 (8/10/2011)
So a few days after the database was created, some of the datafiles were accidentally deleted while trying to move them around on drives.That's the exact reason why I always recommend to copy the files. Instead of move. That can't happen with copy.
That being said, you say only a few days after creating the db. Do you still have the scripts that created the db? Maybe you could recover from hard copies of the data and reenter everything.
Very annoying but it works...
Good news when you don't lost too much, except time!
-
P.P.S.
Told the devAs the DBA, decided to setup maintenance to always backup all dbs and make sure they are in full recovery and also backup the logs and test the backups everytime if possible, and that on all server. -
Ninja's_RGR'us (8/10/2011)
For an amazing crash-course on recovery (75 minutes by the guy who wrote the code for microsoft) :http://channel9.msdn.com/Events/TechEd/NorthAmerica/2008/DAT375
Nice link Ninja, thank you for sharing!;-)
-
Ignacio A. Salom Rangel (8/15/2011)
Ninja's_RGR'us (8/10/2011)
For an amazing crash-course on recovery (75 minutes by the guy who wrote the code for microsoft) :http://channel9.msdn.com/Events/TechEd/NorthAmerica/2008/DAT375
Nice link Ninja, thank you for sharing!;-)
Glad you liked it. This one should blow your mind... http://www.sqlskills.com/T_MCMVideos.asp
-
Ninja's_RGR'us (8/15/2011)
Ignacio A. Salom Rangel (8/15/2011)
Ninja's_RGR'us (8/10/2011)
For an amazing crash-course on recovery (75 minutes by the guy who wrote the code for microsoft) :http://channel9.msdn.com/Events/TechEd/NorthAmerica/2008/DAT375
Nice link Ninja, thank you for sharing!;-)
Glad you liked it. This one should blow your mind... http://www.sqlskills.com/T_MCMVideos.asp
Thanks again!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply