SQL Server中網路備份一例

複製代碼 代碼如下: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下