备份数据库到其它服务器

最新推荐文章于 2022-08-20 17:15:01 发布

weixin_30621959

最新推荐文章于 2022-08-20 17:15:01 发布

阅读量396

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/kimzeng/archive/2009/11/20/1606738.html

版权

CREATE PROCEDURE [dbo].[backupDatabase]
AS
BEGIN
SET NOCOUNT ON;

--显示高级选项
exec sp_configure 'show advanced options',1
reconfigure

--允许执行x-_cmdshell
exec sp_configure 'xp_cmdshell',1
reconfigure

--添加映射驱动器
declare @string varchar(200)
set @string = 'net use z: //192.168.1.200/d$/DatabaseBackup "123456" /user:192.168.1.200\administrator'
exec master..xp_cmdshell @string

--备份数据库至本地
declare @fileName nvarchar(100)
set @fileName ='d:\DatabaseBackup\register_'+convert(char(8),getdate(),112)+'.bak'
backup database register to disk=@fileName with init

--拷贝到映射目录
declare @cmdStr nvarchar(150)
set @cmdStr= 'copy '+@fileName+ ' z:'
exec master..xp_cmdshell @cmdStr

--删除映射以及本地备份
exec master..xp_cmdshell 'net use z: /delete'
--set @cmdStr = 'del '+@str + ''
--exec master..xp_cmdshell @cmdStr

--关闭允许执行cmdshell
exec sp_configure 'xp_cmdshell',0
reconfigure
END

转载于:https://www.cnblogs.com/kimzeng/archive/2009/11/20/1606738.html