Restore Master

1 Comment

You’ve just rebuilt your master database. You discover that your other databases are gone, all logins are missing, pulse is rising…

If you take regular backups of your system databases (master and msdb primarily) you have nothing to worry about. If your database files are on undamaged drives you are even better up.

To get all the logins back and all of your user databases “re-attached” you only need to restore your master database. And restoring the msdb database gets you the jobs, schedules and alerts back as well.

To tell the truth: This is not something you do every day, it may even be a bit scary. So here is a simple way to restore your master database, that works in SQL Server 2008, 2008 R2, 2012 and in 2014.

NOTE: commands are written against the default instance (named MSSQLSERVER). If you have a named instance, you’ll have to find the name of it first…

1. Start an elevated command prompt.

2. Run : net stop mssqlserver

3. Navigate to your instance’s “binn” folder (i.e. “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”)

4. Run : sqlservr.exe -m -c -s MSSQLSERVER
This starts sqlservr (the server executable) in single user mode (-m) as a console/not service application (-c). And the instance name is MSSQLSERVER (the default instance)

!I got a warning from Windows built in firewall at this step. It’s perfectly OK to ignore, and NOT let the application communicate on networks – we’re local so the restore will work anyway.

5. Start ANOTHER Command prompt – DO NOT close the other one.

6. Run : sqlcmd -s. –E
SQL Server command tool logs in to the default instance, locally (-s.) with Windows Authentication (-E)

7. 1>restore database master from disk = ‘full path to backup file ‘

Your other window should now be back at the command prompt. Database restored.

Since the method uses only console tools, it works fine on servers with sparse UI possibilities Ler

So it’s official

Leave a comment

I’ve been working for myself for more than eight years now. I like it. The fact that I’ m my own boss. I decide what to do and what to buy 😊. However sometimes you need co-workers. You know, the persons you can talk to and ask stuff without the risk that they will think you are strange and will never hire you again…

I got the opportunity to work for SolidQ. A really “front line” kind of company. I will still be in business as usual, but I can also be reached at my new company. I havre already found this arangement useful and fun, and  and can’t wait to contribute to the SolidQ community.

Another blog…

Leave a comment

Welcome to this test blog site project. People say WordPress rules, so I’ll have to give it a try.

Here you’ll find information and reflections. I actually don’t know if blogging is for me, but I’ll do my very best 🙂

– Posted using BlogPress from my iPhone