It goes without saying that as a DBA, one of your most important tasks is to schedule and ensure regular backups of your databases. But by ‘databases’ here, we usually mean your user-created databases. But SQL Server also automatically creates and maintains a number of system databases. So should you also back these up? Well, the answer isn’t a simple yes or no …

System Databases – What Are They?

The SQL Server system databases are system-created and maintained meta-databases; that is, they contain data about other databases (in this case, your user-created databases). The system databases are important because they hold important system and database meta-information, without which your SQL Server will not work properly or even start.

There are five main system databases: master, model, msdb, tempdb, and Resource. Your configuration may also have an additional one called Configuring Distribution if you have set up database replication, and the server is configured as a replication distributor.

Of these, the master database is the most important. According to this Microsoft documentation site, the master database “records all of the system-level information for a SQL Server system.” This system-level information includes all the user database logins, linked servers, endpoints, and other system-wide configuration settings. The master database also holds information about the other database names, their last state (online, offline/ recovering, etc.), and the location of their data and log files. If the master database is not present or cannot be read, SQL Server cannot start. It is, therefore, important to regularly include the master database in your backups. It is also a good idea to take a backup immediately after any major system-level changes such as adding or deleting users or creating a new database or deleting an existing one.

Although the master is the most critical system database, a few of the other system databases are also pretty important and should also be regularly backed up whenever you make any changes that affect them. Read more details on the Microsoft website about which system databases should or should not be backed up, but a quick overview is included below. Note that for the tempdb and Resource databases, backups are not required at all – in fact, it’s not even possible to back them up.

• Master: holds information about logins, users, security, and also information about all other databases such as their names, state, and file locations.
• Msdb: stores information on jobs, operators, alerts, backup and restore history, database mail information, and so on.
• Model: used as a model or template for all new databases.  If you want certain objects to be in all new databases, this is where you configure this information.
• Tempdb: created each time SQL Server starts, so no need to back this up. Used to hold temporary data such as when sorting or ordering data within a query.
• Resource: this is a read-only hidden database that contains all the system objects which are included within SQL Server. If you want to back it up, you have to do it at the operating-system file level by performing a file-based copy of its data file (mssqlsystemresource.mdf) and the log file (mssqlsystemresource.ldf), because SQL Server doesn’t support backing up the Resource database.

An easier way to do this

Despite the fact that most DBA’s are aware of the criticality of system databases, they usually don’t bother to include them in the backups (especially the master). The main reason is unlike user databases, the system databases don’t change that often, and the DBA trusts his or her memory when they do change – “when I make any major changes, I’ll remember to back up the system db’s.”

But with the SqlBak tool, you don’t have to compromise on this or rely on your memory. You simply configure your database backups to include the important system databases you want (select the “Show System databases” checkbox). And SqlBak automatically excludes those system databases that cannot be backed up – tempdb and Resource. Or alternatively, if you don’t want to include your system databases in every backup, simply create a separate backup job that runs less frequently, say, once a week, and only include your system databases in this backup.