利用SQL Server代理自动备份数据库

DECLARE @bakName varchar ( 500 )

SET @bakName = ' E:\数据库备份\Lab_ '
+ REPLACE ( REPLACE ( REPLACE ( REPLACE ( CONVERT ( varchar ( 50 ), GETDATE (), 21 ), ' - ' , '' ), ' : ' , '' ), ' . ' , '' ), ' ' , '' ) + ' .bak '

BACKUP DATABASE [ Lab ] TO
DISK = @bakName
WITH NOFORMAT, NOINIT,
NAME = N ' Lab-完整 数据库 备份 ' ,
SKIP,NOREWIND,NOUNLOAD,STATS = 10

GO

　　在SQL Server中新建查询，输入以下SQL语句，即可以在“SQL Server代理”中新建作业，用于自动备份数据库：

USE [ msdb ]
GO
/* ***** 对象: Job [数据库Lab备份] 脚本日期: 02/20/2011 21:35:28 ***** */
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/* ***** 对象: JobCategory [Database Maintenance] 脚本日期: 02/20/2011 21:35:28 ***** */
IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N ' Database Maintenance ' AND category_class = 1 )
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N ' JOB ' , @type = N ' LOCAL ' , @name = N ' Database Maintenance '
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback

END

DECLARE @jobId BINARY ( 16 )
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N ' 数据库Lab备份 ' ,
@enabled = 1 ,
@notify_level_eventlog = 0 ,
@notify_level_email = 0 ,
@notify_level_netsend = 0 ,
@notify_level_page = 0 ,
@delete_level = 0 ,
@description = N ' 无描述。 ' ,
@category_name = N ' Database Maintenance ' ,
@owner_login_name = N ' WEBSERVER\Administrator ' , @job_id = @jobId OUTPUT
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
/* ***** 对象: Step [备份Lab数据库] 脚本日期: 02/20/2011 21:35:28 ***** */
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId , @step_name = N ' 备份Lab数据库 ' ,
@step_id = 1 ,
@cmdexec_success_code = 0 ,
@on_success_action = 1 ,
@on_success_step_id = 0 ,
@on_fail_action = 2 ,
@on_fail_step_id = 0 ,
@retry_attempts = 0 ,
@retry_interval = 0 ,
@os_run_priority = 0 , @subsystem = N ' TSQL ' ,
@command = N ' DECLARE @bakName varchar(500)

SET @bakName = '' E:\数据库备份\Lab_ ''
+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(50), GETDATE(), 21), '' - '' , '''' ), '' : '' , '''' ), '' . '' , '''' ), '' '' , '''' ) + '' .bak ''

BACKUP DATABASE [Lab] TO
DISK = @bakName
WITH NOFORMAT, NOINIT,
NAME = N '' Lab-完整 数据库 备份 '' ,
SKIP,NOREWIND,NOUNLOAD,STATS = 10

GO ' ,
@database_name = N ' master ' ,
@flags = 0
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId , @start_step_id = 1
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId , @name = N ' 数据库Lab备份 ' ,
@enabled = 1 ,
@freq_type = 4 ,
@freq_interval = 1 ,
@freq_subday_type = 8 ,
@freq_subday_interval = 4 ,
@freq_relative_interval = 0 ,
@freq_recurrence_factor = 0 ,
@active_start_date = 20110220 ,
@active_end_date = 99991231 ,
@active_start_time = 0 ,
@active_end_time = 235959
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId , @server_name = N ' (local) '
IF ( @@ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF ( @@TRANCOUNT > 0 ) ROLLBACK TRANSACTION
EndSave:

转载于:https://www.cnblogs.com/hechaoyang/archive/2011/02/20/1959272.html