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