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

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

   2. Differential backup

   --WARNING! ERRORS ENCOUNTERED DURING SQL PARSING!
   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 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

 

Related posts:

1. How to create a simple database recovery solution using SQL Server log shipping
2. How to schedule a database backup restore in SQL Server
3. How to perform a SQL backup and restore history cleanup
4. How to create and manage SQL backup policies
5. How to create and manage database backup chains in SQL Server
May 6, 2015