backup4：数据库自动备份，自动删除备份文件-白红宇

数据文件的备份，可以通过Job，设置Schedule自动管理，而手工编写TSQL脚本，实现起来十分灵活，并且能够分布式备份。在数据量不大时，你使用backup database，能够把数据库备份到指定的硬盘中，但是，当数据库增长到很大的size时，数据库的备份文件，即使压缩，也会达到很大，甚至超过单个磁盘的存储空间，这样，就必需考虑分布式备份，把数据库的备份分为N份，每一份都存储在不同的硬盘上。

一：手写TSQL 脚本

1，自动备份

每周进行一次Database 的 Full Backup，设置 Schedule Interval 为Weekly

use mastergo declare @FileName nvarchar(256)set @FileName = N'D:\SQLBackupFolder\TestDB_FullBackup_'+CONVERT(nvarchar(max),getdate(),112)+N'.bak'BACKUP DATABASE [TESTDB] TO  DISK = @FileName WITH    Compression    ,INIT    ,SKIP    ,FormatGO

每天进行一次Database的 Differential Backup，设置Schedule Interval 为Daily

use mastergo declare @FileName nvarchar(256)set @FileName = N'D:\SQLBackupFolder\TestDB_DiffBackup_'+CONVERT(nvarchar(max),getdate(),112)+N'.bak'BACKUP DATABASE [TESTDB] TO  DISK = @FileName WITH    Differential    ,Compression    ,INIT    ,SKIP    ,FormatGO

2，自动删除备份文件

数据库的备份保留4周，28天，将早于28天前的数据库备份删除，每周执行一次，设置Schedule Interval为Weekly

DECLARE @ExpireDate  DATETIMESET @ExpireDate=dateadd(day,-28,GETDATE());DECLARE @SearchFolder nvarchar(100)SET @SearchFolder=N'D:\SQLBackupFolder\';EXECUTE master.dbo.xp_delete_file 0,@SearchFolder,N'bak',@ExpireDate,1;

xp_delete_file 能够删除标准的bak文件，（RESTORE HEADERONLY可以返回数据的），不能删除普通文件。

存储过程 xp_delete_file 有5个参数，按照顺序，参数代表的含义是:

File Type = 0 for backup files or 1 for report files.

Folder Path = The folder to delete files. The path must end with a backslash "\".

File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.

Date = The cutoff date for what files need to be deleted.

Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

3，进行备份文件分布式存储的脚本

--bull backupdeclare @BackupFileName nvarchar(1024)set @BackupFileName=N'DB_Name_FullBackupAt_'+FORMAT(getdate(),N'yyyy_MM_dd_HH_mm_ss')declare @Disk1 nvarchar(1024)declare @Disk2 nvarchar(1024)declare @Disk3 nvarchar(1024)select @Disk1=N'\\host1\SQLServerBackupFolder\' +@BackupFileName+N'1_.bak'select @Disk2=N'\\host2\SQLServerBackupFolder\' +@BackupFileName+N'2_.bak'select @Disk3=N'\\host3\SQLServerBackupFolder\' +@BackupFileName+N'3_.bak'backup database [db_name]    TO disk=@Disk1,    disk=@Disk2,    disk=@Disk3withcompression,format,init,skip,stats=5;go--differential backupdeclare @BackupFileName nvarchar(1024)set @BackupFileName=N'DB_Name_DiffBackupAt_'+FORMAT(getdate(),N'yyyy_MM_dd_HH_mm_ss')declare @Disk1 nvarchar(1024)declare @Disk2 nvarchar(1024)declare @Disk3 nvarchar(1024)select @Disk1=N'\\host1\SQLServerBackupFolder\' +@BackupFileName +N'1_.bak'select @Disk2=N'\\host2\SQLServerBackupFolder\' +@BackupFileName +N'2_.bak'select @Disk3=N'\\host3\SQLServerBackupFolder\' +@BackupFileName +N'3_.bak'backup database [db_name]    TO disk=@Disk1,    disk=@Disk2,    disk=@Disk3withdifferential,compression,format,init,skip,stats=5;--cleanup backup filesdeclare @ExpireDate  DATETIMEset @ExpireDate=dateadd(day,-22,getdate());declare @Folder1 nvarchar(1024)declare @Folder2 nvarchar(1024)declare @Folder3 nvarchar(1024)select @Folder1=N'\\host1\SQLServerBackupFolder\';select @Folder2=N'\\host2\SQLServerBackupFolder\';select @Folder3=N'\\host3\SQLServerBackupFolder\';execute master.dbo.xp_delete_file 0,@Folder1,N'bak',@ExpireDate,1;execute master.dbo.xp_delete_file 0,@Folder2,N'bak',@ExpireDate,1;execute master.dbo.xp_delete_file 0,@Folder3,N'bak',@ExpireDate,1;go

View Code

二，使用维护计划（Maintenance Plan）

1，使用Backup Database Task 对数据库进行Full Backup 和 Differential Backup

Backup Type：选择Full 进行完整备份，选择Differential进行差异备份。

Database(s)：选择进行备份的数据库

Backup Component：选择Database，对数据库进行备份

勾选“Create a sub-directory for each database”，在Folder中设置backup devices存储的Floder，在Backup file extension中指定backup file的扩展名

在 Set Backup Compression中选择Compress Backup，对backup进行压缩。

2，使用Maintenance Cleanup Task，将备份文件删除

在Delete files of the following type 中选择Backup files

在Search folder and delete files based on an extension 设置Folder（Backup files存储的folder），File Extension（backup files的扩展名），勾选 “Include first-level subfolders"，

File Age：设置被删除的backup files的创建时间

例如，该Task 会将位于 D:\TestBackupFolder\TestDB 下，创建时间早于4周前的 xxxxx.bak 删除。

三，使用SSIS Task来实现，Maintenance Plan 实际上也是通过SSIS Task 来实现的

在Other Tasks Catalog下，存在Backup Database Task 和 Maintenance Cleanup Task，设置和Maintenace Plan相同。

作者：

出处：

本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。

分类:

标签: ,

本文转自悦光阴博客园博客，原文链接：http://www.cnblogs.com/ljhdo/p/5668198.html，如需转载请自行联系原作者