Backup Database Task – Part 1

SQL Server Data Tools (SSDT) is the newest release of the Visual Studio (VS) development environment for the SQL Server projects. I suggest that you use the 2010 VS Shell since the 2012 VS has some GUI color issues that are reminders of terminal screens in the early 1980’s. I hope they listen to developer feedback and fix this issue in a future release.

I will be doing a series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks.

One of the most important job function of being 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.

Today, I will be designing a SSIS package that executes a full backup on all user databases and save the results to a directory of my choosing.

I am going to start up Visual Studio and create a project named “full-user-backup” under the “c:\ssis-depot\” directory. Upon having a blank package, I am going to add the backup database task to the control flow canvas. Double clicking the object with bring up the advance editor.

First, we need to define a new connection to the SQL Server 2012 database server. I decided to choose the native client for SQL Server, define the server as (local), use windows authentication,
and not select a default database. This connection information was saved as “cmMySqlServer” under the connection managers section.

Second, we need to choose a sequence of options from the backup database task that meets our requirements. The backup database task is named “tskFullUserBackup”.

This task has too many features to go over in just this article. We want to have a full backup of all the user databases, create a backup sub-directory for each database under the “c:\mssql\backup” directory and name each backup with a “bak” extension. Also, we want to have each backup file compressed so that it takes the least amount of space on disk.

Last but not least, what good is a bad backup? We want the backup task to verify the contents of the backup before we are done.

Now that we have the backup task configured, we could run the package right now!

However, how do we know that the package completed successfully? If we use SQL Server Agent to schedule the job, we could use built in notifications. However, I want to make the package more generic so that it can be scheduled with WINDOWS scheduler, CA job scheduler, or anything.

To meet this requirement, we need to add two instances of the send mail task to the package. One task named “tskEmailSuccess” will report job success and the other named “tskEmailFailure” will report job failure.

Again, we need to define a new connection to the SMTP server named “cnMySmtpServer” by supplying the name of the server and how to authenticate. The server that I am working with is internal to the organization and allows anonymous connections. Please see the image below that shows the configuration.

To complete the send mail tasks, we need to add the following: our new SMTP connection, both the TO and FROM addresses, a SUBJ and optional BODY text, and a PRIORITY for the mail delivery.

Once we configure both the success and failure send mail instances, we need to draw the correct precedence constraints to complete the project.

A first look at the backup directory shows it is brand new with no sub-directories or backup files.

Now, lets run the package to completion.

A second look at the backup directory shows sub-directories for each database and a backup file for the [MATH] database.

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.

To kick off the trace, select a logical trace name, a physical storage location of the trace file and the T-SQL predefined template. After starting the trace, we are all set to execute (debug) the package.

Lets take an in-depth look at the TSQL that was sent to the server. I am to going to include just one screen shot of the trace output. It is quite a long and detail output. The whole trace file (TRC) along with the SSIS package (DTSX) will be included at the end of the article.

Basically, the 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.

In summary, the Backup Database Task can be used to create a package that can be schedule to perform automated FULL 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. At work, we are using the Simpana Commvault backup/restore product to do just that with Microsoft Azure as a cloud storage.

While this package serves one purpose, how do we have it be re-usable? We can make both the SQL server and SMTP connection information as a project parameter. This can be changed at the run time by job calling the package. Other items that you might want to pararmeterize are the BACKUP directory and MAIL content. Just make sure that the SQL Server Service account has access to the network share that will be your backup directory. I will leave this exercise for you to complete.

Please make sure that you understand the overall design of this package. I will be creating blogs on DIFFERENTIAL and LOG backups, in the near future, without going into such detail. Our time will be spent on looking at the TSQL that is sent to the server. I hope you find this package a starting point for automating your backups.

full user backup bundle