Backup recommendations for SQL system databases

SUMMARY

Backup recommendations for SQL system databases

ISSUE

Purpose

Unitrends backup recommendations for SQL system databases.

Description

SQL system databases (Master, Msdb, Model, Distribution, ReportServer, and ReportServerTempDB) are essential for all Microsoft SQL Server operations. If any system database fails or becomes corrupt, the SQL Instance becomes unavailable. It is important to protect these databases, however unlike a user database that handles real-time customer transactions, it is not necessary to perform backups on an hourly basis.

Resolution

You can protect SQL data using the Unitrends Windows agent or, for VMware environments, you can run application-aware vProtect backups. If you have a VMware environment, see Best practices for protecting VMware virtual machines for a comparison of agent versus agentless SQL backps.

Application-aware vProtect backups

 If you are running application-aware vProtect backups, change the database recovery model to SIMPLE for proper transaction log truncation. If you do not use the SIMPLE recovery model, the transaction log continues to grow. 

Windows agent backups

If you are running backups with the Unitrends Windows agent, follow these recommendations.

--Create one schedule for master, model, and msdb databases and run a full backup weekly.
--Back up all system databases before and after each of the following:
  • installing a server or SQL service pack
  • installing hot fixes
  • performing a cumulative update,
  • performing login changes
  • performing job changes
  • performing operator changes
  • performing database configuration changes
  • performing SSIS package changes
  • Changing replication settings 
--Perform an on-demand full backup of the master database in these cases:
  • After creating user databases
  • After modifying SQL Instance configuration values
  • After modifying SQL logins and credentials
  • After making any change to the master database

--Recommendations for other databases

  • The Distribution database is available when replication is configured and the server is acting as a distributor. In the case of transactional replication, it is advisable to schedule full backup weekly, differentials daily and regular transactional log backups at least every 4 hours.
  • The Resource database is a read-only, hidden database that contains all the system objects included in the SQL server. Perform a selective file-based backup of mssqlsystemresource.mdf and mssqlsystemresource.ldf found in the following locations:

           In case of SQL Server 2005: ...\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

         In SQL Server 2008: ...\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn\

  • The Tempdb database is recreated each time the SQL Instance is started. Backup of Tempdb is not required.

Article link

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Contact us