BACKUP4: Automatic database backup, automatic deletion of backup files

One: Handwritten tsql script

1, automatic backup

Weekly full Backup of database, set Schedule Interval to weekly

 UseMasterGo Declare @FileName nvarchar( the)Set @FileName =N'D:\SQLBackupFolder\TestDB_FullBackup_'+CONVERT(nvarchar(Max),getdate(), the)+N'. bak'BACKUP DATABASE [TESTDB]  to  DISK = @FileName  withCompression, INIT, SKIP, FormatGO

Differential Backup of database once a day, set schedule Interval to daily

 UseMasterGo Declare @FileName nvarchar( the)Set @FileName =N'D:\SQLBackupFolder\TestDB_DiffBackup_'+CONVERT(nvarchar(Max),getdate(), the)+N'. bak'BACKUP DATABASE [TESTDB]  to  DISK = @FileName  withdifferential, Compression, INIT, SKIP, FormatGO

2. Automatically delete backup files

Database backups are retained for 4 weeks, 28 days, deleted from database backups earlier than 28 days ago, executed once a week, and set schedule interval to weekly

DECLARE @ExpireDate  DATETIMESET @ExpireDate=DateAdd( Day,- -,GETDATE());DECLARE @SearchFolder nvarchar( -)SET @SearchFolder=N'D:\SQLBackupFolder';EXECUTEMaster.dbo.xp_delete_file0,@SearchFolderN'Bak',@ExpireDate,1;

Xp_delete_file can delete the standard bak file (RESTORE headeronly can return the data) and cannot delete the normal file.

Ii. use of maintenance plans (Maintenance plan)

1. Use BACKUP database Task to perform full backup and differential backup of the databases

• Backup Type: Select Full to complete the backup and select differential for differential backup.
• Database (s): Select the databases to be backed up
• Backup Component: Select Database to back up the databases
• Tick "Create a sub-directory for each database", set the backup devices stored Floder in the folder, and specify the extension of backup file in the backup file extension
• Select Compress Backup in the Set backup compression to compress the backup.

2. Use maintenance Cleanup Task to delete the backup file

Select backup files in the delete files of the following type

On the Search folder and delete files based on an extension Settings folder (the backup files stored folder), File extension (the extension of backup files), tick "Include first-level Subfolders",

File Age: Set the creation time of the deleted backup files

For example, the task will be deleted under D:\TestBackupFolder\TestDB, and the creation time is earlier than 4 weeks before Xxxxx.bak.

Third, using SSIS task to implement, maintenance Plan is actually implemented by SSIS task

Under Other Tasks catalog, there is a backup Database task and Maintenance Cleanup task, which is set to the same as Maintenace plan.

BACKUP4: Automatic database backup, automatic deletion of backup files