August 6, 2013 at 9:13 am
Hi experts, im trying to recreate a disaster recovery scenario and simulate a drive going down that hosts the database log (ldf) file. I want to see / document the steps I'd need to perform to recover the database from the mdf file (if at all possible). The steps i've performed so far are:
Create a new database 'Test'
mdf file in default SQL location
ldf file on a different drive (happens to be a removable pen drive)
I then remove the pen drive to simulate a drive going down. So now the Test database is inaccessable right.
So what I need to know is:
A. Is it possible to recover this database from the remaining mdf file (assuming no full backup exists)
B. If so, how?
Thanks in advance
August 6, 2013 at 9:23 am
NickBalaam (8/6/2013)
A. Is it possible to recover this database from the remaining mdf file (assuming no full backup exists)
Maybe.
B. If so, how?
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
However if you're working on a DR plan, a far better use of your time would be in implementing a backup strategy as the appropriate recovery procedure from the scenario you describe is restore from 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
August 6, 2013 at 9:47 am
Thanks for that Gail. I'll check that post now.
Yes I totally agree RE: DR plan, we do have a proper DR plan for our prod/dev servers (inc full db and hourly log backups etc), this is really for my own personal learning on my machine to see if it "can" be done. I can't forsee a circumstance where I'd need to do this in live. 😀
August 8, 2013 at 6:49 am
Well I tried that (from the blog post), fascinating. First method fine, stop service and delete log file etc. Second method (detach - delete - create new database and rename files) leaves the database stuck in recovery_pending mode even if I issue a [set emergency] stmt? So can't run the checkdb repair and so can't rebuild the log on the fly. Moral of the story....backups!! Just as well it's a lab exercise :w00t:
August 8, 2013 at 6:54 am
Can happen. Emergency mode is a last resort, it's not guaranteed to work in all cases.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply