SQL 怎样 远程备份数据库到本地
SQL 怎样 远程备份数据库到本地
--1、启用xp_cmdshell USE master EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
--2、使用变量进行备份 declare @db sysname declare @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是登陆的username "jindou" 是Administrator用户的password */ --4、备份(@db是要备份的数据库名称) Backup Database @db To disk=@dbpath --5、COPY declare @copypath varchar(200) set @copypath = 'copy '+@dbpath+' Y:' select @copypath exec master..xp_cmdshell @copypath --6、删除(这句能够去掉) declare @deletepath varchar(200) set @deletepath = 'del '+@dbpath select @deletepath exec master..xp_cmdshell @deletepath --7、完毕后删除映射 exec master..xp_cmdshell 'net use Y: /delete'--8、关闭xp_cmdshell USE 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', 0 RECONFIGURE WITH OVERRIDE
posted on 2017-04-26 13:19 gavanwanggw 阅读(269) 评论(0) 编辑 收藏 举报