February 8, 2012 at 3:00 am
Hi all,
we've had a hard drive failure and ended up having to format the RAID to get it back up and running. With that i lost all the databases for the sql instance including system. (Its only a dev box. So im using it as a chance to learn some DR planning and testing)
I have system dbs back up and running via: restoring them to another sql box of the same version. and copying the mdf ldf files to the server in there original location. Shockingly this has got the sql box back up and running. I found a blog saying this is a good way to get Master back if you lose it to corruptions or lose
Now when i load into it with SSMS it shows all the databases but obviously they are not there.
Is doing a restore like before and putting them back in ok to do?
Have i got SQL running properly or have just put in a botch job that will eventually crash and burn??
Any advice most welcome.
February 8, 2012 at 3:16 am
It is perfectly acceptable to get your system databases back by moving the files back into place. I always have this option available to me for DR purposes. Its basically what a rebuild master does.
Get you user database back by restoring them, SQL is already aware of their presence just cannot find the files.
alternatively put the user database files in the correct place and then run alter database set online.
did you have tape backups of this drive? there are .cert files you could recover from there if this drive contained the default data directory specified at install time, though SQL will function no problems without them.
---------------------------------------------------------------------
February 8, 2012 at 4:12 am
Hi,
thanks for the reply.. Good to know that its a expectable way of doing things..
I plan on doing a restore of the database. But when i right click the dbs all of the tasks etc are greyed out..
Just tried scripting and that works though! .. (Bug?)
Its good to know the restore to another server works as backup solutions!..
Could you or anyone else confirm that say
Restoring a 2008 r2 Ent db to a 2008 r2 Dev would work? to copy it back. Ive just done Dev to Dev.
Does CU/SP have to match?
Thanks
S
February 8, 2012 at 9:14 am
the databases were suspect so the GUI would not alow any action on them. Going up one level to the database root node should allow you to restore a database though.
system dbs levels have to be the same, user dbs ent to dev would be fine, ent to std ok as long as no enterprise level features are used.
Cu\SP not an issue for user databases.
Now you have your system back up when you get a chance stop sql and copy off the system database files somewhere, then they can just be slid back into place should this happen again.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply