/// <summary>
/// 数据库还原
/// </summary>
/// <param name="url">备份文件的地址</param>
/// <returns></returns>
public bool DbRestore(string url)
{
if (exepro() != true)//执行存储过程
{
return(false);
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = database;
oRestore.Files = url;//@"d:\Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return(true);
}
catch
{
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">要还原的数据库名</param>
/// <param name="BackPath">数据库备份的路径</param>
public static bool SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
orestore.Database = DBName;
orestore.Files = BackPath;
orestore.FileNumber = 1;
orestore.ReplaceDatabase = true;
orestore.SQLRestore(oSQLServer);
return(true);
}
catch (Exception)
{
return(false);
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">要还原的数据库名</param>
/// <param name="BackPath">数据库备份的路径</param>
public static void SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
KillProcesses(DBName); //杀死此数据库的进程
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
orestore.Database = DBName;
orestore.Files = BackPath;
orestore.FileNumber = 1;
orestore.ReplaceDatabase = true;
orestore.SQLRestore(oSQLServer);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 还原bak类型备份的数据库函数
/// </summary>
/// <param name="strDbName">数据库名</param>
/// <param name="strFileName">数据库备份文件的完整路径名</param>
/// <returns></returns>
public bool RestoreDB(string strDbName, string strFileName, string userid, string pwd)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
//服务器名，数据库用户名，数据库用户名密码
svr.Connect(M_DataBaseManageConfig.Server, userid, pwd);
SQLDMO.QueryResults qr = svr.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;
}
}
//杀死使用strDbName数据库的进程
for (int i = 1; i <= qr.Rows; i++)
{
int lPID = qr.GetColumnLong(i, iColPIDNum);
string strDBName = qr.GetColumnString(i, iColDbName);
if (strDBName.ToUpper() == strDbName.ToUpper())
{
svr.KillProcess(lPID);
}
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = 0;
res.Files = strFileName;
res.Database = strDbName;
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return(true);
}
catch
{
return(false);
}
finally
{
svr.DisConnect();
}
}
private void btnRestore_Click(object sender, EventArgs e)
{
if (textBoxLJHF.Text == "")
{
MessageBox.Show("请确定数据库备份地址", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (MessageBox.Show("恢复数据库会覆盖当前数据，建议建立新数据库后进行此操作，继续进行恢复操作吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No)
{
return;
}
SQLDMO.Restore restore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();
server.Connect(strDataBaseAddr, strDataBaseUser, strDataBasePass);
//KILL DataBase Process
sqlConn.ConnectionString = strConn;
sqlConn.Open();
sqlComm.CommandText = "use master Select spid FROM sysprocesses ,sysdatabases Where sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='" + strDataBaseName + "'";
sqldr = sqlComm.ExecuteReader();
while (sqldr.Read())
{
server.KillProcess(Convert.ToInt32(sqldr[0].ToString()));
}
sqldr.Close();
sqlConn.Close();
try
{
restore.Action = 0;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step2);
restore.PercentComplete += pceh;
restore.Database = strDataBaseName;
restore.Files = @textBoxLJHF.Text;
restore.ReplaceDatabase = true;
restore.SQLRestore(server);
MessageBox.Show("数据库恢复成功！");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
server.DisConnect();
toolStripProgressBar1.Value = 0;
}
}
//确定
protected void Save_Click(object sender, EventArgs e)
{
if (exepro() != true)
{
Response.Write("执行失败，请重试！");
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect("" + sUrl + "", "" + sUser + "", "" + sPwd + "");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "" + sName + "";
oRestore.Files = sDbUrl;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
//因进程被kill重连db
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["haokeConnectionString"].ConnectionString);
sqlConn.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO hk_DbRecovery(DbName,DbUrl,UserID,UserName,SetTimes) VALUES('" + sDbName + "','" + sDbUrl + "','" + this.Session["userid"] + "','" + this.Session["username"] + "',GETDATE())", sqlConn);
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.ExecuteNonQuery();
//日志
SqlCommand sqlComm2 = new SqlCommand("INSERT INTO hk_SystemLog(LogContent,ModuId,UserID,UserName,Nowtime,IPAddress,DeptID,DeptName,QxStr) VALUES('还原数据库【" + sDbName + "】','" + 60109 + "','" + this.Session["userid"] + "','" + this.Session["username"] + "',GETDATE(),'" + Request.UserHostAddress + "','" + this.Session["deptid"] + "','" + this.Session["deptname"] + "','" + this.Session["deptqx"] + "')", sqlConn);
sqlComm2.CommandType = System.Data.CommandType.Text;
sqlComm2.ExecuteNonQuery();
sqlConn.Close();
mydb.Alert_Close("还原数据库成功！");
}
catch
{
mydb.Alert("还原数据库失败，请重试！");
}
finally
{
oSQLServer.DisConnect();
}
}
}
/// <summary>
/// Database recovery
/// </summary>
public string DbRestore()
{
if (exepro() != true)
{
return("Operation Failed");
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
exepro();
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
oRestore.PercentComplete += pceh;
oRestore.Database = database;
///Self-modification
oRestore.Files = restoreFile;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return("The Database Was Restored Successfully");
}
catch (Exception e)
{
return("Recovery database failed due to:" + e.Message);
throw e;
}
finally
{
oSQLServer.DisConnect();
}
}
}
/// <summary>
/// 数据库恢复
/// </summary>
/// <returns></returns>
public string DbRestore()
{
if (exepro() != true)//执行存储过程
{
return("操作失败");
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
exepro();
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = database;
//自行修改
oRestore.Files = Server.MapPath(DropDownList_path.SelectedValue.Trim());
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return("ok 恢复成功！");
}
catch (Exception e)
{
return("恢复数据库失败");
throw e;
}
finally
{
oSQLServer.DisConnect();
}
}
}
//数据库恢复
public static void DbRestore(string openfilepath)
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = true;
oSQLServer.Connect(".", "", "");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "FoodManage_database";
oRestore.Files = openfilepath;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="filename">备份集文件的全路径</param>
/// <param name="connStrBuilder">连接字符串构造器</param>
/// <param name="Restore_PercentComplete">进度</param>
/// <param name="oRestore">数据库还原服务对象</param>
public bool SQLDbRestore(string filename, SqlConnectionStringBuilder connStrBuilder, SQLDMO.RestoreSink_PercentCompleteEventHandler Restore_PercentComplete, out SQLDMO.Restore oRestore)
{
string ServerIP = connStrBuilder.DataSource;
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
//因为“数据库正在使用，所以无法获得对数据库的独占访问权。”不一定是由于其他进程的占用造成，还有其他的原因，所以要脱机再联机会比较保险...
KillProcess(DBName);
OffAndOnLine(DBName);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.PercentComplete += Restore_PercentComplete;
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = DBName;
oRestore.Files = @"" + string.Format("[{0}]", filename) + "";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return(true);
}
catch (Exception e)
{
MessageBox.Show("恢复时出错：" + e.Message);
}
finally
{
oSQLServer.DisConnect();
}
return(false);
}
/// < summary>
/// SQL恢复数据库
/// < /summary>
/// <param name="id">备份集ID</param>
/// <param name="Restore_PercentComplete">进度</param>
/// <param name="oRestore">数据库还原服务对象</param>
public static bool SQLDbRestore(int id, SQLDMO.RestoreSink_PercentCompleteEventHandler Restore_PercentComplete, out SQLDMO.Restore oRestore)
{
oRestore = null;
TopFashion.SQLDBHelper SqlHelper = new TopFashion.SQLDBHelper();
object obj = SqlHelper.GetSingle("select Path from Record where ID=" + id, true);
if (obj != null && obj != DBNull.Value)
{
string filename = obj.ToString();
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(TopFashion.Configs.ConnString);
string ServerIP = connStrBuilder.DataSource;
if (ServerIP == "." || ServerIP == "(local)" || ServerIP == "127.0.0.1")
{
}
else
{
StringBuilder TargetDir = new StringBuilder();
TargetDir.Append(@"\\");
TargetDir.Append(ServerIP);
TargetDir.Append("\\");
TargetDir.Append("DatabaseBackup");
TargetDir.Append("\\");
filename = TargetDir + Path.GetFileName(filename);
}
if (File.Exists(filename))
{
string LoginUserName = connStrBuilder.UserID;
string LoginPass = connStrBuilder.Password;
string DBName = connStrBuilder.InitialCatalog;
oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginUserName, LoginPass);
//因为数据库正在使用，所以无法获得对数据库的独占访问权。不一定是由于其他进程的占用，还有其他的原因，所以要脱机再联机...
//KillProcess(DBName);
//KillSqlProcess(oSQLServer, DBName);
//OffAndOnLine(DBName);
OffLine(DBName);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.PercentComplete += Restore_PercentComplete;
oRestore.Database = DBName;
oRestore.Files = @"" + string.Format("[{0}]", filename) + "";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);//这里可能存在问题！
OnLine(DBName);
return(true);
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbRestore", "error", "恢复时出错：" + e.Message);
}
finally
{
oSQLServer.DisConnect();
}
}
else
{
TopFashion.WriteLog.CreateLog("数据库操作", "SQLDbRestore", "error", "找不到要还原的备份数据库文件 [" + filename + "]");
}
}
return(false);
}
/// <summary>
/// �ָ����ݿ⣬�ָ�ǰɱ�������뱾���ݿ���ؽ���
/// </summary>
/// <param name="strDbName">���ݿ���</param>
/// <param name="strFileName">���·��</param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool RestoreDB(string strDbName, string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
//ȡ�����еĽ����б�
SQLDMO.QueryResults qr = svr.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() == strDbName.ToUpper())
svr.KillProcess(lPID);
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
res.Files = strFileName;
res.Database = strDbName;
res.FileNumber = 1;
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return true;
}
catch (Exception err)
{
//throw (new Exception("" + err.Message));
ShowError("�ָ����ݿ�ʧ��,��ر����к����ݿ����ӵij���" + err.Message);
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 恢复数据库，恢复前杀死所有与本数据库相关进程
/// </summary>
/// <param name="strDbName">数据库名</param>
/// <param name="strFileName">存放路径</param>
/// <param name="pgbMain"></param>
/// <returns></returns>
public bool RestoreDB(string strDbName, string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
//取得所有的进程列表
SQLDMO.QueryResults qr = svr.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() == strDbName.ToUpper())
{
svr.KillProcess(lPID);
}
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
res.Files = strFileName;
res.Database = strDbName;
res.FileNumber = 1;
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return(true);
}
catch (Exception err)
{
//throw (new Exception("" + err.Message));
ShowError("恢复数据库失败,请关闭所有和该数据库连接的程序！" + err.Message);
return(false);
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 恢复指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">还原文件路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <returns></returns>
public static bool RestoreDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
pBar = bar;
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();
string str = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
try
{
// 连接到数据库服务器
sqlServer.Connect(strServerName, strUserName, strPassword);
// 获取所有的进程列表
SQLDMO.QueryResults qr = sqlServer.EnumProcesses(-1);
// 查找 SPID 和 DBName 的位置
int iColPIDNum = -1; // 标记 SPID 的位置
int iColDbName = -1; // 标记 DBName 的位置
for (int i = 1; i <= qr.Columns; i++)
{
string strName = qr.get_ColumnName(i);
if (strName.ToUpper().Trim() == "SPID")
{
// 标记 SPID
iColPIDNum = i;
}
else if (strName.ToUpper().Trim() == "DBNAME")
{
// 标记 DBName
iColDbName = i;
}
// 如果找到 SPID 和 DBName, 则跳出循环
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() == strDbName.ToUpper())
{
sqlServer.KillProcess(lPID);
}
}
// 实例化还原操作对象
SQLDMO.Restore res = new SQLDMO.RestoreClass();
// 路径中有空格(不包括文件名) 备份到路径的根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
str = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
File.Move(strFileName, str);
}
else
{
str = strFileName;
}
// 数据库存放的路径和要恢复的数据库名字
res.Files = str;
res.Database = strDbName;
// 所恢复的数据库文件的类型
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
res.ReplaceDatabase = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
res.PercentComplete += pceh;
}
#endregion
// 执行数据库恢复
res.SQLRestore(sqlServer);
return(true);
}
catch (Exception ex)
{
string tmpErr = "还原失败";
if (ex.Message.IndexOf("文件不是有效的 Microsoft 磁带格式备份集") > 1)
{
tmpErr = "文件格式不正确";
}
MessageBox.Show(tmpErr);
return(false);
}
finally
{
sqlServer.DisConnect();
// 文件夹名称中有空格 将备份的文件移回到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
File.Move(str, strFileName);
}
}
}
/// <summary>
/// 使用SQL DMO恢复数据库
/// </summary>
/// <param name="serverName"></param>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <param name="dbName"></param>
/// <param name="dbBakFileName"></param>
public void RestoreSqlDb(string serverName, string userName, string pwd, string dbName, string dbBakFileName)
{
string dbFileDir = string.Empty;
SQLDMO.SQLServer sqlServerObj = new SQLDMO.SQLServer();
SQLDMO.Database dbObj = new SQLDMO.Database();
SQLDMO.DBFile dbFileObj = new SQLDMO.DBFile();
SQLDMO.LogFile logFileObj = new SQLDMO.LogFile();
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sqlServerObj.Connect(serverName, userName, pwd);
dbFileDir = sqlServerObj.Registry.SQLDataRoot + @"\DATA\";
if (!Directory.Exists(dbFileDir))
{
Directory.CreateDirectory(dbFileDir);
}
}
catch (Exception exp)
{
throw new Exception("无法连接到Sql Server，" + exp.Message, exp);
}
#region 属性设置
try
{
dbObj.Name = dbName;
dbFileObj.Name = dbBakFileName;
dbFileObj.PhysicalName = dbFileDir + dbName + ".mdf";
dbFileObj.PrimaryFile = true;
dbFileObj.Size = 3;
dbFileObj.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
dbFileObj.FileGrowth = 1;
dbObj.FileGroups.Item("PRIMARY").DBFiles.Add(dbFileObj);
logFileObj.Name = dbName + "Log";
logFileObj.PhysicalName = dbFileDir + dbName + "_log.ldf";
logFileObj.Size = 3;
dbObj.TransactionLog.LogFiles.Add(logFileObj);
sqlServerObj.Databases.Add(dbObj);
}
catch (Exception exp)
{
throw new Exception("数据库属性设置失败，" + exp.Message, exp);
}
#endregion
#region 数据库恢复操作
try
{
SQLDMO.Restore restoreObj = new SQLDMO.RestoreClass();
restoreObj.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
restoreObj.Database = dbName;
//是否覆盖原数据库
restoreObj.ReplaceDatabase = true;
restoreObj.Files = dbBakFileName;// @"\data\mdernode.bak";
//restoreObj.FileNumber = 1;
restoreObj.UnloadTapeAfter = true;
restoreObj.SQLRestore(sqlServerObj);
}
catch (Exception exp)
{
throw new Exception("数据库恢复失败，" + exp.Message, exp);
}
finally
{
sqlServerObj.DisConnect();
}
#endregion
}
/// <summary>
/// 数据库还原
/// </summary>
/// <param name="url">备份文件的地址</param>
/// <returns></returns>
public bool DbRestore(string url)
{
if (exepro() != true)//执行存储过程
{
return false;
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = database;
oRestore.Files = url;//@"d:\Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return true;
}
catch
{
return false;
}
finally
{
oSQLServer.DisConnect();
}
}
}
/// <summary>
/// SQL恢复数据库
/// </summary>
/// <param name="ServerIP">SQL服务器IP或(Localhost)</param>
/// <param name="LoginName">数据库登录名</param>
/// <param name="LoginPass">数据库登录密码</param>
/// <param name="DBName">要还原的数据库名</param>
/// <param name="BackPath">数据库备份的路径</param>
public static void SQLDbRestore(string ServerIP, string LoginName, string LoginPass, string DBName, string BackPath)
{
SQLDMO.Restore orestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(ServerIP, LoginName, LoginPass);
orestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
orestore.Database = DBName;
orestore.Files = BackPath;
orestore.FileNumber = 1;
orestore.ReplaceDatabase = true;
orestore.SQLRestore(oSQLServer);
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// 恢复指定数据库文件
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">还原文件路径</param>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPassword">密码</param>
/// <returns></returns>
public static bool RestoreDB(string strDbName, string strFileName, string strServerName, string strUserName, string strPassword, ProgressBar bar)
{
pBar = bar;
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();
string str = "";
string tmpPath = strFileName.Substring(0, strFileName.LastIndexOf("\\")).ToString();
int isEmpty = tmpPath.IndexOf(" ");
try
{
// 连接到数据库服务器
sqlServer.Connect(strServerName, strUserName, strPassword);
// 获取所有的进程列表
SQLDMO.QueryResults qr = sqlServer.EnumProcesses(-1);
// 查找 SPID 和 DBName 的位置
int iColPIDNum = -1; // 标记 SPID 的位置
int iColDbName = -1; // 标记 DBName 的位置
for (int i = 1; i <= qr.Columns; i++)
{
string strName = qr.get_ColumnName(i);
if (strName.ToUpper().Trim() == "SPID")
{
// 标记 SPID
iColPIDNum = i;
}
else if (strName.ToUpper().Trim() == "DBNAME")
{
// 标记 DBName
iColDbName = i;
}
// 如果找到 SPID 和 DBName, 则跳出循环
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() == strDbName.ToUpper())
{
sqlServer.KillProcess(lPID);
}
}
// 实例化还原操作对象
SQLDMO.Restore res = new SQLDMO.RestoreClass();
// 路径中有空格(不包括文件名) 备份到路径的根目录的临时文件夹tmpBackup中
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
str = strFileName.Substring(0, 1).ToString() + ":\\tmp_backup.kj";
File.Move(strFileName, str);
}
else
{
str = strFileName;
}
// 数据库存放的路径和要恢复的数据库名字
res.Files = str;
res.Database = strDbName;
// 所恢复的数据库文件的类型
res.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
res.ReplaceDatabase = true;
#region 进度条处理
if (pBar != null)
{
pBar.Visible = true;
SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
res.PercentComplete += pceh;
}
#endregion
// 执行数据库恢复
res.SQLRestore(sqlServer);
return true;
}
catch (Exception ex)
{
string tmpErr = "还原失败";
if (ex.Message.IndexOf("文件不是有效的 Microsoft 磁带格式备份集") > 1)
{
tmpErr = "文件格式不正确";
}
MessageBox.Show(tmpErr);
return false;
}
finally
{
sqlServer.DisConnect();
// 文件夹名称中有空格 将备份的文件移回到用户指定的文件夹并将临时目录删除
if (isEmpty > 1 && strFileName.Substring(4).LastIndexOf("\\") > 1)
{
File.Move(str, strFileName);
}
}
}