Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that executes a data retention policy in regards to backup files and [msdb] history records.
One of the most important job functions of a DBA is to have a recent backup chain in which a point in time restore can be performed. A backup chain consists of the most recent FULL backup, differential (DIFF) backup, and transaction LOG backup.
I am going to start up Visual Studio and create a project named “cleanup-backup-info” under the “c:\ssis-depot\” directory. Upon having a blank package, we will be adding and configuring several objects. I am going to list each of the objects we need to solve this business solution.
cmMySqlServer – The local database server connection information.
cmMySmtpServer – The local mail server connection information.
tskRemoveOldBakFiles – A maintenance cleanup task for FULL & DIFF backups.
tskRemoveOldTrnFiles – A maintenance cleanup task for LOG backups.
tskCleanupMsdbHistory – A history cleanup task for [msdb] database.
tskEmailSuccess – Mail notice is sent when the package completes successfully.
tskEmailFailure1 – Mail notice is sent when package fails to remove old “bak” files.
tskEmailFailure2 – Mail notice is sent when package fails to remove old “trn” files.
tskEmailFailure3 – Mail notice is sent when package fails to clean up [msdb] history.
The key items to configure the Maintenance Cleanup task for removing old “bak” files are the location of the root directory, whether to search sub-directories, the file extension to look for, and the number of weeks to retain files.
Again, we need to configure the Maintenance Cleanup task to apply a retention policy for the “trn” files.
The History Cleanup task is even easier to configure. Select the SQL Server connection, all historical data items you want to cull, and the number of weeks to retain entries.
Make sure the package looks like mine with the precedence constraints correctly configured. Now, lets run the package to completion.
I just noticed a success email that was sent to my email account and it appear in MS Outlook application.
I wonder what the SSIS package is actually doing to perform the backup? To discover what T-SQL is being sent to the server, we can open a client/server side trace using SQL Server Profiler. I am going to skip over the mechanics of starting a profiler trace and running the SSIS package in debug mode.
The whole trace file (“cleanup-backup-info.trc”) along with the SSIS package (“cleanup-backup-info.dtsx”) are included as a download bundle at the end of the article.
Basically, the TSQL commands can be broken down into three parts: remove old backup files, remove old transaction files, and cleanup [msdb] history.
I cleaned up the code that was sent by the SSIS package to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- -- TASK #1 -- EXEC master.dbo.xp_delete_file 0,N'C:\mssql\backup',N'bak',N'2012-11-03T09:15:56',1 -- -- TASK #2 -- EXEC master.dbo.xp_delete_file 0,N'C:\mssql\backup',N'trn',N'2012-11-03T09:15:56',1 -- -- TASK #3 -- DECLARE @dt datetime select @dt = cast(N'2012-11-03T09:15:56' as datetime) EXEC msdb.dbo.sp_delete_backuphistory @dt EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2012-11-03T09:15:56' EXEC msdb..sp_maintplan_delete_log null,null,'2012-11-03T09:15:56' |
In summary, the Maintenance Cleanup task can be used to apply a data retention policy to the backup files that are physically on disk. The History Cleanup task can be used to apply a record retention policy to backup/restore jobs, job history, and maintenance plan history inside the [msdb] database.
Like any ETL package, this one can be made more re-usable by parameterizing the arguments needed by each control flow object. In my next article, we will exploring how to use the Check Database Integrity task to validate our databases.
