February 12, 2009 at 7:46 pm
I made a small mistake in my database today and decided to restore from a previous database backup. The only problem is the restore file I used was 5 months old! So I lost 5 months of critical data! I can't find any more recent backups. Is there a way to undo this restore?
Thanks
February 12, 2009 at 8:41 pm
Restore from the "just in case" backup of the database that you made just before you ran this restore.
February 13, 2009 at 2:10 am
Most chances that there is nothing that you can do. The main question is if you really didn’t do any backup for 5 months or if there are backups that were done during this time.
You can query the MSDB database in order to find out if there are any backups that are more recent then the one that you used:
select max(backup_start_date) from backupset where database_name = 'WriteDBNameHere'
If there isn’t any newer backup, then there is nothing you can do.
By the way the topic of this thread is stupid stupid stupid restore problem. I have to say that in my opinion the problem is not the restore. The problem is that there is a chance that a live operational database did not have any backup for 5 months and no one realized that.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 13, 2009 at 2:21 am
Magy (2/12/2009)
Is there a way to undo this restore?
Not unless you took a more recent backup.
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
February 13, 2009 at 10:20 am
Michael Valentine Jones (2/12/2009)
Restore from the "just in case" backup of the database that you made just before you ran this restore.
I have found that "just in case" is one of the most important SQL DBA skills.
next time:
If you just need some simple data recovered, restore your backup as a new database with a different name. I usually append the date of the backup such as MyDatabase_20081201 to remind me it's an old version from 2008-12-01. Then copy out the data you need.
Run daily full backups and verify they are completing
etc
February 13, 2009 at 10:28 am
That reminds me of those 2 procs... hoping you don't need to use them anytime soon :
exec sp_update_resume
exec sp_distribute_resume
February 13, 2009 at 10:29 am
The other way to restore that data is to manually reenter it... that is if you keep some sort of hard copies.
February 14, 2009 at 3:50 am
Ninja's_RGR'us (2/13/2009)
exec sp_update_resumeexec sp_distribute_resume
:hehe:
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
February 14, 2009 at 6:58 am
exec sp_update_resume
exec sp_distribute_resume
:w00t:
February 14, 2009 at 7:14 am
Ninja's_RGR'us (2/13/2009)
The other way to restore that data is to manually reenter it... that is if you keep some sort of hard copies.
I remember doing something like that for a client abotu 2 years ago. He had a db file (csv format). He then had a software build all the pages on his website (all static pages). Since the site was still up I was able to scrape it and rebuild the DB.
Maybe you have something like that available to you... just ask around and see.
February 14, 2009 at 9:37 am
You weren't taking log backups by any chance?
February 14, 2009 at 12:20 pm
That's a good point... if no log backup was taken between then and now, you could possibly restore to point in time. That could be a hell of a long restore, but that would work.
February 14, 2009 at 12:42 pm
I don’t think that he can use the log after he did the restore operation.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2009 at 1:30 pm
Ninja's_RGR'us (2/13/2009)
That reminds me of those 2 procs... hoping you don't need to use them anytime soon :exec sp_update_resume
exec sp_distribute_resume
😀 got a feeling he'll be using those anytime now.
Ninja's_RGR'us (2/13/2009)
The other way to restore that data is to manually reenter it... that is if you keep some sort of hard copies.
thats if he got the spare 5 months it took to enter it in the first place 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 14, 2009 at 2:21 pm
Perry Whittle (2/14/2009)
Ninja's_RGR'us (2/13/2009)
That reminds me of those 2 procs... hoping you don't need to use them anytime soon :exec sp_update_resume
exec sp_distribute_resume
😀 got a feeling he'll be using those anytime now.
Ninja's_RGR'us (2/13/2009)
The other way to restore that data is to manually reenter it... that is if you keep some sort of hard copies.thats if he got the spare 5 months it took to enter it in the first place 😉
I know, but that's still better than losing it all forever. And re-entering data is usually shorter the 2nd time (the usual daily task are not included this time around, it's jsut straight data entry!).
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply