The SQL Server restore master database article will explain the fast and easy method to perform restoring of the most important database. “Master” database restoration is not something you need to do often. DBA administrator can often skip the backup of master database. Of course that is very important to perform full backup in regular basis. Please understand importance of this database.
The importance of Master database:
All the metadata and system-level information regarding SQL Server system stored on this database. Including server logins, file locations of databases, configuration details, etc. If the master database is not available you cannot open any other user database and your SQL Cannot start. This is the reason that you need to take seriously the backup of master database. Use sql server error log file if anything goes wrong.
The good part is that the restoring of master database is very easy step. We will show below the fast way to do the restoring.
SQL Server restore master database
This is the best solutions in situations that you have a good backup of the database.
- Start the SQL Server in Single-user Mode
The first step is to start the instance of the SQL Server in Single-user Mode. This step can be done adding –m parameter on sql services. Open SQL services and stop all services. Right click on sql server service and click properties. Go to start-up parameters and add –m parameter. Apply the change and start only the sql service.
Stop SQL Services
- Connect with sql using sqlcmd.
Connect using SQLCMD in order to be able to restore the database from command line. Enter the command:
SQLCMD -S SERVERNAME –E
Change the SERVERNAME with yours and click enter. You will be connect with sql server now.
- SQL command to restore the database
On the command line copy past the following command and click enter.
RESTORE DATABASE [master] FROM DISK = N'D:\BACKUP\System_Database\master_backup.bak' WITH REPLACE ;
GO
Please change D:\BACKUP\System_Database\master_backup.bak with your path and backup name.
After you click Enter and restore will start and finish very fast because master is small database.
SQL Server restore Master Database
Before using the SQL Server you need to revert the single-user mode. Go to the SQL Services and remove the –m parameter. After that start the sql service and the others service as well.
Read also other SQL Server Helping Posts:
Tips Before and after restoring master database.
Always take backup of the system databases including master database. If the system settings change frequently is recommended that as well master database to be backup.
After restoring the master database you maybe will find that cannot access the other databases. The databases will be in “recovery pending”. What you need to do in this situation is to attach the database using below commands.
USE [master] GO CREATE DATABASE [Test] ON ( FILENAME = N'C:\SQL\Data\Test.mdf' ), ( FILENAME = N'C:\SQL\Logs\Test_log.ldf' ) FOR ATTACH
GO
Read also: Move database files to another drive
Another solutions is to restore the others databases from the backups.
Practice disaster situation on a testing SQL Server. Is good to be prepared before things happen.
If you have any question about “SQL Server restoring master database” please comment below.
