/****************************************************************************************
* *
* FielName : backup.sql *
* Function : 自动备份 *
* Author : Yahong *
* Write Date : 2005-5-10 2005-5-19 *
* Version : 00 01 *
* *
*****************************************************************************************/
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: \yahong1backup backup /user:yahong1\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
--DgLifeng
set @backup_name= 'dglifeng_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
BACKUP DATABASE Dglifeng TO @backup_name
exec sp_dropdevice @backup_name
--lifeng
set @backup_name= 'lifeng_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
BACKUP DATABASE lifeng TO @backup_name
exec sp_dropdevice @backup_name
--lifengOA
set @backup_name= 'lifengOA_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name, @disk_name
BACKUP DATABASE lifengOA TO @backup_name
exec sp_dropdevice @backup_name
--mytopship
set @backup_name= 'mytopship_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE Mytopship TO @backup_name
exec sp_dropdevice @backup_name
--同舟化工 ais001
set @backup_name= '同舟化工_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE ais002 TO @backup_name
exec sp_dropdevice @backup_name
--同川化工 ais002
set @backup_name= '同川化工_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE ais002 TO @backup_name
exec sp_dropdevice @backup_name
--HK同舟公司 AIS20030219102913
set @backup_name= 'HK同舟公司_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE AIS20030219102913 TO @backup_name
exec sp_dropdevice @backup_name
--化出保税仓 AIS20030219105426
set @backup_name= '化出保税仓_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE AIS20030219105426 TO @backup_name
exec sp_dropdevice @backup_name
--东莞化工有限公司 AIS20030219114839
set @backup_name= '东莞化工有限公司_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE AIS20030219114839 TO @backup_name
exec sp_dropdevice @backup_name
--香港同舟公司 AIS20030527110242
set @backup_name= '香港同舟公司_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE AIS20030527110242 TO @backup_name
exec sp_dropdevice @backup_name
--禾邦实业有限公司 禾邦实业有限公司
set @backup_name= '禾邦实业有限公司_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE 禾邦实业有限公司 TO @backup_name
exec sp_dropdevice @backup_name
--香港同舟化工贸易有限公司 香港同舟化工贸易有限公司
set @backup_name= '香港同舟化工贸易有限公司_'+@dynamic_name
set @disk_name=@dir_db+'\'+@backup_name
EXEC sp_addumpdevice 'disk',@backup_name,@disk_name
BACKUP DATABASE 香港同舟化工贸易有限公司
