Guys, today was a very looooong day for us. We had to restore Master Database and MSDB Database on our Production Server…Yessssssssss, you heard it rightL
PS Rebuild Master is different from Restoring Master Database.
Let me tell you the story….what exactly forced us to make this dangerous move.
This weekend our Windows folks had a planned Windows Patching activity and they were doing some Net backup Upgrades on our Production Database Servers. Everything appeared to be normal, till they rebooted the machine. Once they initiated Reboot, Server was not coming up! OMG…you can imagine all kinds of screams on the call! They did all sorts of attempts to bring the server UP via HP ILO without any success:( They stepped into our Data Center and the result was the same…At last HP Engineer was Onsite, but considering the time factor (Business starts on Monday Morning), they decided to rebuild the Entire Windows Server! So… we, the SQL Server DBA’s decided to use our DR Server meanwhile. Unfortunately the Application Folks were not happy running on the DR Server for more than 48 Hours (Since they (we) identified few missing critical SSIS packages and Linked Servers. The Bad DBA who’s supporting this Server missed it for some reason. I’ll come up with a short Blog post very soon what needs to be considered besides Just the Database in your DR Strategy – Stay Tuned)
FYI we had the below-
SQL Server 2005SP3. (Pls. don’t ask me why are we still running on 2005 SP3 on this box;-p )
DR Strategy – DB Mirroring.
Well, So the Quick/Short Plan was:
1 – Windows Folks Reimage the Entire Windows Server.
2 – Storage team takes care of SAN and Drive Allocations etc.
3 – Once notified, We’ll Install SQL Server and recover the databases from our DR (the current Production) Server.
At last we got notified that Windows is all set and the Server is ready for SQL Installation. At this point interestingly one of the LUN (F$) was not visible to us. SAN Folks mi(e)ssed it. Again we pulled them and they had to do their magic. Well, it’s time to Install SQL and LiteSpeed and do the rest of Our Magic as DBA’sJ
1. We Installed SQL Server, Making sure that the DR and Prod Servers match the Build numbers.
2. Before migrating databases from DR to PROD Server, our plan was to restore MSDB and MASTER (We had >100 Agent Jobs and several linked Servers, Endpoints, tons and tons of logins). Our Poor DBA didn’t notice the importance of securing all that info in a secure place till date on prod Server (A very good lesson learnt). So we thought of Restoring Master and MSDB Databases to restore all that critical pieces of puzzle within short time and with less pain in our Butt!
Glad that We’ve a very reliable Backup/Restore Strategy. Soo….Always note that Database Backups (Healthy and restorable backups) are DBA’s Best of Best FriendsJ (I mean it).
Let’s Get Started with Restoring MSDB Database:
Let’s simulate a simple scenario here! Assume, the current Server Snapshot looks as below
Please notice tst, tst1 logins and DENALI linked Server and tst,tst1 Agent Jobs. Let’s assume the below snapshot is from a brand new Installation (you can see all the above mentioned pieces are missing here).
Before Restoring MSDB, make sure that your Agent Service is stopped for Exclusive access. Let me tell you something, Restoring MSDB and MODEL Databases is no different from restoring a simple User Database.
Syntax:
RESTORE DATABASE [msdb] FROM DISK = N’D:\msdb1.bak’
WITH FILE = 1, REPLACE, STATS = 10
GO
Results:
11 percent processed.
23 percent processed.
35 percent processed.
41 percent processed.
53 percent processed.
65 percent processed.
71 percent processed.
83 percent processed.
95 percent processed.
100 percent processed.
Processed 2136 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 2 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
RESTORE DATABASE successfully processed 2138 pages in 1.407 seconds (11.871 MB/sec).
Now, let’s Start SQL Agent and look at the jobs.
Ha ha…our Jobs are Back. That’s all we need to do for Restoring MSDB!!!
Let’s Restore Master Database now.(Go and grab your coffee!)
First Step you’ve to remember to restore Master Database is you’ve to start your SQL Server in Single User Mode.
How to start SQL in Single User Mode?
Go to SQL Configuration manager and add -m in startup parameters as shown below.
Points to Remember: Semi Colon is mandatory between switches and there should be no space in between as shown above.
When SQL is started in single-user mode, first stop SQL Server Agent. Otherwise, Agent might connect first and prevent you to connect from!! 1st Sysadmin connection will be allowed to be connected at this time and rest of the connections will be thrown an error as per below screenshot
Now, open your sqlcmd (Microsoft recommends us to use SQLCMD while restoring Master Database) and please see the syntax as shown below. FYI My SQL Instance name is PROD.
Now, go to your Config manager, your SQL Server will be in Stopped State.Not a problem at all! (Restore Master Database will shut Down the SQL Server Service).
Remove the –m switch added in startup parameters and start the SQL Server and Agent Services.
Now if you see the below snapshot, Server came up exactly as it was earlier in the first Screenshot with all the logins, Jobs and the Linked Server.Tadaaaaaaaaaaaa:) 🙂 🙂
Also, Once Master has been restored successfully, our Mirroring Database went into Synchronizing/and then
into Synchronized state after a while from Disconnected State.Hurrrray………!! This happened because all the Mirroring ENDPOINTS info was secured in our Master Database Backup which knows the Mirror Database Server/Database information to communicate with:)
That’s all Folks! Hope this post helps you. Happy Tuesday!
Like this:
Like Loading...
Related
MSSQLTREK 
Hey Sree, you are the genius one. I was doing some test on my R&D Server. I forgot the stop Agent Service, after taking the sqlservice on single mode & struggling with getting error only one Administrator connect at this time. Now my problem resolved. I am big fan of your and also read some articles from mssqltips.com
Thanks once again for sharing this great knowledge.
Thanks & Regards
Deepak Kumar
Actually, That’s clearly Mentioned in Microsoft Technet Article, “Regarding Stopping Agent Service”. Btw, Looks like you are continuously reading my articles since Morning!..It’s Friday Deepak, Give it a break and Relax for now 🙂
Cheers!
Thanks Sree. Its a good workaround steps.
Can you tell me how to restore the logins once the master database rebuild is done?
Hi Wael – Your Logins should be back in place once you are done restoring(Rebuilding) Master Database. They are stored in master Database. Are you seeing any issues with your logins?
how does the rebuild and restore differ? please guide
how does the rebuild and restore differ??
Hi sri, can u pls explain how does the rebuild and restore differ?
Faran/Vinod – Yes…They are 2 different operations. See below post for Rebuilding your master DB.
https://sqlbuzz.wordpress.com/2011/08/20/how-to-rebuild-master-database-aka-rebuilding-sql-server-2008r2/
Hello. Excelent content. But what if master’s db path in production server is d:\Data and i need to move it to e:\Data??
AFAIK, SQL Server won’t allow to restore Master database directly to a different location using backups. I don’t think “WITH MOVE” option is supported while restoring Master database. However, you can move the master database files once you are done with restoring – You would do this by changing -d and -l parameters in config manager.
For more info on how to move master database:
http://technet.microsoft.com/en-us/library/ms345408.aspx
Excellent workaround.. Its helps me a lot. Thanks