Backup SQL Server with Relative File Path and Progress

• Download demo - 9.98 KB
• Download source - 14.35 KB

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:

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:

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.

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.

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