private void DBForm_Load(object sender, EventArgs e)
{
backupProgress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Backup_PercentComplete);
restoreProgress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Restore_PercentComplete);
DataTable dt = BackupRestore.LoadBackupHistory();
dataGridView1.DataSource = dt;
}
public static void CancelDbRestore(SQLDMO.Restore oRestore, SQLDMO.RestoreSink_PercentCompleteEventHandler Restore_PercentComplete)
{
try
{
oRestore.Abort();
oRestore.PercentComplete -= Restore_PercentComplete;
}
catch (Exception e)
{
TopFashion.WriteLog.CreateLog("数据库操作", "CancelDbRestore", "error", "取消数据库还原失败：" + e.Message);
}
}
public static void CancelDbRestore(SQLDMO.Restore oRestore, SQLDMO.RestoreSink_PercentCompleteEventHandler Restore_PercentComplete)
{
try
{
oRestore.Abort();
oRestore.PercentComplete -= Restore_PercentComplete;
}
catch (Exception e)
{
MessageBox.Show("取消数据库还原失败：" + e.Message);
}
}
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;
}
}
/// <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>
/// 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="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>
/// 恢复指定数据库文件
/// </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恢复数据库
/// </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);
}
