C#：備份和恢複SQL Server資料庫

Backup 對象 (SQL-DMO)

Backup 對象 定義Microsoft SQL Server 的資料庫或記錄備份操作.

備忘

使用 Backup 對象, 你可以:

• 備份一個 SQL Server 資料庫或資料庫交易記錄.
• 產生一個Transact-SQL BACKUP 定義備份的聲明.
• 監聽一個備份操作，並將狀態報表給使用者.

For SQL Server, a database delimits the largest backup unit. Though many different database backup images can be maintained on any single medium, a backup cannot span more than a single database. By default, backup operations performed with the Backup object back up a complete database.

SQL Server can write a backup to one of four media types: disk, tape, named pipe, or a proprietary media called a backup device. SQL Server supports backup striping. A striped backup is one directed to more than a single device. When striped, a backup is written across the devices in equal chunks. Striping is supported to a single media type only. That is, a backup can be written to two tape devices. A backup cannot be written half to a tape device and the other half to a disk.

At a minimum, you must supply values for a backup source and a backup target when using the Backup object. The Database property specifies the backup operation source. SQL-DMO implements supported media types in the Backup object properties Files, Devices, Pipes, and Tapes. Use one media type property to specify the backup operation target.

To perform a complete database backup

1. Create a new Backup object.
2. Set the Database property, naming the database backed up.
3. Set a media property to name the target device(s).
4. Call the SQLBackup method.

In many installations, complete database backup is not a viable option. The Backup object offers access to a number of strategies that ensure data integrity by capturing a subset of the database image.

To back up a database transaction log

1. Create a new Backup object
2. Set the Database property, naming the database backed up.
3. Set the Action property to SQLDMOBackup_Log.
4. Set a media property to name the target device(s).
5. Call the SQLBackup method.

To perform a differential backup

1. Create a new Backup object
2. Set the Database property, naming the database backed up.
3. Set the Action property to SQLDMOBackup_Incremental.
4. Set a media property to name the target device(s).
5. Call the SQLBackup method.

To back up specific filegroups

1. Create a new Backup object
2. Set the Database property, naming the database backed up.
3. Set the DatabaseFileGroups property, naming the filegroup(s) providing backup source data.
4. Set a media property to name the target device(s).
5. Call the SQLBackup method.

To back up specific files

1. Create a new Backup object
2. Set the Database property, naming the database backed up.
3. Set the Action property to SQLDMOBackup_Files.
4. Set the DatabaseFiles property, naming the file(s) providing backup source data.
5. Set a media property to name the target device(s).
6. Call the SQLBackup method.

Settings for any other Backup object properties are optional. Use the optional settings when conditions require extraordinary processing. For example, the MediaName and MediaDescription properties provide, primarily, data used to ensure media availability for tape devices and are applicable when the backup operation defined will initialize the media. For more information about property applicability and use, see individual property documentation.

 

        /**//// <summary>
        /// 備份MSSQL SERVER資料庫
        /// </summary>
        /// <param name="DSN">伺服器名</param>
        /// <param name="UID">使用者名稱</param>
        /// <param name="PWD">密碼</param>
        /// <param name="DB">資料庫名</param>
        /// <param name="FilePath">備份檔案名</param>
        public static void MSSQL_BackupDatabase(string DSN, string UID, string PWD, string DB, string FilePath)
        {
            SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(DSN, UID, PWD);
                oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
                oBackup.Database = DB;
                oBackup.Files = FilePath;
                oBackup.BackupSetName = DB;
                oBackup.BackupSetDescription = string.Format("{0} 備份", DB);
                oBackup.Initialize = true;
                oBackup.SQLBackup(oSQLServer);
            }
            catch
            {
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }

        /**//// <summary>
        /// 恢複MSSQL SERVER資料庫
        /// </summary>
        /// <param name="DSN">伺服器名</param>
        /// <param name="UID">使用者名稱</param>
        /// <param name="PWD">密碼</param>
        /// <param name="DB">資料庫名</param>
        /// <param name="FilePath">備份檔案名</param>
        public static void MSSQL_RestoreDatabase(string DSN, string UID, string PWD, string DB, string FilePath)
        {
            SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(DSN, UID, PWD);
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = DB;
                oRestore.Files = FilePath;
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                oRestore.SQLRestore(oSQLServer);
            }
            catch
            {
                throw;
            }
            finally
            {
                oSQLServer.DisConnect();
            }
        }