Standardize SQL Server backups and maintain existing job schedules

By: Burt King   |   Updated: 2011-12-15   |   Comments (2)   |   Related: > Backup

Problem

After starting a new SQL Server DBA position I discovered that our servers, and there were hundreds, all had backups running, but they were all different. Some had backups to local storage, some across the network or to the SAN, and the list went on and on. I wanted to change them all to a standard script and keep the existing schedules, so I needed to find a way to do this without manually accessing every SQL Server.  Check out this tip to learn about how to standardize your SQL Server backups.

Solution

Solving this problem was really pretty simple once I sat down and reviewed the options. SQL Server provides the sp_update_jobstep and sp_update_jobschedule system stored procedures which can help with the work, but again, I didn't want to logon to each SQL Server.  This is where SQL Server Multi-Query came into place. 

In this tip we will cover the following topics:

• Setup Server Groups in SQL Server Management Studio Registered Servers Interface
• SQL Server Management Studio Multi Query Interface
• Capture the SQL Server Agent Job Information via Multi Query
• Create a table on each SQL Server and store the schedule
• Drop all the existing full backup jobs
• Create standardized SQL Server Agent Backup job
• Update each server with the previously captured schedule
• Push the schedule down to each server

Setup Server Groups in SQL Server Management Studio Registered Servers Interface

To get started, I make use of SQL Server 2008's ability to query multiple servers from one window in SQL Server Management Studio (SSMS). If you're not familiar with it, it's easy to use and a great addition to SSMS. Simply navigate to the Register Servers interface by clicking on the 'View' menu then the 'Registered Servers' option.  Once on this interface you can add new server groups by right clicking on the 'Local Server Groups' folder, then the 'New Server Group' option and then complete interface to add the group name and group description.  Once you have your groups setup as shown below, you can right click on a server group and select, 'New Query' as shown below.

SQL Server Management Studio Multi Query Interface

At this point, you will be presented with a new query window to issue a command against multiple servers in one step. You will know you are on the right path when the ribbon bar at the bottom changes color and it announces that you are connected to more than one server as shown below. In our example, we are successfully connected to 4 servers.

Capture the SQL Server Agent Job Information via Multi Query

At this point we can execute any query normally pending the databases and objects exist on all of the instances in the server group.  At this point we have laid the ground work to access the environment, so let's get back to our real problem: many SQL Servers, all with different backup jobs, schedules and schedule names. One way to tackle this issue is:

1. Capture all the schedules
2. Drop all the existing jobs
3. Add in new jobs that are all standardized with the same backup script
4. Update all those jobs with the schedule, which we gathered in step 1

declare @name nvarchar(128)
declare @enabled tinyint
declare @freq_type int
declare @freq_interval int
declare @active_start_time int
declare @freq_relative_interval int
declare @freq_subday_interval int
declare @freq_subday_type int
SELECT @name =  syssched.name 
    ,@enabled = sysjobs.enabled 
    ,@freq_type = syssched.freq_type
    ,@freq_interval = syssched.freq_interval
    ,@active_start_time = syssched.active_start_time
    ,@freq_relative_interval = syssched.freq_relative_interval
    ,@freq_subday_interval = syssched.freq_subday_interval
    ,@freq_subday_type = syssched.freq_subday_type
FROM msdb.dbo.sysjobs                
INNER JOIN msdb.dbo.sysjobschedules   sysjobsched
 ON sysjobs.job_id = sysjobsched.job_id
INNER JOIN msdb.dbo.sysschedules      syssched
 ON sysjobsched.schedule_id = syssched.schedule_id
WHERE sysjobs.name like '%Backup%'
select @name, @enabled, @freq_type, @freq_interval, @active_start_time,
@freq_relative_interval, @freq_subday_interval, @freq_subday_type

Thus, if you run this as a multi server query you can capture the schedule for all the backup jobs assuming ‘Backup' is in the name. Now that we have a way to get the schedule for all the jobs, we are going to store them in a table in the master database on each server so that we can later pull the information.

Create a table on each SQL Server and store the schedule

Run the following code to create a table to store the schedule for each server in its master database:

if not exists (select * from sys.objects 
  where [OBJECT_ID] = object_id('tbl_backup_schedules'))
 begin
  create table tbl_backup_schedules
  (
  configValue nvarchar(250) not null,
  dateVersion datetime not null,
  param1  nvarchar(4000),
  param2 nvarchar(4000),
  param3 nvarchar(4000),
  param4 nvarchar(4000),
  param5 nvarchar(4000),
  param6 nvarchar(4000),
  param7 nvarchar(4000),
  param8 nvarchar(4000),
  param9 nvarchar(4000),
  )
 end

Again, this should be run using SQL Server 2008 multi server query which will create the table on each server.

Now we will populate the table with this query:

declare @name nvarchar(128)declare @enabled tinyint
declare @freq_type int
declare @freq_interval int
declare @active_start_time int
declare @freq_relative_interval int
declare @freq_subday_interval int
declare @freq_subday_type int
SELECT @name =  syssched.name 
     ,@enabled = sysjobs.enabled 
     ,@freq_type = syssched.freq_type
     ,@freq_interval = syssched.freq_interval
     ,@active_start_time = syssched.active_start_time
     ,@freq_relative_interval = syssched.freq_relative_interval
     ,@freq_subday_interval = syssched.freq_subday_interval
     ,@freq_subday_type = syssched.freq_subday_type
FROM msdb.dbo.sysjobs                
INNER JOIN msdb.dbo.sysjobschedules   sysjobsched
 ON sysjobs.job_id = sysjobsched.job_id
