利用SQLDMO备份和还原数据库

利用SQLDMO备份和还原数据库using System;using System.Collections;using SQLDMO;namespace zj_SQLBackupAndRestore{/// <summary>/// zj_SQLBackupAndRestore 的摘要说明。/// </summary>/// <creator>zj</creator>/// <createDate>2006.12.15.AM</createDate>public class zj_SQLBackupAndRestore{#region "构造函数"/// <summary>/// 不带参数的构造函数/// </summary>public zj_SQLBackupAndRestore(){//// TODO: 在此处添加构造函数逻辑//}#endregion#region "数据库参数返回"/// <summary>/// 返回服务器列表/// </summary>/// <returns>返回服务器列表的ArrayList对象</returns>public static ArrayList SqlServerNameList(){try{SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();for(int i=0;i<sqlServers.Count;i++){object srvname = sqlServers.Item(i + 1);if(srvname != null){sqlServerName.Add(srvname);}}return sqlServerName;}catch(Exception ex){errorMessage=ex.Message;return null;}}/// <summary>/// 数据库服务器连接/// </summary>/// <param name="sServer">服务器地址</param>/// <param name="sUserName">数据库用户名</param>/// <param name="sPwd">数据库密码</param>/// <returns>true/false</returns>public static bool SqlCon(string sServer,string sUserName,string sPwd){sqlServer=sServer;sqlUserName=sUserName;sqlPwd=sPwd;try{srv = new SQLDMO.SQLServerClass();srv.Connect(sqlServer,sqlUserName,sqlPwd);return true;}catch(Exception ex){errorMessage=ex.Message;return false;}}/// <summary>/// 返回数据库对象/// </summary>/// <returns>sqlTableName.ToArray()</returns>public static ArrayList SqlDBNameList(){foreach(SQLDMO.Database db in srv.Databases) { if(db.Name!=null){sqlDBName.Add(db.Name);}}return sqlDBName;}/// <summary>/// 返回数据库表对象/// </summary>/// <param name="sqlDataBase">数据库名</param>/// <returns>返回数据库表对象</returns>public static ArrayList SqlTableNameList(string sqlDataBase){for(int i=0;i<srv.Databases.Count;i++) { if(srv.Databases.Item(i+1,"dbo").Name ==sqlDataBase) { SQLDMO._Database db= srv.Databases.Item(i+1,"dbo"); sqlTableName.Clear(); for(int j=0;j<db.Tables.Count;j++) { sqlTableName.Add(db.Tables.Item(j+1,"dbo").Name); } } } return sqlTableName;}/// <summary>/// 返回数据库存储过程对象/// </summary>/// <param name="sqlDataBase">数据库名</param>/// <returns>返回数据库存储过程对象</returns>public static ArrayList SqlStoredProceduresNameList(string sqlDataBase){for(int i=0;i<srv.Databases.Count;i++) { if(srv.Databases.Item(i+1,"dbo").Name ==sqlDataBase) { SQLDMO._Database db= srv.Databases.Item(i+1,"dbo"); sqlStoredProceduresName.Clear();for(int j=0;j<db.StoredProcedures.Count;j++) { sqlStoredProceduresName.Add(db.StoredProcedures.Item(j+1,"dbo").Name);} } } return sqlStoredProceduresName;}/// <summary>/// 数据库视图对象/// </summary>/// <param name="sqlDataBase">数据库名</param>/// <returns>数据库视图对象</returns>public static ArrayList SqlViewsNameList(string sqlDataBase){for(int i=0;i<srv.Databases.Count;i++) { if(srv.Databases.Item(i+1,"dbo").Name ==sqlDataBase) { SQLDMO._Database db= srv.Databases.Item(i+1,"dbo"); sqlViewName.Clear(); for(int j=0;j<db.Views.Count;j++) { sqlViewName.Add(db.Views.Item(j+1,"dbo").Name); }}}return sqlViewName;}#endregion#region "数据库备份"/// <summary>/// 备份数据库/// </summary>/// <param name="sServer">服务器名</param>/// <param name="sUserName">用户名</param>/// <param name="sPwd">密码</param>/// <param name="BackUpDB">要备份的数据库名</param>/// <param name="FilePath">备份文件存放路径</param>/// <param name="BakcUpDBName">备份文件名</param>/// <param name="TruncateLog">备份日志选项。其选项有：NoLog - 不备份交易日志/0。NoTruncate - 备份交易日志。日志里提供时间标记/1。Truncate - 备份交易日志，但不保留交易纪录/2。</param> /// <returns>true/false</returns>public static bool SqlBackUp(string sServer,string sUserName,string sPwd,string BackUpDB,string FilePath,string BakcUpDBName,TruncateLog TLog){//实例对象bkps=new SQLDMO.BackupClass();if(SqlCon(sServer,sUserName,sPwd)){try{bkps.Database=BackUpDB; //指定需备份的数据库bkps.Action = 0;bkps.BackupSetName=BakcUpDBName;bkps.Files=@FilePath +@"\" + BakcUpDBName; //指定备份文件路径bkps.Initialize =true; //如设置为真(True)，该备份装置将取代其他备份媒介而成为首选。bkps.TruncateLog=(SQLDMO.SQLDMO_BACKUP_LOG_TYPE)TLog;bkps.SQLBackup(srv);bkps = null;return true;}catch(Exception ex){errorMessage=ex.Message;errorMessage="";return false;}finally{srv.DisConnect();}}else{return false;}}/// <summary>/// 枚举备份日志类型/// </summary>public enum TruncateLog{NoLog=SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_NoLog, //不备份交易日志NoTruncate=SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_NoTruncate, //备份交易日志。日志里提供时间标记Truncate=SQLDMO.SQLDMO_BACKUP_LOG_TYPE.SQLDMOBackup_Log_Truncate //备份交易日志，但不保留交易纪录}#endregion#region "数据库还原"/// <summary>/// 还原数据库/// </summary>/// <param name="sServer">服务器名</param>/// <param name="sUserName">用户名</param>/// <param name="sPwd">密码</param>/// <param name="FilePath">要还原的数据库文件路径/param>/// <param name="RestoreDBName">要还原的数据库名</param>/// <returns>true/false</returns>public static bool SQLRestoreDB(string sServer,string sUserName,string sPwd,string FilePath,string RestoreDBName){oRestore = new SQLDMO.RestoreClass();oRestore.Action = 0 ; if(SqlCon(sServer,sUserName,sPwd)){try{SQLDMO.QueryResults qr = srv.EnumProcesses(-1) ; int iColPIDNum = -1 ; int iColDbName = -1 ; //杀死其它的连接进程for(int i=1;i<=qr.Columns;i++) { string strName = qr.get_ColumnName(i) ; if (strName.ToUpper().Trim() == "SPID") { iColPIDNum = i ; } else if (strName.ToUpper().Trim() == "DBNAME") { iColDbName = i ; } if (iColPIDNum != -1 && iColDbName != -1) break ; } for(int i=1;i<=qr.Rows;i++) { int lPID = qr.GetColumnLong(i,iColPIDNum) ; string strDBName = qr.GetColumnString(i,iColDbName) ; if (strDBName.ToUpper() == "CgRecord".ToUpper()) srv.KillProcess(lPID) ; } oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;oRestore.Database = RestoreDBName;oRestore.Files = @FilePath;oRestore.FileNumber = 1;oRestore.ReplaceDatabase = true;oRestore.SQLRestore(srv);return true;}catch(System.Exception ex){errorMessage=ex.Message;return false;}finally{srv.DisConnect();}}else{return false;}}#endregion#region "内部函数"/// <summary>/// 服务器地址/// </summary>private static object sqlServer;/// <summary>/// 数据库用户名/// </summary>private static object sqlUserName;/// <summary>/// 数据库密码/// </summary>private static object sqlPwd;/// <summary>/// 数据库备份对象/// </summary>private static SQLDMO.Backup bkps;/// <summary>/// 数据库还原对象/// </summary>private static SQLDMO.Restore oRestore;/// <summary>/// 数据库连接对象/// </summary>private static SQLDMO.SQLServer srv;private static SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass() ;/// <summary>/// 错误消息/// </summary>private static string errorMessage;/// <summary>/// 服务器对象/// </summary>private static ArrayList sqlServerName=new ArrayList();/// <summary>/// 数据库表对象/// </summary>private static ArrayList sqlTableName=new ArrayList();/// <summary>/// 数据库对象/// </summary>private static ArrayList sqlDBName=new ArrayList();/// <summary>/// 数据库存储过程对象/// </summary>private static ArrayList sqlStoredProceduresName=new ArrayList();/// <summary>/// 数据库视图对象/// </summary>private static ArrayList sqlViewName=new ArrayList();#endregion}}

From:http://bbs.bccn.net/thread-121981-1-1.html