工作中需要创建SQL Job对数据库进行定期备份,现把脚本记录如下。
1. 完整备份:
-- FULL declare @filename varchar(1024), @file_dev varchar(300) declare @path varchar(1024) set @path = N'F:\Backup\Plan2\'; declare @extension_name varchar(16) set @extension_name = N'bak'; set @filename = convert(varchar(1024), getdate(), 120) set @filename = replace(@filename, ':', '') set @filename = replace(@filename, '-', '') set @filename = replace(@filename, ' ', '') set @filename = @filename + '_' + convert (varchar(3), datepart(ms, getdate())) + N'.' + @extension_name -- start backup, COMPRESSION is the parameter set @file_dev = @path + 'SmartDev_Full_' + @filename backup database [SmartDev] to disk = @file_dev with noformat, init, name = N'SmartDev-Database full backup', COMPRESSION -- delete the old backup file 1 days ago declare @olddate datetime select @olddate=getdate()-1 -- execute delete select @path select @extension_name select @olddate execute master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1 go
2. 差异备份:
-- Differential declare @filename varchar(1024), @file_dev varchar(300) declare @path varchar(1024) set @path = N'F:\Backup\Plan2\'; declare @extension_name varchar(16) set @extension_name = N'bak'; set @filename = convert(varchar(1024), getdate(), 120) set @filename = replace(@filename, ':', '') set @filename = replace(@filename, '-', '') set @filename = replace(@filename, ' ', '') set @filename = @filename + '_' + convert (varchar(3), datepart(ms, getdate())) + N'.' + @extension_name -- start backup, COMPRESSION is the parameter set @file_dev = @path + 'SmartDev_Differential_' + @filename backup database [SmartDev] to disk = @file_dev with differential, noformat, init, name = N'SmartDev-Database Differential backup' go
以上两段可以分别创建两个SQL Job,比如完整备份的作业可以定在每周天凌晨运行,执行成功后删掉之前的备份文件(代码中有删除的语句),差异备份的作业定在周一到周六每天凌晨运行。