Manage multiple database backups across different SQL Server instances

One of the most common ways to ensure that the recovery will be possible if a data-file corruption or any other disaster occurs is to create a recovery plans for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to a nearest available point in time, prior to disaster.

In order to create and apply successful recovery plan, it is important to create a solid backup schedule and to manage backups of multiple databases across different SQL Server instances.

In this article we will create a SQL Server scheduled backup by using a SQL Server Agent job and ApexSQL Backup.

Schedule and automate database backup(s) with SQL Server Agent

To perform this task via SQL Server Agent, we will create a SQL Server Agent job:

  1. In the SQL Server Management Studio, navigate to the SQL Server Agent node in the Object Explorer, right click on the Jobs node and choose New job from the context menu

  2. In the New Job dialog specify a name for a job and all other job details (owner, category, description)
  3. In the Select a page pane click on the Steps tab and click on the New button to create a backup step
  4. Specify the name of the step and chose Transact-SQL script (T-SQL)
  5. Depending on the type of backup being created (full, differential or transaction lob backup), paste one of the following scripts to the Command pane:

    1. Full database backup

      BACKUP DATABASE [DEMO02]
      TO DISK = N'F:\Backup\DEMO02.bak'
      WITH CHECKSUM;
      
    2. Differential backup

      BACKUP DATABASE [DEMO02]
         TO DISK = N'F:\Backup\DEMO02.bak'
         WITH DIFFERENTIAL;
         WITH CHECKSUM;
      
      GO
      
    3. Transaction log backup

      BACKUP LOG [DEMO02]
         TO DISK = N'F:\Logs\DEMO02.log';
      GO
      

    Note: To be able to create a differential or a transaction log SQL Server database backup a full database backup has to be created first.

  6. Click on the OK button to add the job step

    Note: to set up backup of multiple databases, simply repeat steps 4-6 and provide appropriate details for those databases

  7. In the Select a page pane click on the Schedules tab and click on the New button to create a schedule for the job

  8. Provide a job schedule name, choose a schedule type, occurring frequency and job validity date range

  9. Click OK to create a schedule, and OK again to create a job

With this, the job has been created and can now be found in the Object Explorer pane under the SQL Server Agent / Jobs.

To put the job in use, right click on it in the Object Explorer pane and click on the Start job at step option in the context menu

At this moment, the backup job is set and scheduled actions will complete at the specified time

There are several disadvantages to automating database backup(s) with SQL Server Agent:

  • Process of the backup job creation and scheduling via SQL Server Agent can be substantial and hard to understand
  • Inability to see both scheduled jobs and those that have already been ended
  • It is not possible to include databases from different SQL instances in the same job
  • Transact-SQL script (T-SQL) must be provided for each individual database

Schedule and automate a database backup(s) with ApexSQL Backup

Different approach to create and schedule database backup jobs in SQL Server is to ApexSQL Backup – a tool for management and automation of SQL Server backup, restore and log shipping jobs. ApexSQL Backup enables user to schedule database backup jobs through simple wizard, and allows one to overview all jobs history, schedules and results, or notify about the result on completion by email alert.

Main advantages when using ApexSQL Backup in comparison to SQL Agent jobs:

  • The backup job and schedule wizards are simple and easy to manage
  • Both scheduled jobs and historical overviews of all backup (and restore and log shipping) jobs can be used for easy tracking of all or specific jobs
  • There is no limit in number of SQL instances that can be managed at the same time
  • There is no need for any transact-SQL script (T-SQL)

To schedule and automate a database backup(s) with ApexSQL Backup, do the following:

  1. Start the application, and click on the Manage policy button in the main menu to see existing policies and create new ones

  2. Click on the New policy button to bring up the policy wizard

  3. Type policy name and description and select a backup type. Click on the Next button to advance

  4. Use existing wildcards to predetermine naming customization of backupset name, description and destination filename (or provide a specific name)

  5. Press the Next button to proceed to the next step of the wizard – Options
  6. In the Options dialog, specify desired options for the backup creation and click Next to proceed to the final step of the policy creation wizard

  7. In the Schedule step of the wizard, it is required to choose whether the backup of a SQL Server database will start immediately or will it be scheduled. If the job is scheduled, the Schedule wizard dialog will appear, which enables the user to select a job frequency, a period, and a time range

    It is also possible to set up an automatic email notification when the scheduled job is completed

  8. Click Finish and the backup policy will be created

    Now, when the policy is created, it simply needs to be deployed to as many databases on as many SQL Server instances is needed

  9. To deploy the policy, start the process by clicking on the Deploy policy button in the main ribbon

  10. Choose the policy from the list and click on the Next button

  11. Select databases from multiple SQL Servers from the Databases list

  12. Provide the backup destinations for each SQL Server instance and click Next button

  13. (Optional) Provide encryption details

  14. Inspect the policy deployment and click on the Next button to initiate the scheduling process

  15. The backup jobs schedules are now created and will occur in accordance to the schedule’s specification. The schedule will be displayed in the Schedules view, with other ongoing and finished schedules

May 6, 2015