C#：备份和恢复SQL Server数据库

C#：备份和恢复SQL Server数据库

Posted on 2008-06-19 23:54 会痛的石头 阅读(178) 评论(0) 编辑 收藏

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();
            }
        }