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 plan for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to the nearest available point in time, prior to the disaster.

In order to create and apply a 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.

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
      TO DISK = N'F:\Backup\DEMO02.bak'

    3. Differential backup
      TO DISK = N'F:\Backup\DEMO02.bak'
    5. Transaction log backup
    6. BACKUP LOG [DEMO02]
      TO DISK = N'F:\Logs\DEMO02.log';
  6. 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.

  7. Click on the OK button to add the job step
  8. Note: to set up backup of multiple databases, simply repeat steps 4-6 and provide appropriate details for those databases

  9. In the Select a page pane click on the Schedules tab and click on the New button to create a schedule for the job
  10. Provide a job schedule name, choose a schedule type, occurring frequency and job validity date range
  11. Click OK to create a schedule, and OK again to create a job
  12. 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 stepoption 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


    May 6, 2015