DB-유

Building a SQL Server Backup Strategy with Maintenance Plans

· mssql, backup, maintenance plans, sql agent

Maintenance Plans get a bad reputation among DBAs, and some of it is deserved. The Shrink Database task should never have existed, and the wizard hides too much. But I still find servers where "someone runs a backup when they remember", and next to that, a decent Maintenance Plan looks great. Full, differential and log backups with cleanup, scheduled through SQL Server Agent, no code.

This is how I set one up on SQL Server 2014/2016 Standard. You need Standard or higher (Express has no Agent, so no plans), Agent set to start automatically, and a backup location with room for two full backups plus a week of differentials and logs. A UNC path is fine if the service account can write there. Databases that need point-in-time recovery must be in FULL recovery:

SELECT name, recovery_model_desc FROM sys.databases WHERE database_id > 4;

Decide the schedule first

Before opening anything, settle the recovery point objective. My default for a business application:

  • Full backup: daily at 22:00.
  • Differential backup: every 6 hours during the day (skipping the full backup hour).
  • Transaction log backup: every 15 minutes.
  • Retention: 7 days on local disk, with the backup share itself copied off-site by another job.

System databases (master, model, msdb) get a daily full backup only; they are in SIMPLE recovery and cannot have log backups.

Three sub-plans, not one

In SSMS expand Management → Maintenance Plans, right-click and choose New Maintenance Plan (not the wizard; I stick to the designer because I can see what each task does). Name it DBA - Backups.

In the designer, use the Subplan toolbar to create three sub-plans, each with its own schedule: Full, Diff, Log. Each sub-plan becomes a separate Agent job, which is what allows different schedules.

The Back Up Database tasks

Drag a Back Up Database Task into the Full sub-plan and set:

  • Backup type: Full
  • Databases: All user databases (system databases go in a fourth sub-plan)
  • Create a backup file for every database and tick Create a sub-directory for each database
  • Folder: \\backupsrv\sqlbackup\PROD01\
  • Extension: bak
  • Verify backup integrity: on
  • Compress backup (Standard edition supports compression since 2008 R2)
  • Set backup checksum: on (Options tab in 2016)

Repeat for the Diff sub-plan with type Differential (extension dif) and for Log with type Transaction Log (extension trn). On the Log task, set Databases to All user databases and tick Ignore databases where the state is not online. Databases in SIMPLE recovery are skipped automatically by the log task in 2016; in 2014 and earlier you have to pick the databases explicitly or the task fails.

The T-SQL the task generates (visible under View T-SQL) should look like this:

BACKUP DATABASE [Sales] TO DISK = N'\\backupsrv\sqlbackup\PROD01\Sales\Sales_backup_2017_05_05_220001_1234567.bak'
WITH NOFORMAT, NOINIT, NAME = N'Sales_backup', SKIP, REWIND, NOUNLOAD, COMPRESSION, CHECKSUM, STATS = 10;
RESTORE VERIFYONLY FROM DISK = N'\\backupsrv\sqlbackup\PROD01\Sales\Sales_backup_2017_05_05_220001_1234567.bak' WITH CHECKSUM;

Cleanup

Add a Maintenance Cleanup Task to the Full sub-plan, connected after the backup task with a green (success) constraint. Configure it to delete Backup files in the folder above, Include first-level subfolders, extension bak, older than 1 week. Add two more cleanup tasks for dif and trn. One task per extension is required; the task does not accept multiple extensions.

Also add a History Cleanup Task (remove backup and restore history, job history and maintenance plan history older than 4 weeks) so msdb does not grow forever.

Schedules and reporting

Click the calendar icon on each sub-plan and enter the schedule from above. For the log sub-plan choose Occurs every 15 minute(s), daily.

Under Reporting and Logging on the toolbar, enable Log extended information and Generate a text file report to a folder such as D:\MaintenanceReports\. Always turn this on. When a task fails, the report tells you which database and why, and job history alone often doesn't.

Did it work?

Right-click the DBA - Backups.Log job in SQL Server Agent → Jobs and choose Start Job at Step. Then confirm backups landed:

SELECT TOP 20 database_name, type, backup_start_date, backup_size / 1024 / 1024 AS size_mb, physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily mf ON mf.media_set_id = bs.media_set_id
ORDER BY backup_start_date DESC;

type is D for full, I for differential and L for log. Once a week, actually restore one of the backups to a test instance. A backup that has never been restored is a hope, not a strategy.

When it goes wrong

  • "BACKUP LOG cannot be performed because there is no current database backup." A database was just created or switched to FULL recovery and the log job ran before the first full backup. Run the Full sub-plan once manually.
  • "Cannot open backup device '\\backupsrv\sqlbackup\...'. Operating system error 5 (Access is denied.)" The SQL Server service account (not the Agent account) writes the backup file, so it needs Modify permission on the share and the NTFS folder.
  • "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE." On older versions, the log task does not skip SIMPLE databases. Either change the recovery model or list the databases individually in the task.
  • Cleanup task deletes nothing. The most common reason is the extension typed as .bak with a leading dot. It must be bak.

This won't scale to dozens of instances. If you get there, look at Ola Hallengren's maintenance solution next.