Windows DTS Package for SQL Server 2005 Database Backup

• Download source files - 2.25 MB
• Download demo project - 1.44 KB

Introduction

This is my first article related to SQL Server 2005. This product helps the user to backup database as well as the database backup logfile of a SQL Server 2005 database using a job which is created automatically by the installer. The user just needs to unzip and install DTSPackage.zip in the database prior to installation of BackUpDatabaseUsingDTS. you can find BackUpDatabaseUsingDTS MSI setup inside BackUpDatabase.zip.

Background

In my example above, I have typed in my database username "sa" my database password "xxxx" and I have selected my own SQL Server 2005 "(local)". Even though we have an option to select network databases, I would suggest that you install the component in the database Server. On selection of the server, the database name will be listed making this a successful installation. We need to install the script first (DTSPackage.zip). I executed the script in my database called MeridiumCom. After successful installation, I went into my BackUpDatabaseUsingDTS MSI installer inside BackupDatabase.zip. Remember that the user should select the same database in order to complete successfully. The user is given an option to select database backup path as well as the log file. While we install the component successfully Backup is scheduled automatically at 11:45 PM daily once. It does not have an enddate and startdate is 12/9/2006. Schedule type is recurring. If at all the user wants to schedule for another time, then he/she can go to SQL SERVER 2005 Object Explorer --> go to SQL Server Agent --> Jobs -->Select the Job and Right Click --> Properties --> Click Schedule from the left panel --> Click EDIT button in the middle panel.

The component starts the SQL SERVER AGENT if it's not started and creates a new JOB. The name of the job will be very similar to the database selected by the user.

Using the Code

Add an Installer class and add the below reference:

//
// System.configuration.Install
//

By default, the installer will be inherited for the class. For example, my component was named as DatabaseBackUpInstaller. The user needs to add [RunInstaller(true)], this is mandatory.

//
// [RunInstaller(true)]
// public partial class DatabaseBackUpInstaller : Installer
//

Let me give a detailed overview of my methods used in DatabaseBackUpInstaller:

//
//GetConnectionString --> forms the database connection string
//ExecuteStoredProcedure --> executes the DTSPackage StoredProcedure with 
//necessary information.
//public override void Install --> overrides Install 
//StartSQLServerAgent --> Starts SQL Server Agent if it is stopped
//ExecuteSQLQuery --> finds the status of SQL Server Agent
//

Let me give a detailed overview of my methods used in InstallForm:

//protected override void OnLoad --> overrides onLoad event to get 
//SQL Servers in the Network
//btnFinish_Click--> Validates the input textboxes.
//btnDBPath_Click -->  As we know MSI installers are MTA Threaded 
//ApartmentState. If we want to invoke FolderBrowserDialog, we need to go 
//for STA Threaded ApartmentState. This event gets us the database backup path. 
//The user can only select the folder. 
//filename will be provided by the application.SQLServerLocator Class 
//helps to get the network databases.

How It Works

1. The user needs to execute the StoredProcedure in any database (e.g. MeridiumCom)
2. The user needs to install the application. He/she doesn't need execute the code. The code is only for the purpose of understanding.

Points of Interest

1. I learnt know how to invoke FolderBrowserDialog from an MTA threaded application
2. The usage of SQLDMO namespace in .NET

History

• 4^th July, 2007: Initial post

As this is my first article, users need to execute DTSPackage StoredProcedure manually. My next version will completely automate with some additional features.

About Proteans Software Solutions

Proteans is an outsourcing company focusing on software product development and business application development on Microsoft Technology Platform. Proteans partners with Independent Software Vendors (ISVs), System Integrators and IT teams of businesses to develop software products. Our technology focus and significant experience in software product development - designing, building, and releasing world-class, robust and scalable software products help us to reduce time-to-market, cut costs, reduce business risk and improve overall business results for our customers. Proteans expertise in development uses Microsoft .NET technologies.