備份資料庫到其它伺服器

 

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