In the previous articles of the SQL Server System Databases series, we have learnt the purpose of all SQL Server System databases that comes as part of SQL Server installation and understood the best practices to be implemented for them. We have also understood the Tempdb and MSDB databases in more detail. Since Backup and Restore of System database is slightly different compared to User databases, we will go through how to Backup System databases and Restore system database from Backups in detail in this article.
Backup SQL Database
The term Backup refers to copying the data inside a database as a backup file that can be used to restore or recover the data further.
A Full Backup of a database will contain the data copies across Data Files and Log files to either restore as a database or do the point-in-time restore in case of any disasters or catastrophic failures. Hence, regular Full Backups on the system as well as User databases are crucial.
Apart from Full Backup, SQL Server supports several other Backup options like Differential Backup, Transactional Log backup, File backup, etc., but we aren’t going to focus on them since it is outside the scope of this article.
A Full Database Backup should be scheduled to execute periodically or after any specific configuration changes. Even though SQL Server can accept the Full Database Backup in different file extensions, it is recommended to save that backup as a *.bak file for easier classification and maintenance.
The Backup database syntax has a lot of options available, but we’ll only focus on the fundamental commands required to take the Full Backup of any database. The full syntax is present in the MSDN article.
BACKUP DATABASE Syntax (minimal)
BACKUP DATABASE <Database_name>
TO DISK = <File_Path>
GO
Backup User Databases
To take a Full backup of the AdventureWorks database, just replace the Database Name and File Path in the above BACKUP DATABASE command and execute it:
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks_Full.bak'
GO
Backup System Databases
Using the above BACKUP DATABASE command, we can take a Full Backup of system databases, such as master, msdb, and model:
BACKUP DATABASE master
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\master_Full.bak'
GO
BACKUP DATABASE model
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\model_Full.bak'
GO
BACKUP DATABASE msdb
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\msdb_Full.bak'
GO
We can navigate to the above folder path, and there we see the Full Database Backups created successfully for both the user database and system databases:
Is it Possible to Back Up the tempdb Database?
Let’s try taking a Full Backup of the tempdb system database using the same BACKUP DATABASE syntax:
BACKUP DATABASE tempdb
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\tempdb_Full.bak'
GO
As we discussed in past articles, tempdb is the only database for which a Backup cannot be taken. The tempdb database will be created newly whenever SQL Server services are started. Hence, if there are any issues with tempdb, restarting the SQL Server service will help resolve them.
Restore Database
Restoring a database from Full Backup(*.bak) brings back the database with complete data or “rolls back” a database to a point in time. The RESTORE command can also be used to restore Files, Filegroups, or Transactional Logs, but it is not the scope of the current article.
To restore a Full Database Backup, we can use the below syntax. Note: Similar to the BACKUP command, the RESTORE command has many options, but we’ll appeal to the basic ones only. You can find detailed information about those options in a dedicated MSDN article.
RESTORE DATABASE syntax
RESTORE DATABASE <Database_Name>
FROM DISK = <File Path>
WITH REPLACE
GO
Restore User Database
To restore the AdventureWorks database from the Full Backup we took earlier, we can use the RESTORE DATABASE command, replacing the Database Name and File Path as shown below:
RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks_Full.bak'
WITH REPLACE
GO
Restore Model Database
Let’s try to restore the model system database using the RESTORE DATABASE command:
RESTORE DATABASE model
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\model_Full.bak'
WITH REPLACE
GO
Model database backup can be successfully restored using the default RESTORE DATABASE command.
Restore Master Database
Now, let’s try restoring the System master database using the RESTORE DATABASE command:
RESTORE DATABASE master
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\master_Full.bak'
WITH REPLACE
GO
It threw an error stating that the SQL Server instance should be running in the single-user mode to restore the master database. As the error message indicates, we need to start our SQL Server instance in the single-user mode and then try restoring the master database.
To start a SQL Server instance in the single-user mode, we can apply one of the following methods:
- Command prompt
- SQL Server Startup parameters in the SQL Server Configuration Manager.
Command Prompt Approach
To start the SQL Server instance in a single-user mode via command prompt, open the Command Prompt with Run as Administrator option as shown below:
Once it’s done, type in the command:
NET STOP <SQL_Server_Service_Name>The SQL_Server_Service_Name can be obtained via the SQL Server Configuration Manager or services.msc
SQL Server Configuration Manager
Services.msc
Since our SQL Server Service is the default instance, we can use the name MSSQLSERVER as highlighted above. Once entered, it will ask for confirmation to stop all dependent services. We can stop the SQL Server Agent Service as well by typing in Y for Yes.
When the SQL Server Service is stopped, we can start it in single-user mode. Run the below command where /m means starting SQL Server Service in single-user mode.
NET START MSSQLSERVER /m
When it is performed, we can try to restore the master database. Run the RESTORE BACKUP command as before:
By starting SQL Server Instance in Single-User mode, we are able to restore master system database successfully from the last known Full Database Backup without any issues. Thus, we have learnt that to restore master database, SQL Server instance should be in Single-user mode.
With SQL Server instance in Single-user mode, only one user can connect and after our Restoration activities are completed, we need to change it back to Multi-user mode by Stopping SQL Server instance and starting SQL Server instance without /m option as shown below:
NET STOP MSSQLSERVER
NET START MSSQLSERVER
Once started in normal mode or Multi-user mode, all users can connect to the SQL Server instance and perform their activities. We can also verify that the master database was restored to the time Full Backup was taken and any configuration changes done after that needs to be performed again.
Few things to note while SQL Server Instance is in Single-User Mode:
- When the SQL Server instance is in single-user mode, only one user session can connect to it. Hence, make sure that the SQL Server Agent Service and all other SQL Server-related services are in the stopped status. Otherwise, those connections may get established first, and if it happens, we can’t connect to SQL Server to restore the database.
- While connecting via SSMS in a single-user mode, disconnect the Object Explorer and connect only via the Query Window. If a connection is established from the Object Explorer, a single connection is established. Hence, you can’t connect from the Query Window. Refer to the below screenshot – it demonstrates how to connect only the New Query window without connecting via Object Explorer:
SQL Server Startup Parameter in Configuration Manager
Those who are more comfortable with GUI instead of the command prompt can apply the below approach.
1). Open the SQL Server Configuration Manager by typing MSSQLManager13.msc into the command prompt. Here, 13 refers to SQL Server 2016, so use the correct corresponding numbers for other versions of SQL Server:
- SQL Server 2012 – SQLServerManager11.msc
- SQL Server 2014 – SQLServerManager12.msc
- SQL Server 2016 – SQLServerManager13.msc
- SQL Server 2017 – SQLServerManager14.msc
- SQL Server 2019 – SQLServerManager15.msc
2). Expand SQL Server Services:
3). Right-click on SQL Server Service identified as SQL Server (MSSQLSERVER) > Properties.
4). Select the Startup Parameters menu tab.
5). Click on -m in the Specify a startup parameter field, then click Add to start the SQL Server Service in a Single-User mode.
6). Click OK and restart the SQL Server Service to start SQL Server Service in Single-user mode.
To modify the SQL Server instance from Single-User mode to Multi-user mode or normal mode after restoring the master database, just Click on -m parameter from Existing Parameters, Click Remove and restart the SQL Server service.
Restore MSDB Database
Now, let’s try restoring the msdb system database backup using the default RESTORE DATABASE command:
RESTORE DATABASE msdb
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\msdb_Full.bak'
WITH REPLACE
GO
This attempt brings us an error stating Exclusive access could not be obtained because the database is in use. This error message indicates that some other process is using msdb. Thus, we need to apply one of the below approaches to restore the msdb database correctly:
- Start the SQL Server Instance or Service in the single-user mode as we did earlier to avoid anyone connecting and accessing the msdb database.
- Or bring the msdb database to the single-user mode without any user connecting to it.
Since we know how to turn the SQL Server Instance or Service to single-user mode while restoring master system database, we will try the second option by changing the msdb database to Single-user mode to restore the msdb database backup.
Run the below command:
USE [master]
GO
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Important: The execution of the above command can fail in the following cases:
- The SQL Server Agent Service is up and running. To fix the failure, stop the SQL Server Agent Service and try again.
- Any User sessions are connected to the msdb database. We can find out active connections to msdb or another database by using the below query:
use master
GO
select spid
from sys.sysprocesses
where dbid = db_id('msdb')
If this query brings any results, it indicates that some user sessions are connected to the msdb database. In this case, we should kill those sessions by the KILL command and replacing the spid obtained from the above query execution:
KILL <spid>When no sessions are connected to the msdb database, we will be able to successfully bring msdb database into single-user mode. Whenever a database is in Single-user mode, we will be able to see the (Single User) word shown nearby to the database as highlighted below for msdb database:
Let’s try restoring the msdb database now using our RESTORE DATABASE command:
With msdb database in single user mode, we were able to restore msdb database successfully from the last known good Full backup of msdb database. Restoring a database from backup will by default bring it to multi-user mode and if it is still in Single-user mode for any reason, we can execute the below command to bring it back to multi-user mode:
USE [master]
GO
ALTER DATABASE [msdb] SET MULTI_USER
GO
Rebuild System Databases
For any ideal Production environments, it’ critical to have a Full Backup of System databases in place to restore the system databases without any data loss in case of any accidental mistakes by users or Database Corruptions or Disaster Recovery.
In worst case scenarios, if SQL Server Services cannot be started then we would need to first try restoring the system databases from last known Full Backups and if we don’t have Full Backups available and couldn’t start SQL Server Services, then we are left with only one final option i.e., Rebuilding the system databases. Note: Rebuilding system databases will clear all Server level configurations stored across all system databases and we might lose all Server/Instance Level configurations like Logins, SQL Server Agent configurations, other critical details stored across the system databases we have seen across our previous articles.
Let’s take a quick look on how to rebuild system databases (As mentioned above, this process should be done as a last measure to bring back the SQL Server instance in the absence of Full backups of system databases).
To rebuild the system databases, we would need the SQL Server Installation media, either mounted or copied to the Server where our SQL Server Instance is installed. Once it is done, we should follow the below steps:
- In the Command Prompt, navigate to the path where the SQL Server setup files (setup.exe) are located:
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016 - Execute the below command, replacing all parameters with valid values. Value ACTION = REBUILDDATABASE denotes that all system databases are to be rebuilt after executing that command.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]Parameters to be changed include:
- InstanceName – the Instance Name of SQL Server, in our case it is RRJ
- Accounts – the sysadmin account name, in our case it is sa
- StrongPassword – a strong password for the sa sysadmin account.
- CollationName – the Collation Name of SQL Server database if needs to be changed out.
Conclusion
We have learnt how to Backup and Restore User and System databases and understood the additional measures required to Restore System databases like master and msdb system databases. In case of missing Full Backups of System databases, we have also learnt how to Rebuild System databases from SQL Server Installation media and understood the configuration related data losses involved while rebuilding system databases. To summarize, we indirectly understood the importance of scheduling Full Backups for System databases in addition to User databases.
Thanks for your time, and we will meet again soon with another interesting article.
Tags: database backup, sql server system databases Last modified: September 16, 2021
