以文本方式查看主题

-  昂捷论坛  (http://enjoyit.com.cn/bbs/index.asp)
--  □-系统配置管理类  (http://enjoyit.com.cn/bbs/list.asp?boardid=34)
----  问：简单做异地备份的脚本是怎样的？  (http://enjoyit.com.cn/bbs/dispbbs.asp?boardid=34&id=3980)
--  作者：飞絮
--  发布时间：2008/6/3 22:18:23
--  问：简单做异地备份的脚本是怎样的？

答：在数据库任务中以以下脚本可实现数据库的定时备份：

 


-- 异地备份

 

-- 第一步：使用net命令连接异地服务器
-- To allow advanced options to be changed.
EXEC sp_configure \'show advanced options\', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure \'xp_cmdshell\', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

EXEC xp_cmdshell \'net use \\\\192.168.1.6\\d$\\shq_bak password /user:administrator\',no_output

-- 第二步：执行备份

declare @day int
set @day = datepart(weekday,getdate())
if (@day in (3,5,7)) begin
 backup database enjoy_shq to disk=\'\\\\192.168.1.6\\d$\\shq_bak\\enjoy_shq01.bak\' with init
end else if (@day in (2,4,6)) begin
 backup database enjoy_shq to disk=\'\\\\192.168.1.6\\d$\\shq_bak\\enjoy_shq02.bak\' with init
end else begin
 backup database enjoy_shq to disk=\'\\\\192.168.1.6\\d$\\shq_bak\\enjoy_shq01.bak\' with init
end