工作中需要创建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，比如完整备份的作业可以定在每周天凌晨运行，执行成功后删掉之前的备份文件（代码中有删除的语句），差异备份的作业定在周一到周六每天凌晨运行