SqlServer备份脚本

/*Sqlserver数据库数据文件备份*/
DECLARE @v VARCHAR(30) = CAST(REPLACE(( CONVERT(VARCHAR, GETDATE(), 112)
                                       + CONVERT(VARCHAR, GETDATE(), 114) ),
                                     ':', '') AS BIGINT)
DECLARE @v2 VARCHAR(200)='D:\prs\Microsoft SQL Server\MSSQL11.ZSTEC\MSSQL\Backup\YUNGOU_backup_'+@v+'.bak'
BACKUP DATABASE [YUNGOU] TO  DISK = @v2 WITH NOFORMAT, NOINIT,  NAME = N'YUNGOU-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'YUNGOU' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'YUNGOU' )
if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“YUNGOU”的备份信息。', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = @v2 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


/*Sqlserver数据库日志文件备份*/
DECLARE @v VARCHAR(30) = CAST(REPLACE(( CONVERT(VARCHAR, GETDATE(), 112)
                                       + CONVERT(VARCHAR, GETDATE(), 114) ),
                                     ':', '') AS BIGINT)
DECLARE @v2 VARCHAR(200)='D:\prs\Microsoft SQL Server\MSSQL11.ZSTEC\MSSQL\Backup\YUNGOU_LogBackup_'+@v+'.bak'
BACKUP LOG [YUNGOU] TO  DISK = @v2 WITH NOFORMAT, NOINIT,  NAME = N'YUNGOU-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'YUNGOU' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'YUNGOU' )
if @backupSetId is null begin raiserror(N'验证失败。找不到数据库“YUNGOU”的备份信息。', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = @v2 WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


/*删除过期备份文件，冗余7天*/
DECLARE @v VARCHAR(30) = CONVERT(varchar, getdate()-7, 120 )
EXECUTE master.dbo.xp_delete_file 0,N'D:\prs\Microsoft SQL Server\MSSQL11.ZSTEC\MSSQL\Backup',N'bak',@v