INNER JOIN msdb.dbo.sysschedules      syssched
 ON sysjobsched.schedule_id = syssched.schedule_id
WHERE sysjobs.name like '%Backup%'
           
 
INSERT INTO master.dbo.tbl_backup_schedules (configValue, dateVersion, param1, param2, 
param3, param4, param5, param6, param7, param8)
VALUES ('FullBackupSchedule', GETDATE(), @name, @enabled, @freq_type, @freq_interval, 
@active_start_time, @freq_relative_interval, @freq_subday_interval, @freq_subday_type)

Unfortunately, you will need to poke around a bit to make sure you gathered all of the schedules and not the transaction log backup schedules. Since SQL Server multi server query returns the data in the order it receives it you will need to be vigilant and make sure you have the full backup schedule for every server.

Now run a multi server query to get all those rows from tbl_backup_schedules and verify you have the schedules.

SELECT * from master.dbo.tbl_backup_schedules

You should see something like this:

You now have the existing name of every job (param1) as well as the schedule in parameters 1-8. In this example, param5 contains the time for the jobs to start.  You will notice it's not exactly a time format, but rather it's the time represented as HHMMSS. So in our example, you will see a number of jobs starting at 2:00 AM, 3:00 and so forth. For more information on the specifics check out the Microsoft article on how to use sp_update_schedule which we will use later to push the schedule back to each server.

Drop all the existing full backup jobs

Now that we have the schedules stored in a table we can proceed with Step 2; drop all the existing jobs because we are going to replace them with a standardized job. It should be noted that we're assuming your MSDB database on each server is backed up prior to making these changes. Recall that this is where job information is stored and you should be prepared to recover MSDB in the event of disaster. That said, this is also your last chance to verify you have all the schedules so practicing the carpenters mantra, "Measure Twice, Cut Once", it's a good chance to double check that you have every server covered before proceeding. Modify the script below as needed to insure you delete the full backup SQL Agent Job on each server (since the jobs are different names across the environment, you're going to have to be extra diligent here):

EXEC sp_delete_job @job_name = N'NightlyBackups' ;

Create standardized SQL Server Agent Backup job

Once we have With that we have now completed steps one and two and we're ready for step three; we add in a new standardized job that has the same name, parameters, and so forth. I'm going to take a certain number of liberties here. First, that you know how to create a backup job that you want to use on all of the servers, and second, that you're familiar with SQL Server Managements Studio's ability to script the job out by right clicking on the job and selecting, "Script the job as create to..."

Therefore, for step 3 you have some homework. Create a backup job that will be standardized and then script it out. Bear in mind that you will likely be using sp_update_jobstep to adjust servers afterwards since you will probably have a number of different file system paths to account for. There are a number of excellent backup and recovery tips on MSSQLTips which can help with this.  Once you have your backup logic run the script on the servers. Since all of the backups jobs now have the same name we can begin to leverage our multi server query ability to work with all of the servers from one window instead of one at a time.

Update each server with the previously captured schedule

We're going to be using sp_update_schedule to update each server with the schedule we previously recorded in the tbl_backup_schedules table on each server.

One thing to notice is that we need to pass in the schedule name which we're going to pull from param1 in the tbl_backup_schedules table. You may recall that param1 currently has the old backup schedule name so we need to run a housecleaning chore and update that field with the name we created when we added the job into each server. Simply run this statement which will provide param1 with the new standardized backup schedule name:

UPDATE dbo.tbl_backup_schedules 
SET param1 = "Standardized Full Backup"
WHERE configValue = "FullBackupSchedule" 

Push the schedule down to each server

Now that we have param1 populated with 'Standardized Full Backup' as the name, we're ready for the final step, updating all of these jobs to have their previous schedule. The following script reads the data out of the tbl_backup_schedules and pushes it down to each server.

declare @name nvarchar(128)
declare @enabled tinyint
declare @freq_type int
declare @freq_interval int
declare @active_start_time int
declare @freq_relative_interval int
declare @freq_subday_interval int
declare @freq_subday_type int
SELECT  @name=param1, 
 @enabled =param2, 
 @freq_type = param3,
 @freq_interval = param4, 
 @active_start_time = param5, 
 @freq_relative_interval=param6,
 @freq_subday_interval = param7, 
 @freq_subday_type = param8 
FROM master.dbo.tbl_backup_schedules 
WHERE configValue = 'FullBackupSchedule'
     
exec sp_update_schedule @name = @name, 
 @enabled = @enabled, 
 @freq_type = @freq_type, 
 @freq_interval = @freq_interval,
 @active_start_time = @active_start_time , 
 freq_relative_interval = @freq_relative_interval, 
 @freq_subday_interval = @freq_subday_interval , 
 @freq_subday_type = @freq_subday_type

Next Steps

• Now that the jobs on each server have been updated with the new SQL Agent job and the old schedule we can proceed with the optional requirement to update the job steps, if necessary, to point to the correct network share, file system, etc. Take a look at sp_update_jobstep from Microsoft to learn how. Now that all the jobs have the same name and the step is named the same too, we're ready to move forward and enjoy the fact we now have standardized backups.
• Review documentation on working with SQL Agent jobs
• Review backup and recovery of the MSDB
• Ensure all jobs are pointing to the correct network or local file system path
• Verify backups work!
• Check out these additional resources:
  • sp_update_schedule (Transact-SQL)
  • sp_update_jobstep (Transact-SQL)
  • All MSSQLTips.com Backup and Recovery Tips

About the author

Burt King is Senior Database Administrator at enservio with more than 15 years experience with SQL Server and has contributed to MSSQLTips.com since 2011.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips