How to create and manage SQL backup policies

Performing regular database back-ups is a crucial part of any disaster recovery plan. There are many aspects that should be taken into consideration when planning long term backup strategies. It is necessary to choose the backup type and backup location and to plan how frequently to perform the backups. Additionally, the processes of verification or encryption could also be included into the plan.

For systems that contain multiple servers, automation of the process is a must. It is difficult to keep track of all the parameters, details and settings, and that is where the SQL backup policies come in handy. All of the settings could be precisely defined and applied using a single backup policy. Furthermore, the policy can be reused according to your needs, or could be setup to run automatically.

There are few ways to define and uphold the SQL backup policy. Native solutions include the configuration of SQL Server agent or SQL Server maintenance plans. There are also several 3rd party software solutions that make creating and managing SQL backup policies much easier. ApexSQL Backup is one of them, and will be used as an example for policy management in this article.

Creating and managing SQL backup policies with SQL Server Agent

To use the SQL Server Agent job for SQL policy management, it is necessary to make sure that the SQL Server Agent Service for the respective instance is up and running. The Agent Service is running if the SQL Server Agent node in object explorer can be expanded. If this is not the case, the SQL Server Agent service needs to be started manually. Go to the Control Panel/Administrative Tools/Services. Locate the SQL Server Agent service for the specific instance, mark it, and click Start the service.

As the agent service is running, the SQL backup policy can be defined by creating SQL Server Agent jobs, and scheduling them to run automatically according to your needs. Each job consists of one or more steps that are executed in the specified order. The steps are actually the T-SQL queries.

  1. Create the new agent job by expanding the SQL Server Agent node, right clicking on Jobs, and selecting New Job…:

  1. On the General page, the name for the new job should be provided. Optionally, Owner, Category, and Description could also be specified.
  2. On the Steps page, click on New… button to define the new step for the job.
  3. In New Job Step, enter the Step name, and provide the backup script in the Command field. Depending on the backup type that needs to be performed (full, differential, or transaction log backup), the following scripts could be used:

    1. Script for full database backups:
    2. USE TestDatabase
      GO
      
      BACKUP DATABASE [TestDatabase] --Database that is backed up
            TO DISK = N'F:\Backup\TestDatabase.bak'--Backup path and backup name
      WITH CHECKSUM;
      
    3. Script for differential backups:
    4. BACKUP DATABASE [TestDatabase]               
              TO DISK =   N'F:\Backup\TestDatabase.bak'
              WITH DIFFERENTIAL;
              WITH CHECKSUM;
      GO
      
    5. Script for transaction log backups:
    6. BACKUP LOG [TestDatabase]
      TO DISK = N'F:\Logs\TestDatabase.log';
      GO
      

    Note that it is necessary to create at least one full database backup, in order to be able to perform differential, or transaction log backups.

  1. Click Ok when done:

  1. It is possible to set the schedule for the created job on the Schedule page in Job Properties. To create a new schedule, click on New…:

  1. In New Job Schedule window, the name, frequency, and duration of the schedule could be specified. For this example, we will use the daily schedule, that will start immediately, with no end date. Click Ok when all required parameters are set:

  1. Optionally, Alerts and Notifications could be set for the job. Click Ok when done.
  2. To run the job, expand the Jobs node, right click on the created job, and click Start Job at Step…:

  1. If the job is configured and executed properly, the success message should be displayed. The job defined in the example should automatically perform full backup of the TestDatabase on the path F:\Backup, every day on 12 AM:

Backup policies and SQL Server Maintenance Plans

If we want to avoid using complex T-SQL scripts needed for the job definition, we can still create and manage policies by using SQL Server Maintenance Plans. Maintenance Plans provide simple user interface, and could be configured by the less experienced users. On the other hand, they lack the fine customization that could be achieved by using T-SQL scripts.

There are two ways to create the SQL backup policy by using SQL Server Maintenance Plans:

  • Create a new plan manually
  • Create a new plan with Maintenance Plan Wizard

