Trouble restoring Master DB
-
I am running through a Disaster Recovery scenerio and I am having trouble restoring the Master and msdb DBs to a recovery box.
My production is a default Instance on WIN2k Server - SQL Server is 2000 Standard, sp3a
The box I'm trying to recover to is a Named Instance on WIN2003 server - SQL Server 2000, sp3a
I stopped/restarted my recovery server with -m and -T3608 startup parameters but when I attempt to restore the master backup from my production box to the recovery box, I get an error - it's a blank dialog box with a big red X and it says: Microsoft SQL-DMO - ODBS SQL State - HY000
when I check the sql server log: it shows the Server started in single user then it says recovering master db then starting up master DB then it just hangs..
Server event viewer shows this: 18052:error 3151, severity 21, state 0
not much to go on.. does anyone know what I'm doing wrong???
-
By the sounds of it you are going into SQL Server via Enterprise Manager (correct me if I'm wrong).
Have you tried starting SQL Server from the command prompt, using "sqlservr.exe -c -m"? You should then go in through Query Analyzer and perform your restore from there rather than through Enterprise Manager.
-
yes, I'm going in through EM to stop and restart the instance in single user mode. I wasn't sure how to start SQL Server from the command prompt being that there are multiple instances of SQL Server on this box.
Do I even need to restore Master and MSDB in a disaster recovery scenerio? or do i just need to recover my logins and then the user databases??? not sure what steps i would NEED to take to rebuild this production Instance on another (differently named) Instance for Disaster Recovery.. can anyone suggest??????
-
It depends. MSDB also contains SQL Server Agent job information as well as any DTS packages not stored n the file system. So you may need to restore msdb if you need either of these.
As far as master goes, if you can recover your logins and you've not build any custom procedures into master, probably not. At my organization we've talked about the possibility of recovering all user databases to a single SQL Server, recreating logins through a saved script which will ensure SIDS and passwords are matched up.
K. Brian Kelley
@kbriankelley -
As Brian states, as long as you don't have to recover any custom stored procedures that you built into master you're probably ok if you have a script of your logins.
However, there can be a few config settings that you might want to keep. Also, things like linked servers or replication can be broken if you don't have a backup (nothing that can't be fixed though with scripts.) It's always worth having a backup of master and testing a recovery scenario as it means a quicker recovery time and you don't have to remember to run/maintain a bunch of scripts.
As for starting a named instance of SQL Server, I'm pretty sure you can run the sqlservr.exe but you have to be in the corresponding binn folder for that named instance. Don't quote me on that though it's been a while since I've ran with named instances
e.g
C:\Program Files\Microsoft SQL Server\MSSQL$MyNamedInstance\Binn\sqlservr.exe
-
Thank you for your thoughts! I think I'll skip restoring Master and see how that works. I'll restore the logins (from a script restoreing the same password and SID) and that should be good enough. I really don't have any Custom Stored Procedures in Master DB. Thank you all. !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply