Click here to Skip to main content
15,861,125 members
Articles / Database Development / SQL Server
Article

Windows DTS Package for SQL Server 2005 Database Backup

Rate me:
Please Sign up or sign in to vote.
4.86/5 (27 votes)
4 Jul 2007CPOL3 min read 43.8K   879   30   5
Automated Windows DTS Package for SQL Server 2005 Database Backup
Screenshot - ConfigurationSettings.gif

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:

C#
//
// 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.

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

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

C#
//
//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:

C#
//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

  • 4th 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer Proteans Software Solutions Pvt Ltd.
India India
Shiras AbdulRahman Currently working with Proteans Software Solutions Bangalore.

Proteans a CAMO group company is an outsourcing company focusing on software product development and business application development on Microsoft Technology Platform. "Committed to consistently deliver high-quality software products and services through continual improvement of our knowledge and practices focused on increased customer satisfaction.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:58
professionalKanasz Robert24-Sep-12 5:58 
GeneralTry EZmanage SQL Pro for VDI backup 5 time faster than native tool Pin
itayl27-May-09 3:12
itayl27-May-09 3:12 
QuestionThere is a question about SQLDMO to consult you Pin
HoverLee2-Sep-08 16:06
HoverLee2-Sep-08 16:06 
Questionhow do i use the same setup for more than one database Pin
assdfggh29-Aug-08 2:37
assdfggh29-Aug-08 2:37 
GeneralThanks for a great timely help Pin
mno_12429-Aug-08 2:34
mno_12429-Aug-08 2:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.