Before dig into restore system databases in SQL server first let’s know about why they are important.
Their are mainly four system databases
- master : All system level information in SQL server like Database info, User info etc.
- model : It’s a template database in SQL server, when user creates a new database it get a copy of model database.
- msdb : This database is used by SQL server agent. It store the inforamation like backup , scheduling, proxy, jobs, operators and job and mail history.
- tempdb : This is a temporary workspace for result sets. This database is automatically get created from model database whenever we start a sql server.
Unlike the other user databases, procidure of restoring a system databases little bit different.
Restore model database : Before restoring model database keep in mind that database shouldn’t be used by anyone.
RESTORE Database model FROM DISK ='d:\dbbackup\model.bak -- Your backup location WITH REPLACE
tempdb: This database is used by SQL server agent service so before restoring this database make sure agent service is stopped.
RESTORE Database msdb
FROM DISK ='C:\Backup\msdb.bak' --Change backup path WITH REPLACE