To create a new plan manually:

  1. Expand the Management node in Object Explorer. Right click on Maintenance Plans, and select New Maintenance Plan… Provide the name for the maintenance plan:

  1. On the upper left, click on the Toolbox tab, and select Back up Database Task:

  1. Right click on the created task, and select Edit…
  2. In the General tab of the Back Up Database Task, you can choose the backup type that you want to use (full, differential, or transaction log backup), and specify the databases that you want to back up:

  1. In Destination tab, the path for the backups should be provided.
  2. In Options tab, actions such as backup compression, backup expiration, backup encryption, and backup integrity verification can be added to the task. Click Ok when done:

  1. To set up the schedule for the task, select the Schedule button in the Subplan_1 row. Set the schedule according to your needs:

  1. Save the plan by right clicking on the upper left tab (MaintenancePlan1 in this case), and selecting Save Selected Items.
  2. Saving the plan automatically creates the new Job for the SQL Server Agent with the parameters provided in the maintenance plan. To run the created job, expand SQL Server Agent and Jobsnodes, right click on the job, and select Start Job at Step…:

  1. If all actions complete without errors, you should get Success message

Another way to create a new plan is by using Maintenance Plan Wizard:

  1. Right click on Maintenance Plans under Management node in Object Explorer. Select Maintenance Plan Wizard option:

  1. In the first step of the wizard, the name for the maintenance plan must be provided. Specify if the schedule needs to be set for each task separately, or all tasks need to run with a single schedule. If decided to use singe schedule for the entire plan, click the Change… button, and set the preferred schedule. When done, click Next:

  1. The second step lists all maintenance tasks that could be integrated into the plan. The database backup tasks are also listed. Check the box in front of the backup task(s) that should be used with this plan. Optionally, more than one maintenance task can be chosen to be included in the plan. Click Next when done:

  1. The third step allows you to specify the order for the task execution:

    1. Configuration step is the same as step 6 from the previous example. In the General tab, the databases that should be backed up are selected; in Destination tab, the destination is specified; and in Options tab, additional actions such as compression, expiration, verification and encryption can be included in the task. If more than one task is selected in the previous step, the wizard will run the configuration step for each task. Click Next when done:

    1. Select Report Options step allows the user to save the maintenance reports, or to send them to the specified email address.
    2. The final step lists all actions included in the maintenance task, along with the options for each of them. If everything is displayed as planned, click Finish:

    1. The Success message is displayed after the maintenance plan is created.
    2. To execute the created maintenance plan, expand the Management and Maintenance Plansnodes in Object Explorer, right click on the created plan, and select Execute:

    1. Alternatively, you can expand SQL Server Agent and Jobs nodes, right click on the created maintenance plan, and select Start Job at Step…:

    Creating and managing SQL backup policies with ApexSQL Backup

    ApexSQL Backup is a SQL backup management tool that can create SQL backup policies for full, differential and transaction log backups and apply them to multiple databases at once. Creation and management of SQL backup policies is performed solely through graphical user interface, and it does not require the experience in using T-SQL scripts. The Command Line Interface (CLI) is also supported. In ApexSQL Backup, the policies can be created by using backup templates.

    To create a SQL backup policy using ApexSQL Backup:

    1. Click on the Manage button from the Backup templates group:

    1. When Backup templates window opens, select New:

    1. The Backup database template wizard starts. Here, specify the name for the template, choose the preferred backup type, edit backup job name, as well as backup job description and backup filename:

    1. In Schedule step, specify when the policy should be executed. To setup the schedule for the policy, select the schedule browse button. In the Schedule wizard, specify when, and how often will a job occur (frequency), as well as choose a daily frequency and a job duration (validity period). Under summary, the info about chosen schedule parameters is presented. Next step is to click on the OK button to finalize the schedule(s):

    1. In the Advanced tab, additional tasks regarding media, backup verification, transaction log, compression and cleanup interval can be integrated into the policy:

    2. If needed, set the email notifications in Notification tab. Choose to receive notifications on job failure, info, job success, canceled and/or skipped job. To add email recipients to the list, click on the Add button, and type in the email address. When done, click OK to save the changes:

    3. Click the OK button to close the Backup database template wizard and save policy settings

    4. To apply the created policy, click the Deploy button from the Backup templates group:

    5. The Deploy templates wizard starts. Select the SQL backup policy created in previous steps from the list and click the Ok button. Create and save multiple SQL backup policies and choose which one to deploy:

    6. In the Deploy databases dialog, select all databases that need to be used with the policy:

    7. In Destination column, the destination path for the backup files could be specified, either by typing the path manually, or by using the folder explorer. Click the OK button to confirm the selection:

    1. Running tasks completed windows is displayed where all pending tasks are listed. Click the Finish button when all tasks are completed:

    1. If the tasks were set to run on schedule, they can be viewed with a click on Schedules tab:

     

    April 22, 2016