Microsoft SQL Server Front End Blog

The writers of this blog are a part of the Microsoft SQL Server Manageability team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Agent, Data collector and other tools

Automatic backups, one per day, and with different backup file names

Automatic backups, one per day, and with different backup file names

Rate This
  • Comments 1

 

SQL Agent can be used to perform scheduled backup job to backup a database to a unique file name that is constructed based on the system date.  To construct a unique file name, you can use SQL Agent’s token substitution mechanism. You can read more about token substitution  at  http://msdn.microsoft.com/en-us/library/ms175575.aspx

 

Here is the T-SQL Script to create a backup job on database “testDatabase”

In T-SQL job step definition, you can create a job step with following command. In the example below, when SQL Agent executes the job, $(ESCAPE_NONE(DATE)) is replaced with string“YYYYMMDD”

 

 1: BACKUP DATABASE [testDatabase]  
 2: TO  DISK = N'C:\DbBackups\DemoDB_$(ESCAPE_NONE(DATE)).bak' 
 3:  

 

Script to create a  T-SQL job that uses agent token substitution, recurring daily schedule @ 3:00 AM. Following script assumes that testDatabase exists and also backup file folder c:\DbBackups should be writable by SQL Agent service account

Full Script:

 1: USE [msdb]
 2: GO
 3:  
 4: BEGIN TRANSACTION
 5: DECLARE @ReturnCode INT
 6: SELECT @ReturnCode = 0
 7:  
 8: DECLARE @jobId BINARY(16)
 9: EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Backup database every day', 
 10:         @enabled=1, 
 11:         @notify_level_eventlog=0, 
 12:         @notify_level_email=0, 
 13:         @notify_level_netsend=0, 
 14:         @notify_level_page=0, 
 15:         @delete_level=0, 
 16:         @description=N'Backup database everyday.', 
 17:         @job_id = @jobId OUTPUT
 18: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 19:  
 20: EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup database step', 
 21:         @step_id=1, 
 22:         @cmdexec_success_code=0, 
 23:         @on_success_action=1, 
 24:         @on_success_step_id=0, 
 25:         @on_fail_action=2, 
 26:         @on_fail_step_id=0, 
 27:         @retry_attempts=0, 
 28:         @retry_interval=0, 
 29:         @os_run_priority=0, @subsystem=N'TSQL', 
 30:         @command=N'BACKUP DATABASE [testDatabase] 
 31: TO  DISK = N''C:\DbBackups\DemoDB_$(ESCAPE_NONE(DATE)).bak'' 
 32:  
 33: ', 
 34:         @database_name=N'master', 
 35:         @flags=0
 36:  
 37: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 38:  
 39: EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 40:  
 41: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 42:  
 43: EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily Schedule', 
 44:         @enabled=1, 
 45:         @freq_type=4, 
 46:         @freq_interval=1, 
 47:         @freq_subday_type=1, 
 48:         @freq_subday_interval=0, 
 49:         @freq_relative_interval=0, 
 50:         @freq_recurrence_factor=0, 
 51:         @active_start_date=20111109, 
 52:         @active_end_date=99991231, 
 53:         @active_start_time=30000, 
 54:         @active_end_time=235959, 
 55:         @schedule_uid=N'04644a19-4059-440d-a366-897cfd76dcd7'
 56: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 57:  
 58: EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 59:  
 60: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 61:  
 62: COMMIT TRANSACTION
 63:  
 64: GOTO EndSave
 65: QuitWithRollback:
 66:     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
 67: EndSave:
 68:  
 69: GO
 70:  
 71:  

 

 

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

Attachment: AutomatedDailyBackups.sql
Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Wow! its great, thanks for sharing your ideas, i will look forward for more interesting posts from you.

    <a href="www.sabersync.com/">Backup and Restore</a>

Page 1 of 1 (1 items)