以星期为周期备份数据到另一台机的网络盘上

1.先在D盘下创建一个备份目录: D:\SQLDBBackup\

 

2.在数据库下执行如下语句:

Use master go   Create Procedure MQ_SQLDBBackUpEx     @cPath  AS  VARCHAR(200)     ,@cDatabase AS  VARCHAR(200)  WITH ENCRYPTION  as  begin       Declare @cWeek as nvarchar(12)     Declare @backupfile as nvarchar(200)             Set @cWeek = DATENAME(WEEKDAY,GETDATE())           set @backupfile = @cPath+Ltrim(Rtrim(@cDatabase))+'_'+@cWeek+'.bak'           backup database @cDatabase to disk=@backupfile With FORMAT,COMPRESSION End

 

 

3.然后在数据库下执行如下语句执行备份测试:

Use master go   Exec  MQ_SQLDBBackUpEx 'D:\SQLDBBackup\','HTMOSAIC'

 

在目录下能看到 D:\SQLDBBackup\HTMOSAIC_[星期].BAK 的文件即表示上面的语已无问题.

 

4.最后再执行如下语句生成定时作业(需要先启动 数据库的SQL Server代理 )

/******    Job [MQ_备分数据库_HTMOSAIC]    Date: 03/28/2019 13:30:26 ******/ Use master go   BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 03/28/2019 13:30:27 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   END   DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MQ_备分数据库_HTMOSAIC',        @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'[Uncategorized (Local)]',        @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object:  Step [MQBAK__HTMOSAIC]    Script Date: 03/28/2019 13:30:27 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'MQBAK__HTMOSAIC',        @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'Exec  MQ_SQLDBBackUpEx ''D:\SQLDBBackup\'',''HTMOSAIC''',        @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'TM_MQBK_HTMOSAIC',        @enabled=1,        @freq_type=4,        @freq_interval=1,        @freq_subday_type=1,        @freq_subday_interval=0,        @freq_relative_interval=0,        @freq_recurrence_factor=0,        @active_start_date=20190328,        @active_end_date=99991231,        @active_start_time=30011,       --注意这里面要修改: 现在是03:00:11分开始备分,改发你要执行的时间        @active_end_time=235959,        @schedule_uid=N'abdeef03-e32a-42d8-a898-e1b9c4d453b2' 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:   GO

 

 

         以上步聚会在每天执行备份并生成 [数据库名]+[星期名].BAK 的备份文件. 你再借用其他工具把相应文件COPY到网络盘.

 

         备分不同的数据库时, 你把第3,4步的语句里的 HTMOSAIC 字符全部换成 你其他数据库名称即可

 

 

--完结--