Tonight, 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 transaction log backup on all user databases and save the results to a directory of my choosing.
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 “log-user-backup” under the “c:\ssis-depot\” directory. Upon having a blank package, I am cheat by re-using the “full-user-backup” package as a starting point.
Go to the solution explorer and right clicking the SSIS Packages object. There is an option to add existing package. Do not forget to make this package the starting object by right clicking the imported package. Remove the default blank package that was part of the solution. Make sure the resulting package has the correct name by right clicking and renaming the file.
Wow, we just saved ourselves a ton of work!
I am going to list each of the objects in the package below. Some objects will need minor changes.
cmMySqlServer – The database server connection information will remain the same.
cmMySmtpServer – The mail server connection information will remain the same.
tskLogUserBackup – Switch the backup type to transaction log for this renamed object.
tskEmailSuccess – Change the mail subject to reflect a transaction log backup.
tskEmailFailure – Change the mail subject to reflect a transaction log backup.
The screen shot below shows the backup database task edit window. The most important modification to be made is changing the backup type.
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 (“log-user-backup.trc”) along with the SSIS package (“log-user-backup.dtsx”) are included as a download bundle at the end of the article.
Basically, the TSQL commands can be broken down into three parts. Part one is to create the sub-directories for each database if they do not exist. Part two is to perform a full backup on each database. Part three is to verify the backup that was just taken.
I cleaned up the code for the [MATH] database 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 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- -- TASK #1 -- -- Create the sub-directory if it does not exist EXECUTE master.dbo.xp_create_subdir N'C:\mssql\backup\MATH' -- -- TASK #2 -- -- Backup the database with the choosen options BACKUP LOG [MATH] TO DISK = N'C:\mssql\backup\MATH\MATH_backup_2012_12_01_084457_9190581.trn' WITH NOFORMAT, NOINIT, NAME = N'MATH_backup_2012_12_01_084457_9190581', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 -- -- TASK #3 -- -- Find the recent backup set declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'MATH' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MATH' ) -- Error out if we do not find a backup if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MATH'' not found.', 16, 1) end -- Verify the backup RESTORE VERIFYONLY FROM DISK = N'C:\mssql\backup\MATH\MATH_backup_2012_12_01_084457_9190581.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND |
In summary, the Backup Database Task can be used to create a package that can be schedule to perform automated transaction LOG backups. This design pattern includes alerting for both job success and job failure. A best practice for backups is to save the backup file to another server and swipe to either tape, disk, or the cloud.
While this package serves one purpose, how do we have it be re-usable? We can make both the SQL Server connection information, SMTP Server connection information, the BACKUP directory path, the MAIL subject text as project parameters. I will leave this exercise for you to complete.
FULL and LOG backup jobs will be sufficient for most small to medium size databases. However, for larger databases we will only want to perform a FULL backup on a weekend since it takes a real long time to run. We will want to execute DIFFERENTIAL backups each night. In my next article, we will create a package to solve this business problem.
I hope you find this package a starting point for automating your log backups.
