December 5, 2010 at 1:43 am
Our maintenance plan has been setup to take daily backup of the actual database. Once a week the the latest backup of actual database is restored to test database which is TEST01. Every time I perform the restore, the previous one is overwritten and TEST01.ldf and TEST01.mdf files are created with date as same day the restore was done. We did not schedule a daily backup of the TEST01 database.
My SQL server knowledge is limited and would appreciate your advice. We are developing a custom report and hence had written the program code and created new custom tables on the TEST01 database directly sometime in Oct'10. We wanted to test the program before moving to actual database. And since the restore happens weekly, when the latest live database was restored as TEST01 in Nov'10, I believe the program code and tables created have gone missing. We did not back up the work done on test database.
The ldf and mdf files are dated Dec 5'10 since restore happened today. My question is can I restore and get back the work done in Oct'10 on TEST01 using the ldf and mdf files?
If yes, how?
Any suggestions and information would be very helpful.
December 5, 2010 at 11:20 am
Unless you have a copy of the database from the date in question, you will not be able to recovery. A restore overwrites the database completely.
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
December 5, 2010 at 10:47 pm
I have the copy of the master database from date in question. But the custom program was made on test database and there was no back up of test database taken. I agree that every time I restore master database to test, it overwrites the previous one.
My question is do the mdf and ldf log files store all changes/transactions made to TEST01 database starting from day one or will it only have transactions logs made to latest database restored.
December 5, 2010 at 11:25 pm
sraned (12/5/2010)
I have the copy of the master database from date in question. But the custom program was made on test database and there was no back up of test database taken. I agree that every time I restore master database to test, it overwrites the previous one.
If you don't have a backup/copy of the test database, then there is no way to recover anything
My question is do the mdf and ldf log files store all changes/transactions made to TEST01 database starting from day one or will it only have transactions logs made to latest database restored.
As I said, a restore overwrites the target database completely - data and log files. When you restore master to test, it's the equivalent of first dropping the test database, then restoring the master in its place.
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
December 5, 2010 at 11:29 pm
NO backup - all is lost. Coping the MDF and LDF files do not a backup make... having a backup of Master is like having a key to the house but the movers have already been and gone. Master only holds the configuration of the Database in question. If test1 was over written and you have no backup then the changes are lost.. vanished .. Poof.. up in smoke .. and just plain gone.
Suggest you get a proper development / test cycle implemented before more is lost.
Code on
😛
December 5, 2010 at 11:35 pm
Malcolm Daughtree (12/5/2010)
having a backup of Master is like having a key to the house but the movers have already been and gone. Master only holds the configuration of the Database in question.
I think he meant their master system database *as opposed to their test database), not the actual database named master.
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
December 5, 2010 at 11:42 pm
Oh ... wasn't clear, sorry . Either way there's a whole world of hurt goin' there. It's still gonski..
😛
December 6, 2010 at 12:06 am
sraned (12/5/2010)
My SQL server knowledge is limited and would appreciate your advice. We are developing a custom report and hence had written the program code and created new custom tables on the TEST01 database directly sometime in Oct'10. We wanted to test the program before moving to actual database. And since the restore happens weekly, when the latest live database was restored as TEST01 in Nov'10, I believe the program code and tables created have gone missing.
i would say , for these kind of scenario. you can do below kind of approach
1. maintain the data/sql code in other DB.So that you can recover it when required.
2. take backup of data and restoree at different DB and then do the restoration and migrate the required tables into main DB.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 6, 2010 at 10:59 pm
Thanks for all your suggestions.
December 7, 2010 at 11:55 am
Just thinking loud- If you have mdf and ldf files can't we use those files for a db....i mean create a db with similar name (mdf and ldf file names). Do a detach (new files) and attach old mdf/ldf files. Worth a try as we still have mdf and ldf files. I do remember there was a similar solution in one of the online forums. Worth the effort of checking if data can be recovered with existing files...no harm in doing some research.
December 7, 2010 at 4:03 pm
If the .mdf and .ldf files are available for the date in question (Through Wndows or System backup), then they can be used to recover the lost data.
Thank You,
Best Regards,
SQLBuddy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply