SQLSERVER数据库远程备份脚本

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[deleteFile] 
@file  nvarchar(100)
as
begin 

begin try

DECLARE @hr1 int;
DECLARE @ole_FileSystem1    int;

EXEC @hr1 = sp_OACreate 'Scripting.FileSystemObject',@ole_FileSystem1 OUT
EXEC @hr1 = sp_OAMethod @ole_FileSystem1, 'DeleteFile',NULL, @file
EXEC @hr1 = sp_OADestroy @ole_FileSystem1

end try
begin catch
select error_number() as error_number ,
error_message() as error_message,
error_state() as error_state,
error_severity() as error_severity
end catch

end;

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[bakDeleteJob_wz]
@fold nvarchar(50),    --备份文件夹位置
@dbname nvarchar(50),  --备份的数据库名称
@delBeforeDay int      --需要多少天前的备份删除(负数)
as
begin 

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'xp_cmdshell', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'Ole Automation Procedures', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE 

begin try
--删除映射
exec master..xp_cmdshell 'net use z: /delete';
--做一个与客户端的映射 
exec master..xp_cmdshell 
'net use z: \\10.1.1.100\d$ "1111111" /user:10.1.1.100\administrator';

declare @wzbkfile nvarchar(60),
        @wzdelfile nvarchar(60),

        @sql nvarchar(200),
        @i int 
 
SELECT @wzbkfile=@fold+@dbname+convert(nvarchar(12),DATEADD(day, 0, getdate()),112)+'wz.bak'
SELECT @wzdelfile=@fold+@dbname+convert(nvarchar(16),DATEADD(day, -2, getdate()),112)+'wz.bak'

set @sql ='BACKUP DATABASE '+@dbname+' TO DISK ='''+@wzbkfile+'''';

exec (@sql);

set @i=1 
while(@i<8)
begin 

SELECT @wzdelfile=@fold+@dbname+convert(nvarchar(16),DATEADD(day, @delBeforeDay, getdate()),112)+'wz.bak';
EXEC dbo.deleteFile  @wzdelfile;

set @i=@i+1;
set @delBeforeDay=@delBeforeDay-1;

end

end try
begin catch
select error_number() as error_number ,
error_message() as error_message,
error_state() as error_state,
error_severity() as error_severity
end catch

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'xp_cmdshell', 0 
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'Ole Automation Procedures', 0
RECONFIGURE WITH OVERRIDE 
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE 

end;