Backup and Restore SQL Server databases programmatically with SMO
By: Arshad Ali | Updated: 2009-10-01 | Comments (17) | Related: 1 | 2 | 3 | 4 | More > Backup
Problem
In my last set of tips, I discussed SMO at a basic level. In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.
Solution
As I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.
Examples
C# Code Block 1 - Full Backups - This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up. The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set. The Backup class also has a property called ExpirationDate which indicates how long backup data is considered valid and to expire the backup after that date. The backup object instance generates several events during the backup operation, we can write event-handlers for these events and wire them up with events. This is what I am doing for progress monitoring. I am wiring up CompletionStatusInPercent and Backup_Completed methods (event-handlers) with PercentComplete and Complete events of backup object instance. Finally, I am calling the SqlBackup method for starting up the backup operation, SMO provides a variant of this method called SqlBackupAsync if you want to start the backup operation asynchronously.
C# Code Block 1 - Full Database Backup |
Backup bkpDBFull = new Backup(); |
private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) |
Result:![]() |
C# Code Block 2 Differential Backups - The process of issuing differential backups is not much different from issuing full backups. To issue a differential backup, set the property Incremental = true. If you set this property the incremental/differential backup will be taken since last full backup.
C# Code Block 2 - Differential Database Backup |
Backup bkpDBDifferential = new Backup(); |
Result: ![]() |
C# Code Block 3 Transaction Log Backups - Again the process of issuing transactional log backup is not much different from issuing full backups. To issue transactional log backups, set the property Action = BackupActionType.Log instead of BackupActionType.Database as in the case of a full backup.
C# Code Block 3 - Transaction Log Backup |
Backup bkpDBLog = new Backup(); |
Result: ![]() |
C# Code Block 4 Backup with Compression - SQL Server 2008 introduces a new feature to issues backups in a compressed form. As such, SMO for SQL Server 2008 has been enhanced to support this feature. If you look at the image below you will notice the compressed backup size is almost 25% of full backup, though the level of compression depends on the several factors.
C# Code Block 4 - Backup with Compression (SQL Server 2008) |
Backup bkpDBFullWithCompression = new Backup(); |
Result:![]() |
C# Code Block 5 Full or Differential Restores - Thus far we have worked through SMO backup examples. Now let's change gears to restore with SMO. SMO provides a Restore class to restore a database, similar to the Backup class. With these classes it is necessary to specify the Action property to indicate the type of restore i.e. database, files or log. In a scenario where if you have additional differential or log backups to be restored after it is necessary to specify the NoRecovery = true except for the final restore. In this example, I am wiring up events of the Restore object instance to event-handlers for progress monitoring. Finally the SqlRestore method is called to start the restoration. If you want to start the restore operation asynchronously you would need to call SqlRestoreAsync method instead.
C# Code Block 5 - Database Restore - Full or Differential |
Restore restoreDB = new Restore(); |
Result:![]() |
To restore a database SQL Server needs to acquire exclusive lock on the database being restored. If you try to restore a database which is in use, SQL Server will throw the following exception:
C# Code Block 6 Transaction Log Restore - The process of restoring a transactional log is similar to restoring a full or differential backup. While restoring a transactional log, it is necessary to set the property Action = RestoreActionType.Log instead of RestoreActionType.Database as in case of full/differential restore. Here is an example:
C# Code Block 6 - Database Restore - Log |
Restore restoreDBLog = new Restore(); |
Result:![]() |
C# Code Block 7 Database Restore to a new location - At times you need to create a new database and restore to a new physical location which differs from the original database. For that purpose, the Restore class has the RelocateFiles collection which can be completed for each file with the new location as shown in the code below.
C# Code Block 7 Database Restore - Different location |
Restore restoreDB = new Restore(); |
Result:![]() |
Complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.
Notes:
-
Location of assemblies in SQL Server 2005 is the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
-
Location of assemblies in SQL Server 2008 is the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
-
In SQL Server 2005, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.Smo (microsoft.sqlserver.smo.dll) assembly.
-
In SQL Server 2008, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.SmoExtended (microsoft.sqlserver.smoextended.dll) assembly.
-
If you are restoring a transaction log, you can specify a particular point in time with ToPointInTime property of the Restore class.
-
The Restore class methods (SqlVerify, SqlVerifyAsync and SqlVerifyLatest) to verify and validate (backup set is complete and the entire backup is readable) the backup media before restoration.
-
The SQL Server service account must have access to the folders where backup or restore operations are executed.
-
You need to have sufficient permissions to perform backup and restore operations. For example, for backup you need to be either in sysadmin/db_owner/db_backupoperator role or must have BACKUP DATABASE or BACKUP LOG permission on the database.
-
If you try to connect SQL Server 2008 from SMO 2005, you will get an exception "SQL Server <10.0> version is not supported".
Next Steps
-
Review the following tips:
-
Review the MSSQLTips Backup and Recovery category with 40+ tips.
Last Updated: 2009-10-01
About the author

View all my tips