添加数据库备份&恢复功能

您希望备份完整的数据库，还是只希望能够恢复到更改数据库之前的状态?

第一种情况在你的程序中是不常见的，毕竟:你确定在你备份的时候没有其他人在修改数据库吗?

后一种情况很常见:在对数据库进行几次更改之前，您要记住更改之前的情况，进行更改，并决定是保留更改还是回到更改之前的情况，例如由于错误。

后者是使用System.Data.SqlClient.SqlTransaction类完成的。

using (var sqlConnection = GetSqlConnection(...))
{
    try (sqlConnection.Open()
    {   // before making the changes: remember the state of the database
        using (var transaction = sqlConnection.BeginTransaction())
        {
            bool changesOk = true;
            try
            {
                changesOk = ChangeDatabase(...);
            }
            catch (Exception exc)
            {
                changesOk = false;
            }
            if (!changesOk)
            {   // go back to the state at the beginning of the transaction
                transaction.RollBack();
            }
            else
            {   // no error: save the changes:
                transaction.Commit();
            }
        }
    }
}

**用SMO程序备份和恢复SQL Server数据库**

SMO为特定任务提供实用程序类。对于备份和恢复，它提供了两个主要的实用程序类(backup和restore)，它们在Microsoft.SqlServer.Management.Smo命名空间中可用。

这是示例代码

 Backup bkpDBFull = new Backup();
    /* Specify whether you want to back up database or files or log */
    bkpDBFull.Action = BackupActionType.Database;
    /* Specify the name of the database to back up */
    bkpDBFull.Database = myDatabase.Name;
    /* You can take backup on several media type (disk or tape), here I am
     * using File type and storing backup on the file system */
    bkpDBFull.Devices.AddDevice(@"D:'AdventureWorksFull.bak", DeviceType.File);
    bkpDBFull.BackupSetName = "Adventureworks database Backup";
    bkpDBFull.BackupSetDescription = "Adventureworks database - Full Backup";
    /* You can specify the expiration date for your backup data
     * after that date backup data would not be relevant */
    bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);
    /* You can specify Initialize = false (default) to create a new 
     * backup set which will be appended as last backup set on the media. You
     * can specify Initialize = true to make the backup as first set on the
     * medium and to overwrite any other existing backup sets if the all the
     * backup sets have expired and specified backup set name matches with
     * the name on the medium */
    bkpDBFull.Initialize = false;
    /* Wiring up events for progress monitoring */
    bkpDBFull.PercentComplete += CompletionStatusInPercent;
    bkpDBFull.Complete += Backup_Completed;
    /* SqlBackup method starts to take back up
     * You can also use SqlBackupAsync method to perform the backup 
     * operation asynchronously */
    bkpDBFull.SqlBackup(myServer);
    private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)
    {
        Console.Clear();
        Console.WriteLine("Percent completed: {0}%.", args.Percent);
    }
    private static void Backup_Completed(object sender, ServerMessageEventArgs args)
    {
        Console.WriteLine("Hurray...Backup completed." );
        Console.WriteLine(args.Error.Message);
    }
    private static void Restore_Completed(object sender, ServerMessageEventArgs args)
    {
        Console.WriteLine("Hurray...Restore completed.");
        Console.WriteLine(args.Error.Message);
    }