0

What is the reason to backup master, msdb and model system databases?

How does it help during disaster recovery? And are they to be restored just like user databases?

variable
  • 3,591
  • 3
  • 28
  • 81
  • there are specifics proc to restore system dbs. ei check the doc for master : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql?view=sql-server-ver15 – Mickael Oct 04 '21 at 10:12
  • I restored master db on a new sql server and it didn't bring the logins along with it. So what is the point of backing up the master db? – variable Oct 04 '21 at 10:19
  • @variable If there were Logins defined in your server, then they will be part of the master database and I'm sure did come with the restore you did. The database Users are a different object stored at the database level, and my guess is your Logins got orphaned from the Users when you did your restore. That's a common issue that can be resolved. – J.D. Oct 04 '21 at 13:17
  • By curiosity, how did you restore your master ? Did you do a restore with replace (replacing the actual master db - Which I think cannot be done) or did you restore it under another name ? If you did it with another name, then you probably (surely) still have the original master DB in place... if you did not change the SQL Startup configuration, then it's simply still using the old master (that would explain why you don't see the logins) – Dominique Boucher Oct 04 '21 at 14:23
  • Yes with new name. So assuming there is crash, then you think restoring these dbs on the crashed server will automatically reinstate the sql logins, jobs? – variable Oct 04 '21 at 14:34
  • If you restore master with a new name, i.e. not master, then the logins present in the restored version of the database will not be visible in SQL Server. Only logins stored in the current master database are visible. You can see them via a SELECT statement executed in the Dedicated Administrator Connection, but only if you know where to look. – Hannah Vernon Oct 04 '21 at 21:27

1 Answers1

3

The best reason are the Microsoft recommendations from Backup & restore: system databases (SQL Server) (Microsoft | SQL Docs)

master

Description: The database that records all of the system level information for a SQL Server system.

Comments:
Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can supplement with an additional backup after a substantial update.

..and then this bit of information from master Database (Microsoft | SQL Docs)

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

If you lose the master database, you lose the ability to start the instance.

It is not intended to be able to restore the master databse to a different server to "migrate logins". To achieve this look at the DBTools.IO or Transfer logins and passwords between instances of SQL Server (Microsoft | SQL Docs) or as Hannah pointed out in the comment.

model

Comments:
Back up model only when necessary for your business needs; for example, immediately after customizing its database options.

msdb

Description:
The database used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. msdb also contains history tables such as the backup and restore history tables.

Comments:
Back up msdb whenever it is updated.

This happens whenever a backup or restore is performed, when jobs are executed or fail, mails are sent, etc. This is because all of the above mentioned tasks log their respective actions and outcomes in the various msdb tables.

IMO msdb is one of the most underrated databases on a SQL Server instance. Protect all of your system databases at all costs with a regular FULL database backup.

Disclaimer
Most of the information posted in this answer was copied from the above mentioned article and has been copied here, to ensure the availability of the information provided in Microsoft's article.

John K. N.
  • 17,649
  • 12
  • 51
  • 110
  • Yes I understand the recommendation is to backup. But for example I restored master db on a new sql server and it didn't bring the logins along with it. So what is the point of backing up the master db? – variable Oct 04 '21 at 11:35
  • When you restore the master database, using WITH REPLACE when the SQL Server is running in single-user mode, SQL Server automatically shuts down. Upon next restart of the SQL Server service, the logins present in the backed up master database will be present, along with all the other details stored in master, such as database file locations and numerous other settings. – Hannah Vernon Oct 04 '21 at 21:25