Decide on a SQL Server backup strategy
- Last UpdatedOct 12, 2022
- 2 minute read
Three backup strategies are considered in this document. Each strategy has the common aim of storing database backups and transaction log backups on a network share so that they are protected from malfunction of the SQL Server hardware. Another important goal is to manage the backup files so that they don’t use too much storage. It is not necessary to keep a year’s worth of backup files.
Strategy 1
Configure the Maintenance Plan to backup to a directory on the network and manage the files.
-
The job would be scheduled on the SQL Server and executed by the SQL Server Agent.
-
The Maintenance Plan and schedule would be backed up and restored with the msdb database.
-
The MSSQLSERVER service would run under a Windows account which has network access and a password that does not expire.
Strategy 2
Configure the Maintenance Plan to backup to a local directory and use a DTS package (VBScript) to copy the files to a network location and manage the files in both the local and the network directories.
-
The Job would be scheduled on the SQL Server and executed by the SQL Server Agent.
-
The Maintenance Plan, schedule and DTS package are backed up and restored with the msdb database.
-
The SQLSERVERAGENT service would run under a Windows account which has network access and a password that does not expire.
Strategy 3
Backup to a local directory and use a stand-alone VBScript file (the same VBScript as Strategy 2) to copy the files to a network location and manage the files in the local and the network directories.
-
The job would be scheduled in and executed by the Windows Scheduler.
-
The Maintenance Plan would be backed up and restored with the msdb database.
-
The VBScript file would be backed up and restored manually.
-
The Windows schedule would have to be documented and recreated during disaster recovery.
-
A Windows account with network access and a password that does not expire would be required for the Windows scheduled job.
-
Both the MSSQLSERVER and SQLSERVERAGENT services would be able to run under the Local System account.
It is difficult to recommend any one of the options to cover every circumstance. Strategies 1 and 2 have an advantage in that everything is backed up and restored with the msdb database. This can reduce the chances of neglecting to restore the backup strategy, in the heat of the moment, while recovering from a disaster.
The main advantage of Strategy 3 is that there is no requirement to change the logins for the MSSQLSERVER and SQLSERVERAGENT services, something else that can be neglected during disaster recovery and cause a backup job to not succeed.