完整备份和差异备份数据库的SQL脚本

2017-12-14 20:01:19来源:cnblogs.com作者:石头ai蓝天人点击

分享

工作中需要创建SQL Job对数据库进行定期备份,现把脚本记录如下。

1. 完整备份:

-- FULLdeclare @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 parameterset @file_dev = @path + 'SmartDev_Full_' + @filenamebackup database [SmartDev] to disk = @file_dev with noformat, init,  name = N'SmartDev-Database full backup', COMPRESSION-- delete the old backup file 1 days agodeclare @olddate datetimeselect @olddate=getdate()-1-- execute deleteselect @pathselect @extension_nameselect @olddateexecute master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1go

2. 差异备份:

-- Differentialdeclare @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 parameterset @file_dev = @path + 'SmartDev_Differential_' + @filenamebackup database [SmartDev] to disk = @file_dev with differential, noformat, init,  name = N'SmartDev-Database Differential backup'go

以上两段可以分别创建两个SQL Job,比如完整备份的作业可以定在每周天凌晨运行,执行成功后删掉之前的备份文件(代码中有删除的语句),差异备份的作业定在周一到周六每天凌晨运行。

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台