当前位置: 数据库>sqlserver
SQL Server中网络备份一例
来源: 互联网 发布时间:2014-09-05
本文导语: 代码如下:use master declare @dir varchar(256),@dir_db varchar(256), @verb varchar(256),@cmd varchar(256), @backup_name varchar(256),@dynamic_name varchar(10), @disk_name varchar(256) --建立网络连接 exec xp_cmdshell '...
代码如下:
use master
declare @dir varchar(256),@dir_db varchar(256),
@verb varchar(256),@cmd varchar(256),
@backup_name varchar(256),@dynamic_name varchar(10),
@disk_name varchar(256)
--建立网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use K: \Lantbackup backup /user:Lantbackup '
--设定名字
set @dynamic_name=convert(varchar(10),getdate(),120)
set @dir='K:'+@dynamic_name
set @dir_db=@dir+'ibm'
set @verb='mkdir '
--建立目录
--set @cmd=@verb+@dir
--exec xp_cmdshell @cmd
set @cmd=@verb+@dir_db
exec xp_cmdshell @cmd
--wap ec_wap
set @backup_name= 'lifengOA_'+@dynamic_name
set @disk_name=@dir_db+''+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
BACKUP DATABASE ec_wap TO @backup_name
exec sp_dropdevice @backup_name
--文档 ec_tech
set @backup_name= '文档_'+@dynamic_name
set @disk_name=@dir_db+''+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE ec_tech TO @backup_name
exec sp_dropdevice @backup_name
--博吧 ec_blog
set @backup_name= '博吧_'+@dynamic_name
set @disk_name=@dir_db+''+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE ec_blog TO @backup_name
exec sp_dropdevice @backup_name
exec xp_cmdshell 'net use K: /delete'
declare @dir varchar(256),@dir_db varchar(256),
@verb varchar(256),@cmd varchar(256),
@backup_name varchar(256),@dynamic_name varchar(10),
@disk_name varchar(256)
--建立网络连接
exec xp_cmdshell 'net use K: /delete'
exec xp_cmdshell 'net use K: \Lantbackup backup /user:Lantbackup '
--设定名字
set @dynamic_name=convert(varchar(10),getdate(),120)
set @dir='K:'+@dynamic_name
set @dir_db=@dir+'ibm'
set @verb='mkdir '
--建立目录
--set @cmd=@verb+@dir
--exec xp_cmdshell @cmd
set @cmd=@verb+@dir_db
exec xp_cmdshell @cmd
--wap ec_wap
set @backup_name= 'lifengOA_'+@dynamic_name
set @disk_name=@dir_db+''+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
BACKUP DATABASE ec_wap TO @backup_name
exec sp_dropdevice @backup_name
--文档 ec_tech
set @backup_name= '文档_'+@dynamic_name
set @disk_name=@dir_db+''+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE ec_tech TO @backup_name
exec sp_dropdevice @backup_name
--博吧 ec_blog
set @backup_name= '博吧_'+@dynamic_name
set @disk_name=@dir_db+''+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE ec_blog TO @backup_name
exec sp_dropdevice @backup_name
exec xp_cmdshell 'net use K: /delete'
然后做一个Task,那么,SQL Server就会备份到指定的网络地址\Lantbakcup下