This post will show one of the methods of using Microsoft’s SQL SMO to create and restore database backups programatically.
The basis behind the code in this post came from a need we had to create an automated process that would check three log files on our server to determine if our nightly imports had completed properly. If the logs all showed success then we created a backup of our three primary databases, otherwise we had to restore from the previous day’s backup.
We had initially gone the route of doing this purely in SQL with scheduled jobs, but determined that we would have better control (and a larger base of support in our development team) if we created the utility to check the logs and work with the databases in .NET. Originally this entire process was completed using SQLDMO, however since that method is being phased out by Microsoft soon™, I have converted it over to use SMO.
I’ve included the basics for backing up and restoring a database as well as the events that can be wired up should you have a need to do so.
The assemblies you need to include in your project are located in the following locations:
SQL Server 2005
\Program Files\Microsoft SQL Server\90\SDK\Assemblies
Include the Microsoft.SqlServer.Smo.dll assembly
Include the Microsoft.SqlServer.ConnectionInfo.dll assembly
SQL Server 2008
\Program Files\Microsoft SQL Server\100\SDK\Assemblies
Include the Microsoft.SqlServer.SmoExtended.dll assembly
Include the Microsoft.SqlServer.ConnectionInfoExtended.dll assembly
You’ll want to include the following using statements at the top of your project
[cclN_csharp]
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
[/cclN_csharp]
The following code is from a sample project I made as a C# Windows Form application. I added a simple label, progress bar and two buttons (one for backup and one for restore). The actual application we use is a console app, but this method is easier for demoing and explaining usage.
[cclN_csharp]
Server dbServer = new Server(new ServerConnection(“
Backup dbBackup = new Backup();
/* The type of device you going to backup: Database, Files or Log */
dbBackup.Action = BackupActionType.Database;
/* The SQL Database name you need to backup */
dbBackup.Database = “
/* Keep in mind that the file path you use here is relative to the SQL Server, not your local file system */
dbBackup.Devices.AddDevice(@”
dbBackup.BackupSetName = “Automated Daily Backup Example”;
dbBackup.BackupSetDescription = “Sample database – Automated Daily Backup (Example)”;
/* This is an optional item that you can use to set when the backup data expires and
* should no longer be considered “restorable”
*/
//dbBackup.ExpirationDate = DateTime.Now.AddDays(7);
/* Leaving the Initialize value set to the default of false will create
* a new backup item as the last backup set.
* If you change the Initialize value to true then this backup set will
* become the first backup set and will overwrite other backups that have
* the same BackupSetName
*/
dbBackup.Initialize = true;
/* Leaving the Incremental value set to the default of false will create
* a full backup.
* If you change the Incremental value to true it will only perform a
* delta backup since the last full backup that was performed
*/
//dbBackup.Incremental = false;
/* There are several events that you can wire up to the Backup object
* These will allow you to keep track of:
* The current progress of the backup
* When the current backup media is exhausted (full disk)
* When the current backup is completed
* When a message not captured by the above options is sent
*/
dbBackup.PercentComplete += new PercentCompleteEventHandler(Event_PercentComplete);
dbBackup.Complete += new ServerMessageEventHandler(Event_Complete);
dbBackup.NextMedia += new ServerMessageEventHandler(Event_NextMedia);
dbBackup.Information += new ServerMessageEventHandler(Event_Information);
/* You can also use SqlBackupAsync with the same parameter if you want to run
* asynchronously
*/
try
{
dbBackup.SqlBackup(dbServer);
}
catch (Exception err)
{
responseLabel.Text = err.Message;
}
[/cclN_csharp]
As you can see there’s not much to it. You just select your server, database, backup destination and then you can “fire and forget.” The events that are referenced above are all very generic and are mainly included just to show what is available with SQL SMO
[cclN_csharp]
void Event_NextMedia(object sender, ServerMessageEventArgs e)
{
responseLabel.Text = e.Error.Message;
responseLabel.ForeColor = Color.Red;
}
void Event_Information(object sender, ServerMessageEventArgs e)
{
responseLabel.Text = e.Error.Message;
responseLabel.ForeColor = Color.Red;
}
void Event_Complete(object sender, ServerMessageEventArgs e)
{
responseLabel.Text = e.Error.Message;
responseLabel.ForeColor = Color.Green;
}
void Event_PercentComplete(object sender, PercentCompleteEventArgs e)
{
dbProgress.Value = e.Percent;
dbProgress.Update();
}
[/cclN_csharp]
The restore process is equally simple from the SMO side of things, you just have to be careful due to the requirement of having exclusive access to the database prior to restoring it. If you aren’t in a production environment it is POSSIBLE to shutdown the SQL server then immediately run your restore after you start it back up. But I’ve included a method that I consider to be a little “cleaner.” (You may disagree and I’m always open to suggestions for improving it)
[cclN_csharp]
Server dbServer = new Server(new ServerConnection(“
Restore dbRestore = new Restore();
dbRestore.Database = “
dbRestore.Action = RestoreActionType.Database;
/* Keep in mind that the file path you use here is relative to the SQL Server, not your local file system */
dbRestore.Devices.AddDevice(@”
/* Leaving the ReplaceDatabase value set to the default of false will not create
* a new database image so the database that is set must exist on the SQL server
* If you change the Incremental value to true it will create a new image of the
* database regardless of whether it currently exists or not
*/
dbRestore.ReplaceDatabase = true;
/* Leaving the NoRecovery value set to the default of false the tail end of the log
* is backed up and the database will not be in a Restoring state
* If you change the NoRecovery value to true then the database will be left in a
* Restoring state.
* This option is only used when the log is backed up as well
*/
//dbRestore.NoRecovery = false;
/* There are several events that you can wire up to the Restore object
* These will allow you to keep track of:
* The current progress of the restore
* When the current backup media is exhausted (next disk)
* When the current backup is completed
* When a message not captured by the above options is sent
*/
dbRestore.PercentComplete += new PercentCompleteEventHandler(Event_PercentComplete);
dbRestore.Complete += new ServerMessageEventHandler(Event_Complete);
dbRestore.NextMedia += new ServerMessageEventHandler(Event_NextMedia);
dbRestore.Information += new ServerMessageEventHandler(Event_Information);
/* You can also use SqlRestoreAsync with the same parameter if you want to run
* asynchronously
*
***************************** IMPORTANT NOTE ********************************
* In order to perform a restore you must be able to acquire an exclusive lock
* on the database or it will fail
*
* You can easily check to see what processes are currently running on the
* database with the following command (alternatively you can use SP_WHO)
*
* SELECT spid
* FROM master..SysProcesses
* WHERE DBID IN (SELECT dbid FROM master..SysDatabases WHERE name = ‘EPSS_PRD_EN’)
*
* Once you have the list of the running processes against the DB you can use the
* KILL procedure to terminate the session in order to free up the DB for restore
*/
try
{
dbRestore.SqlRestore(dbServer);
}
catch (Exception err)
{
responseLabel.Text = err.Message;
}
[/cclN_csharp]
As you can see in the comment above the call to the SqlRestore method (inconspicuously called out as an important note) you must have exclusive access to restore a database or it will throw an error and fail. I’ve read several different methods from various sources about ways to accomplish the exclusive access and finally settled on the one I’ve included here as the easiest and most reliable.
A couple key things to remember is that the Service account for SQL Server has to be able to read / write to the folder you are using for the backup and restores. Hopefully it goes with out saying that another requirement is that the login you are using to the server has to have all of the proper rights to be able to backup and restore the database (sysadmin, db_owner).
There are most likely other features of the SMO backup and restore that I’ve missed in this post, but I’ve just begun using it within the past week and am still ramping up my knowledge on it. So far I’ve not come across an instance where the above code (or a slightly altered version of it) has not been able to perform my required backup / restore operations (working on SQL 2008, 2005 and 2000).