SQL 如何 远程备份数据库到本地

--1、启用xp_cmdshellUSE masterEXEC sp_configure ‘show advanced options‘, 1RECONFIGURE WITH OVERRIDEEXEC sp_configure ‘xp_cmdshell‘, 1RECONFIGURE WITH OVERRIDEEXEC sp_configure   ‘show advanced options‘, 0RECONFIGURE WITH OVERRIDE

--2、使用变量进行备份declare @db sysnamedeclare @dbpath varchar(120)set @db=‘shenyin‘--文件名根据数据库名称＋日期YYYYMMDD+扩展名set @dbpath=‘D:\‘+@db+convert(varchar(10),getdate(),112)+‘.bak‘

--3、在sql中建个映射exec master..xp_cmdshell ‘net use Y: \\192.168.0.156\DBBackup "jindou" /user:192.168.0.156\Administrator‘/* 说明: Y:是映射网络路径对应本机的盘符 192.168.0.156是我本机的IP DBBackup是我本机共享的文件夹 Administrator是登陆的用户名  "jindou" 是Administrator用户的密码*/ --4、备份（@db是要备份的数据库名称）Backup Database @db To disk=@dbpath--5、COPYdeclare @copypath varchar(200)set @copypath = ‘copy ‘+@dbpath+‘ Y:‘select @copypathexec master..xp_cmdshell @copypath --6、删除（这句可以去掉）declare @deletepath varchar(200)set @deletepath = ‘del ‘+@dbpathselect @deletepathexec master..xp_cmdshell @deletepath --7、完成后删除映射exec master..xp_cmdshell ‘net use Y: /delete‘

--8、关闭xp_cmdshellUSE master EXEC sp_configure ‘show advanced options‘, 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure ‘xp_cmdshell‘, 0 RECONFIGURE WITH OVERRIDE EXEC sp_configure   ‘show advanced options‘, 0RECONFIGURE WITH OVERRIDE 

SQL 如何 远程备份数据库到本地