复制代码 代码如下: 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: \\lant\backup backup /user:lant\backup ‘
–设定名字
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就会备份到指定的网络地址\\lant\bakcup下