Restore Master database in SQL Server

sqlserver2014

As a SQL Server Database Administrator, one of the tasks you should know how to perform is restoring the Master database. The Master database is one of the key system databases in SQL Server and should be included in your backup/recovery plans.

Step 1

You will have to start the SQL Server instance in single user mode from the command window as shown:

C:\>NET STOP SQLSERVERSERVICE
C:\>NET START SQLSERVERSERVICE  /m

Step 2

Once you have your instance running in single user mode, open a command prompt and connect to the SQL Server instance using SQLCMD. This will allow you to run the restore command as shown below.

C:\> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'X:\Backups\master.bak' WITH REPLACE;
2> GO

The restore command is specified with REPLACE command to overwrite the existing master database. The SQL Server service is now stopped.

Step 3

Start the service and you are ready to verify the SQL Server instance is back to normal.

C:\>NET START SQLSERVERSERVICE

 

As will all your training and testing, you should practice this process in a test environment. If this type of problem happens to you, you will be ready quickly react to the issue and minimize down time or data loss.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s