SQL自动异地备份、删除、还原数据库(利用代理作业)

--备份脚本

/*xp_cmdshell 设置改为开启*/
EXEC sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE

EXEC sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE

EXEC sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
GO

RECONFIGURE
GO


declare @SQL varchar(4000)
declare @backupfile varchar(2000)
declare @retaindays int
declare @deletefiles varchar(2000)
declare @cmd varchar(2000)
declare @User varchar(2000)
declare @Pwd varchar(2000)
declare @IPPart varchar(2000)
declare @IP varchar(2000)
declare @Store1 varchar(2000)


declare @dir1 varchar(2000)
declare @bakfile1 varchar(2000)
declare @str1 varchar(4000)


SET @Store1 ='test'  --【※】需要备份的数据库名 ----数据库名建议不要用“-”符号，拼SQL语法报错


SET @IP  ='bk'  --【※】备份文件所存放的异地服务器IP或主机名
SET @User =@IP + '\administrator'  --【※】(异地服务器)用户名 -----要管理员权限【格式为IP\用户名】
SET @Pwd ='888'  --【※】(异地服务器)密码 -----密码不要有符号
SET @IPPart ='dbbackup' --【※】相对路径(异地服务器的共享目录，(注意：此目录要有@User用户的访问读写权限)
SET @retaindays=2   --【※】要保留备份的天数  


--【】创建映射驱动器（映射硬盘）
DECLARE @CreateMap NVARCHAR(254)

SET @CreateMap = 'net use W: \\' + @IP + '\G$\dbbackup "' + @Pwd + '" /user:'+ @User

EXEC master..xp_cmdshell @CreateMap

--【】开始备份
SET @backupfile='w:\' + @Store1 + replace(substring(convert(varchar(20),Getdate(),120),1,10),'-','') + '.Bak'
SET @SQL='Backup Database ' + @Store1 + ' To Disk='''+@backupfile+''' With NoFORMAT, NOINIT, Retaindays='+convert(varchar(10),@retaindays)
EXEC (@SQL)



--自动删除备份文件  
set @dir1='del \\bk\G$\dbbackup\'  
set @bakfile1=left(replace(substring(convert(varchar(20),Getdate()- @retaindays,120),1,10),'-',''),10)+ '*.Bak'
set @str1=@dir1 + @Store1 + @bakfile1   
exec master..xp_cmdshell @str1



--删除映射驱动器
EXEC master..xp_cmdshell'net use w: /delete'

--关闭允许执行xp_cmdshell
EXEC sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE

EXEC sp_configure N'xp_cmdshell', N'0'
RECONFIGURE WITH OVERRIDE

EXEC sp_configure N'show advanced options', N'0'
RECONFIGURE WITH OVERRIDE
GO
RECONFIGURE WITH OVERRIDE
GO




--恢复脚本



 --【※】  test
Alter database BackBayPublish Set offline with ROLLBACK IMMEDIATE--脱机
GO

Alter database BackBayPublish Set online with ROLLBACK IMMEDIATE--联机
GO

DECLARE @SQL NVARCHAR(4000)
DECLARE @Store1 varchar(2000)

SET @Store1 ='test'  --【※】备份的数据库名
SET @SQL  = 'G:\dbbackup\'+@Store1+Replace(Substring(Convert(varchar(20),Getdate(),120),1,10),'-','') + N'.Bak'

Restore Database BackBayPublish From Disk= @SQL WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO