System Databases Restoration

Some times we need to restore System Databases.

This may happen due to system or hard disk or any upgrade failures.
We have two cases to go for restoring system databases

1)If MSSql Service is running fine then we will proceed to restoring system databases.

2)If MSSql service is not running or it is going to fail while at restarting then we need to reinstall SQLServer Software in 2005 and 2008 and in 2000 we have one option of rebuilding master database by running of the Rebuildm.exe executable and this was located at C:\Program Files\Microsoft SQL Server\80\Tools\Binn by default.

In the case of one we must need to restore system databases again but in case of two it's depend on business requirement. If we restore system databases again then no need to create user logins,tables,views,triggers,procedures and Server level configurations in the System databases.

I feel you might be understand why we need to restore system databases.

Steps to restore System databases

------------------------------------------

1)Take the backups of all System databases in the drives and verify them whether the taken backups are valid files or not.

2)Restoration of System Databases and User Databases are different here. We can only restore master database when the server should be on single user mode.

Steps to start SQL Services in Single User Mode

• A3 A) Go to the SQL Server Configuration Manager then click on SQLServices then it displaying what are the services running under SQLServer.
• 3 B)Right click on SQLServer(MSSqlservice) or any other named instance and click on properties then it displaying all properties what it has.
• 3 C)Click on Advanced, in this window we have Startup Parameters. Here just place -m and fallowed by ;(Semicolon) as shown in the below screen shot.
• 3 D)Click on Ok and restart the SQLServer(MSSqlservice). If we wont restart SQLServer(MSSqlservice) then it is still under multiple user mode only.

4) Go to Windows Start –>Run and under the Run type CMD and click enter button then it display Command Prompt.

5)Under Command Prompt type sqlcmd and click enter.

6)Now it is ready for executing SQL queries under the single user mode

7)Give the restore Query for the Master database as given below

 

RESTORE DATABASE [master] FROM

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\master.bak'

WITH FILE = 1, REPLACE, STATS = 10

go

Run the above query

Now Restoration of Master Database was completed.

8)Fallow the same procedure for Msdb, Model Databases too.

9)If we have Distribution database also exists then restore this too and this is must for replication.

10)After restoration of all system databases Please remove the -m option in the startup parameters in the SQLServer(MSSqlservice) and click ok and restart the SQLServer(MSSqlservice) then its ready for SQLServer to connect with Management Studio.

Thanks for your attention for reading article and All The Best For Your Careers and My Career Too.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.