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