远程服务器备份数据库

–远程服务器备份数据库
EXEC sp_configure ‘show advanced options’, 1;RECONFIGURE;
EXEC sp_configure ‘xp_cmdshell’, 1;RECONFIGURE;

declare @machine nvarchar(50) = ‘12.1.4.1’ --服务器IP
declare @path nvarchar(50) = ‘bacabase’ --服务器共享目录名称
declare @user nvarchar(50) = ‘administrator’ --服务器的用户名
declare @pwd nvarchar(50) = ‘aitc@15’ --服务器的密码
declare @database nvarchar(50) = ‘WorksB’ --本地要备份的数据库名称

if left(@path,1) != ‘’ set @path = ‘’+@path
declare @sql nvarchar(800) = ‘net use \’+@machine+@path+’ “‘+@pwd+’” /user:‘+@machine+’'+@user
–建立连接类似：‘net use \192.168.200.175\208bf " " /user:192.168.200.175\happy’
Exec xp_cmdshell @sql

declare @date datetime = getdate();
Declare @fileName nvarchar(200) = ‘\’+@machine+@path+‘’+@database + replace(Convert(nvarchar,@date,120 ) ,‘:’,‘_’) + ‘.bak’
–备份数据库
Backup Database orksDB To disk = @fileName With Format

set @sql = ‘net use \’+@machine+@path+’
/delete’
–断开连接类似：‘net use \192.168.200.175\208bf /delete’
Exec xp_cmdshell @sql