利用SQL2000的DTS调度进行定期数据库备份

1.新建作业‘数据库备份’，如下图所示

2.在作业中新建‘步骤’，将下面的备份语句嵌入，并在‘调度’中设置合理的调度时间

3.备份语句

（1）本地备份

declare
@DW_Olap varchar(1000)

select @DW_Olap='D:/BACKUP/OLAP_BACKUP'++convert(varchar(2),datepart(weekday,getdate()))++'.bak'

---为节省空间，仅保留一周内的备份

backup database DW_Olap  to disk=@DW_Olap  WITH INIT

 

（2）跨服务器备份

declare
@dw_base_str varchar(1000),
@dw_clean_str varchar(1000),
@dw_olap_str varchar(1000),
@msdb_str varchar(1000),
@master_str varchar(1000)

exec master..xp_cmdshell 'net use //10.99.36.172/sqlbake /del'
exec master..xp_cmdshell 'net use //10.99.36.172/SqlBake sql_user2008 /user:lenged/sql_user'

select @dw_base_str='//10.99.36.172/sqlbake/dw_base/dw_base'++convert(varchar(2),datepart(weekday,getdate()))++'.bak'
select @dw_clean_str='//10.99.36.172/sqlbake/dw_clean/dw_clean'++convert(varchar(2),datepart(weekday,getdate()))++'.bak'
select @dw_olap_str='//10.99.36.172/sqlbake/dw_olap/dw_olap'++convert(varchar(2),datepart(weekday,getdate()))++'.bak'
select @msdb_str='//10.99.36.172/sqlbake/msdb/msdb'++convert(varchar(2),datepart(weekday,getdate()))++'.bak'
select @master_str='//10.99.36.172/sqlbake/master/master'++convert(varchar(2),datepart(weekday,getdate()))++'.bak'

---为节省空间，仅保留一周内的备份

backup database dw_base  to disk=@dw_base_str  WITH INIT
backup database dw_clean  to disk=@dw_clean_str WITH INIT
backup database dw_olap  to disk=@dw_olap_str WITH INIT
backup database msdb  to disk=@msdb_str WITH INIT
backup database master  to disk=@master_str WITH INIT