Creating SQL Server database backup using the Microsoft.SqlServer.Management.Smo

Assemblies to be referenced can be found in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies. The "100" part in the path may need to be changed to reflect the SQL Server version in use.

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended

A backup can be created in either sync or async mode. Below is a basic console implementation built around  the Backup class of the library.

using System;
using Microsoft.SqlServer.Management.Smo;

namespace SqlServerBackupStudy
{
    public class Program
    {
        public static void Main(string[] args)
        {
            TestCreateBackup(
                ".", 
                "MyDatabase", 
                @"c:\backup\test_backup.bak");

            Console.Write("\n\nAny key...");
            Console.ReadKey();
        }

        private static void TestCreateBackup(
            string serverName, 
            string databaseName, 
            string backupPath)
        {
            try
            {
                var server = new Server(serverName);

                var backup = new Backup
                    {
                        Database = databaseName, 
                        Initialize = true
                    };

                backup.Devices.Add(new BackupDeviceItem(
                            backupPath,
                            DeviceType.File));

                backup.SqlBackup(server);
            }
            catch (Exception ex)
            {
                Console.WriteLine(
                    "{0}\n{1}", 
                    ex.GetType(), 
                    ex.Message);
            }
        }
    }
}

Microsoft.SqlServer.Management.Smo.FailedOperationException can be caused by non-existent or unreachable server or database, as well as by invalid backup path. My guess that insufficient disk space should cause same exception.

It is important to understand that in case of DeviceType.File the destination path is meant to be a path on the machine that runs the SQL Server. In other words, do not expect creating backup of a remote database on your local drive. This may change eventually.

Database restoring routine uses the Restore class, and otherwise looks almost identical to the backup routine.



private static void TestRestoreFromBackup(
    string serverName, 
    string databaseName, 
    string backupPath)
{
    try
    {
        var server = new Server(serverName);

        var restore = new Restore
            {
                Database = databaseName
            };

        restore.Devices.Add(new BackupDeviceItem(
            backupPath,
            DeviceType.File));

        restore.SqlRestore(server);
    }
    catch (Exception ex)
    {
        Console.WriteLine(
            "{0}\n{1}",
            ex.GetType(),
            ex.Message);
    }
}

The exception may have one and more inner exception levels. The lowest one is the most instructive. Here is an example of three levels of exception.

1. Restore failed for server [server]
2. An exception occurred while executing a Transact-SQL statement or batch.
3. Cannot open backup device '...'. Operating system error 3(The system cannot find the path specified.).\r\nRESTORE DATABASE is terminating abnormally.

Also one has to ensure that the target database is not in use. Otherwise the following exception would pop up.

Exclusive access could not be obtained because the database is in use.\r\nRESTORE DATABASE is terminating abnormally.