Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server / SQL Server 2008

Backup SQL Server with Relative File Path and Progress

Rate me:
Please Sign up or sign in to vote.
4.92/5 (6 votes)
11 Jun 2010CPOL3 min read 43.7K   2.8K   43   15
This class will allow you to asynchronously backup a database while also getting progress, message, and error notifications.

Introduction

The class provided by this article will allow you to perform SQL Backup operations without blocking (aka, asynchronously) while receiving progress, message, error, and completion notifications. It will also allow you to be unaware of the environment of the SQL Server by defaulting to a relative backup filename. Backup files will be placed in the default backup folder of the SQL server.

Using the Code

Using the class to perform a backup is as easy as two lines:

C#
SQLBackup sb = new SQLBackup("Data Source=myServerAddress;
		User Id=myUsername;Password=myPassword", "databaseName");
sb.BeginBackup();

The above code will start an asynchronous process that will back the database up. You can check for completion by using the Running property. There are also three events that your thread can subscribe to:

C#
sb.BackupProgress += delegate(object sender, SQLBackup.BackupProgressEventArgs e) { }
sb.BackupFinished += delegate(object sender, EventArgs e) { }
sb.BackupMessage += delegate(object sender, SQLBackup.BackupMessageEventArgs e) { }
  • The BackupProgress event will be fired every half second the backup is running and will pass a Percentage through the BackupProgressEventArgs variable.
  • The BackupFinished event will be fired once the backup has been completed. It will also fire when the backup has encountered an error.
  • The BackupMessage event will be fired when the SqlConnection receives an information message, or when an error occurs. If an error occurred, Error will be true. InnerException may contain an exception or null.

There are several properties which allow you to control the SQLBackup class. These properties are read-only while the backup is running.

C#
sb.ConnectionString = "Data Source=myServerAddress;
			User Id=myUsername;Password=myPassword";
sb.Database = "databaseName";
sb.BackupFileName = "TestBackupFile.bak";
sb.Compression = false;
sb.CopyOnly = true;
Console.WriteLine(sb.Running.ToString());
  • The BackupFileName property will be used in the backup command. This can be a path on the database server, a UNC path (if the proper permissions are set up on the SQL server and destination), or just a filename. If the property is set to null, a filename will be automatically generated from the database name and the date/time of the backup.
  • The Compression property will attempt to use SQL Backup Compression option. This option is only valid in 2008 and higher, and cannot be used on Express editions.
  • The ConnectionString property will allow you to change the connection string of the SQLBackup instance. If this connection string contains a database, it will change the Database property and then store the master database in the connection string.
  • The CopyOnly property will attempt to use SQL Copy-Only Backup option. This option will take a backup of the database without interrupting the previous backups and will not commit transaction logs into the database (like a full backup without copy-only will).
  • The Database property will change the database to be backed up.
  • The Running property is true if the thread is running, false if it is not.

Finally, there are two methods in the SQLBackup class. The first will allow you to block the current thread until the backup is completed. The second will allow you to forcibly kill the backup process.

C#
sb.BeginBackup();
// sb.Running == true
Thread.Sleep(2000); // Wait 2 seconds
sb.EndBackup(); // End the backup forcibly.
// The backup may not exit immediately, you can chose to block 
// the thread until it is completed.
sb.BlockUntilFinished();
// sb.Running == false

Points of Interest

I was surprised to find that no one had a generally good way to determine the progress of a SQL Backup. I decided to write this class for another project I am working on that requires backing up a SQL database before performing a schema-compare-update. I searched around for a bit until I stumbled upon a reply to this StackOverflow thread by Remus Rusanu. This post helped me figure out how I wanted to build the class.

The source code was successfully compiled on Windows 7 Enterprise, 64-bit, with Visual Studio 2010 Premium. The project is targeted at v2.0 of the .NET Framework. Version 2.0 is a requirement of the project I am working on.

The one and only gotcha I've found is that sometimes the SQL connection does not connect properly when doing back-to-back SQL backups. If you look at the solution, you can see the OrderedTests I did which performs 4 backups back-to-back. I have worked on the code until I could successfully run 4 simultaneous backups.

History

  • 1.0.3813.32246 Final code commit for 1.0, submitted project to CodeProject
  • 1.0.3814.14372 Fixed bug in optionsString, thanks rmorgex

License

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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionFile is not made in destination Pin
Kazehaya Shen20-Dec-20 17:58
Kazehaya Shen20-Dec-20 17:58 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:00
professionalKanasz Robert24-Sep-12 6:00 
QuestionVB Code Pin
Ferri Suryadi26-Mar-12 23:12
Ferri Suryadi26-Mar-12 23:12 
AnswerRe: VB Code Pin
theit851426-Mar-12 23:31
theit851426-Mar-12 23:31 
GeneralRe: VB Code Pin
Ferri Suryadi27-Mar-12 0:12
Ferri Suryadi27-Mar-12 0:12 
GeneralMy vote of 5 Pin
markus-muc24-Aug-11 1:08
markus-muc24-Aug-11 1:08 
QuestionProgress information Pin
markus-muc24-Aug-11 1:07
markus-muc24-Aug-11 1:07 
AnswerRe: Progress information Pin
theit851424-Aug-11 2:28
theit851424-Aug-11 2:28 
GeneralRe: Progress information Pin
markus-muc24-Aug-11 3:46
markus-muc24-Aug-11 3:46 
GeneralRe: Progress information Pin
theit851424-Aug-11 13:32
theit851424-Aug-11 13:32 
SuggestionCOPY_ONLY option available in SQL 2005 too Pin
markus-muc24-Aug-11 1:03
markus-muc24-Aug-11 1:03 
GeneralError on SQL 2005 Pin
rmorgex10-Jun-10 23:19
rmorgex10-Jun-10 23:19 
GeneralRe: Error on SQL 2005 [modified] Pin
theit851411-Jun-10 2:47
theit851411-Jun-10 2:47 
QuestionBackup, and Restore ? Pin
Bizounours10-Jun-10 21:06
Bizounours10-Jun-10 21:06 
AnswerRe: Backup, and Restore ? Pin
theit851411-Jun-10 2:49
theit851411-Jun-10 2:49 
Thanks for the reply. Unfortunately, I have not planned to create a Restore class for this.

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.