How to Restore model and msdb database | SansSQL

Tuesday, September 10, 2013

How to Restore model and msdb database

Unlike master database, restoring model and msdb is simple and follows the same procedure as restoring any other user database. However we have to be very cautious while restoring system databases as it will have sensitive data which is important for SQL Server to function without any issues.
The first important thing is, we should make sure that the build versions of the both instances (Current instance and the instance where backup is generated) should be the same.
If the versions are different then the restoration will not happen.

Restore model database:

Restoration of model database can be done using the below T-SQL scripts.

RESTORE Database model
FROM DISK ='C:\Backup\model.bak' --Change backup path

Restore msdb database:

Before restoring msdb database, make sure that the SQL Server agent service is stopped. This is because an exclusive lock on msdb database is required for restoring database and this will not be achieved while SQL Sever Agent is running.
Once the SQL Server Agent is stopped, restore the msdb database

RESTORE Database msdb
FROM DISK ='C:\Backup\msdb.bak' --Change backup path

After the msdb database is restored, proceed with other recovery steps such as correcting mismatches in Jobs, alerts, operators, etc... (if any)

No comments:

Post a Comment