SqlServer数据库自动备份作业
工具/原料
ThinkPadR480
Windows7
SqlServer2008以上
方法/步骤
1、/******对象:Job[数据库备份]******/BEGINTRANSACTIONDECLARE@ReturnCodeINTSELECT@ReturnCode=0
2、/******对象:JobCategory[DatabaseMaintenance]*涯箨唁峦*****/IFNOTEXISTS(SELECTnameFROMmsdb缕计瓤账.dbo.syscategoriesWHEREname=N'DatabaseMaintenance'ANDcategory_class=1)BEGINEXEC@ReturnCode=msdb.dbo.sp_add_category@class=N'JOB',@type=N'LOCAL',@name=N'DatabaseMaintenance'IF(@@ERROR<>0OR@ReturnCode<>0)GOTOQuitWithRollbackEND
3、DECLARE@jobIdBINARY(16)EXEC@ReturnCode=msdb.dbo.sp_add_job@job_name=N'数据库备份1',@enabled=1,@notify_level_eventlog=0,@notify_level_email=0,@notify_level_netsend=0,@notify_level_page=0,@delete_level=0,@description=N'数据库备份1',@category_name=N'DatabaseMaintenance',@owner_login_name=N'sa',@job_id=@jobIdOUTPUT
4、IF(@@ERROR<>0OR氆尧彻性@ReturnCode<>0)GOTOQuitWithRo造婷用痃llback/******对象:Step[数据库备份]******/EXEC@ReturnCode=msdb.dbo.sp_add_jobstep@job_id=@jobId,@step_name=N'数据库备份1',@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@filenamenvarchar(200),@dbnamevarchar(50)--@dbname需要备份的数据库名称set@dbname=''数据库名称''--@filename完成备份的数据库备份文件名set@filename=''D:\DatabaseBak\''+@dbname+''_''+(replace(replace(replace(CONVERT(varchar,getdate(),120),''-'',''_''),'''',''-''),'':'',''_''))+''.bak''--数据库备份文件保存的磁盘地址，以及备份的名称。已经使D用日期来区分了BACKUPDATABASE@dbnameTODISK=@filename',--@database_name需要备份的数据库名称@database_name=N'数据库名称',@flags=0
5、IF(@@ERROR<>0OR氆尧彻性@ReturnCode<>0)GOTOQuitWithRo造婷用痃llbackEXEC@ReturnCode=msdb.dbo.sp_update_job@job_id=@jobId,@start_step_id=1IF(@@ERROR<>0OR@ReturnCode<>0)GOTOQuitWithRollbackEXEC@ReturnCode=msdb.dbo.sp_add_jobschedule@job_id=@jobId,@name=N'数据库备份1',@enabled=1,@freq_type=4,@freq_interval=1,@freq_subday_type=8,@freq_subday_interval=12,@freq_relative_interval=0,@freq_recurrence_factor=0,@active_start_date=20131230,@active_end_date=99991231,@active_start_time=0,@active_end_time=235959
6、IF(@@ERROR<>0OR@ReturnCode<>0)GOTOQuitWithRollbackEXEC@ReturnCode=msdb.dbo.sp_add_jobserver@job_id=@jobId,@server_name=N'(local)'IF(@@ERROR<>0OR@ReturnCode<>0)GOTOQuitWithRollbackCOMMITTRANSACTIONGOTOEndSaveQuitWithRollback:IF(@@TRANCOUNT>0)ROLLBACKTRANSACTIONEndSave: