bakServer.cs类
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using SQLDMO;
using System.Windows.Forms;
using System.Collections;
namespace MeasurementWellCurve.BLL
{
/// <summary>
/// 数据库的备份与还原
/// </summary>
public class bakServer
{
private string ServerName; //数据服务器名称
private string UserName; //用户名称
private string Password; //用户密码
private string DBName; //数据库名称
//数据库连接字符串
private string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();
/// <summary>
/// 取得数据库服务器列表
/// </summary>
/// <returns>数据库服务器列表</returns>
public ArrayList GetServerList()
{
ArrayList alServers = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
try
{
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();
for (int i = 1; i <= serverList.Count; i++)
{
alServers.Add(serverList.Item(i));
}
}
catch (Exception e)
{
throw(new Exception("取数据库服务器列表出错:"+e.Message)) ;
}
finally
{
sqlApp.Quit();
}
return alServers;
}
/// <summary>
/// 取得指定数据库列表
/// </summary>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名称</param>
/// <param name="strPwd">用户密码</param>
/// <returns>数据库列表</returns>
public ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
{
ServerName = strServerName;
UserName = strUserName;
Password = strPwd;
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
alDbs.Add(db.Name);
}
}
catch (Exception err)
{
throw (new Exception("连接数据库出错:" + err.Message));
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return alDbs;
}
/// <summary>
/// 数据库的备份
/// </summary>
/// <param name="strDbName">数据库名称</param>
/// <param name="strFileName">备份文件名(完整路径)</param>
/// <param name="strPwd">密码</param>
/// <returns>备份成功返回true ,否则返回false</returns>
public bool BackUPDB(string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
ServerName = GetServerName();
UserName = GetUserID();
Password = GetPassword();
svr.Connect(ServerName, UserName, Password);
SQLDMO.Backup bak = new SQLDMO.BackupClass();
bak.Action = 0;
//bak .Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
bak.Database = GetDBName();
bak.Files = strFileName;
bak.BackupSetDescription = "数据库备份";
bak.Initialize = true;
bak.SQLBackup(svr);
return true;
}
catch (Exception err)
{
throw (new Exception("备份数据库失败" + err.Message));
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 数据库的恢复和杀死进程
/// </summary>
/// <param name="strFileName">备份文件名</param>
/// <returns>恢复成功返回true ,否则返回false</returns>
public bool RestoreDB(string strFileName)
{
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
ServerName = GetServerName();
UserName = GetUserID();
Password = GetPassword();
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() == GetDBName().ToUpper())
svr.KillProcess(lPID);
}
SQLDMO.Restore res = new SQLDMO.RestoreClass();
res.Action = 0;
res.Files = strFileName;
res.Database = GetDBName();
res.ReplaceDatabase = true;
res.SQLRestore(svr);
return true;
}
catch (Exception err)
{
throw(new Exception("恢复数据库失败,请关闭所有和该数据库连接的程序!"+err.Message)) ;
return false;
}
finally
{
svr.DisConnect();
}
}
/// <summary>
/// 获取服务器名称
/// </summary>
/// <returns></returns>
public string GetServerName()
{
string strServerName = "";
string[] connList = connString.Split(';');
for (int index = 0; index < connList.Length; index++)
{
if (connList[index].Contains("Data Source"))
{
strServerName = connList[index].Substring(connList[index].IndexOf('=')+1);
break;
}
}
return strServerName;
}
/// <summary>
/// 获取数据库名称
/// </summary>
/// <returns></returns>
public string GetDBName()
{
string strDBName = "";
string[] connList = connString.Split(';');
for (int index = 0; index < connList.Length; index++)
{
if (connList[index].Contains("Initial Catalog"))
{
strDBName = connList[index].Substring(connList[index].IndexOf('=') + 1);
break;
}
}
return strDBName;
}
/// <summary>
/// 得到连接数据库用户ID
/// </summary>
/// <returns></returns>
public string GetUserID()
{
string strUserID = "";
string[] connList = connString.Split(';');
for (int index = 0; index < connList.Length; index++)
{
if (connList[index].Contains("User ID"))
{
strUserID = connList[index].Substring(connList[index].IndexOf('=') + 1);
break;
}
}
return strUserID;
}
/// <summary>
/// 得到连接数据库密码
/// </summary>
/// <returns></returns>
public string GetPassword()
{
string strPassword = "";
string[] connList = connString.Split(';');
for (int index = 0; index < connList.Length; index++)
{
if (connList[index].Contains("Password"))
{
strPassword = connList[index].Substring(connList[index].IndexOf('=') + 1);
break;
}
}
return strPassword;
}
/// <summary>
/// 取得备份文件名
/// </summary>
/// <returns></returns>
public string GetFileName()
{
string fileName = "";
string year = DateTime.Now.Year.ToString();
string month = DateTime.Now.Month.ToString();
if (Convert.ToInt32(month) < 10)
{
month = "0" + month;
}
string day = DateTime.Now.Day.ToString();
if (Convert.ToInt32(day) < 10)
{
day = "0" + day;
}
string hour = DateTime.Now.Hour.ToString();
if (Convert.ToInt32(hour) < 10)
{
hour = "0" + hour;
}
string minute = DateTime.Now.Minute.ToString();
if (Convert.ToInt32(minute) < 10)
{
minute = "0" + minute;
}
string secode = DateTime.Now.Second.ToString();
if (Convert.ToInt32(secode) < 10)
{
secode = "0" + secode;
}
fileName = GetDBName() + year + month + day + hour + minute + secode + ".bak";
return fileName;
}
}
}
数据库备份
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>数据库备份</title>
<link rel="Stylesheet" href="../CSS/style.css" />
<script type="text/javascript" src="../JS/common.js"></script>
<script type="text/javascript" src="../JS/ajax.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellSpacing="0" cellPadding="0" width="100%" bgColor="#c4d8ed" border="0">
<tbody>
<tr>
<td style="height: 27px"><IMG alt="" src="../images/r_1.gif"></td>
<td width="100%" background="../images/r_0.gif" style="height: 5px"></td>
<td style="height: 27px"><IMG alt="" src="../images/r_2.gif"></td>
</tr>
<tr>
<td ></td>
<td>
<table class="toptable grid" cellspacing="1" cellpadding="1" align="center" border="1">
<tbody>
<tr>
<td class="category" colspan="3">数据库备份</td>
</tr>
<tr>
<td colspan="3" class="tdControl" style="height:24px; border:0;">
<div id="divMsg" runat="server" style="color:Red;"></div>
</td>
</tr>
<tr>
<td colspan="3" class="tdControl">
请指定用于备份的目录:(如未指定位置,默认备份至 软件目录/DBBackUp/ )
</td>
</tr>
<tr>
<td class="tdControl" style="text-align:right;">备份目录:</td>
<td colspan="2" class="tdControl">
<asp:Panel ID="Panel1" runat="server">
<asp:TextBox ID="txtPath" runat="server" Width="297px"></asp:TextBox>
<span style="color:Red;">例:C:/DBBack/</span>
</asp:Panel>
</td>
</tr>
<tr>
<td colspan="3" style="padding-left:400px;" class="tdControl">
<asp:Button ID="btnBackUp" runat="server" Text="开始备份" CssClass="btn"
οnclick="btnBackUp_Click" />
</td>
</tr>
</tbody>
</table>
</td>
<td ></td>
</tr>
<tr>
<td ><IMG alt="" src="../images/r_4.gif"></td>
<td></td>
<td><IMG alt="" src="../images/r_3.gif"></td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>
/// <summary>
/// 数据库备份
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnBackUp_Click(object sender, EventArgs e)
{
bakServer bak;
this.divMsg.InnerHtml = "数据正在备份中,请稍后……";
this.btnBackUp.Enabled = false;
try
{
bak = new bakServer();
if (this.txtPath.Text == "")
{
string fileName = Server.MapPath("/DBBackUp/") + bak.GetFileName();
if (bak.BackUPDB(fileName))
{
this.divMsg.InnerHtml = "数据库备份成功。";
this.btnBackUp.Enabled = true;
}
else
{
this.divMsg.InnerHtml = "数据库备份失败。";
this.btnBackUp.Enabled = true;
}
}
else
{
if(txtPath.Text.Trim().Substring(txtPath.Text.Trim().Length-1)!=@"/")
{
this.txtPath.Text = this.txtPath.Text.Trim() + @"/";
}
string fileName = this.txtPath.Text.Trim() + bak.GetFileName();
if (bak.BackUPDB(fileName))
{
this.divMsg.InnerHtml = "数据库备份成功。";
this.btnBackUp.Enabled = true;
}
else
{
this.divMsg.InnerHtml = "数据库备份失败。";
this.btnBackUp.Enabled = true;
}
}
}
catch (Exception ex)
{
Session["errorMsg"] = ex.Message;
Response.Redirect("Error.aspx", false);
}
}
数据库还原
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>数据库还原</title>
<link rel="Stylesheet" href="../CSS/style.css" />
<script type="text/javascript" src="../JS/common.js"></script>
<script type="text/javascript" src="../JS/ajax.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellSpacing="0" cellPadding="0" width="100%" bgColor="#c4d8ed" border="0">
<tbody>
<tr>
<td style="height: 27px"><IMG alt="" src="../images/r_1.gif"></td>
<td width="100%" background="../images/r_0.gif" style="height: 5px"></td>
<td style="height: 27px"><IMG alt="" src="../images/r_2.gif"></td>
</tr>
<tr>
<td ></td>
<td>
<table class="toptable grid" cellspacing="1" cellpadding="1" align="center" border="1">
<tbody>
<tr>
<td class="category" colspan="3">数据库还原</td>
</tr>
<tr>
<td colspan="3" class="tdControl" style="height:24px; border:0;">
<div id="divMsg" runat="server" style="color:Red;"></div>
</td>
</tr>
<tr>
<td colspan="3" class="tdControl">
还原数据库时请关闭所有和该数据库连接的程序!
</td>
</tr>
<tr>
<td colspan="3" class="tdControl">
<asp:RadioButton ID="sysDirectory" runat="server" Text="系统目录" Checked="True"
GroupName="aa" οnclick="selectRadioButton()" />
<asp:RadioButton ID="otherDirectory" runat="server" Text="其他目录"
GroupName="aa" οnclick="selectRadioButton()" />
</td>
</tr>
<tr id="trSys" runat="server">
<td class="tdControl" style="text-align:right; width:200px;">系统目录文件:</td>
<td colspan="2" class="tdControl">
<asp:Panel ID="Panel1" runat="server">
<asp:ListBox ID="DBList" runat="server" Width="285px" Height="108px"></asp:ListBox>
</asp:Panel>
</td>
</tr>
<tr id="trOther" runat="server" style="display:none;">
<td class="tdControl" style="text-align:right; width:200px;">其他目录文件:</td>
<td colspan="2" class="tdControl">
<asp:FileUpload ID="FileUpload1" runat="server" Width="368px" />
</td>
</tr>
<tr>
<td colspan="3" style="padding-left:400px;" class="tdControl">
<asp:Button ID="btnRestore" runat="server" Text="还 原" CssClass="btn"
οnclick="btnRestore_Click" />
</td>
</tr>
</tbody>
</table>
</td>
<td ></td>
</tr>
<tr>
<td ><IMG alt="" src="../images/r_4.gif"></td>
<td></td>
<td><IMG alt="" src="../images/r_3.gif"></td>
</tr>
</tbody>
</table>
</div>
</form>
</body>
</html>
public partial class DBRestore : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetBackUpFile();
}
}
protected void btnRestore_Click(object sender, EventArgs e)
{
bakServer bak;
string strPath = "";
try
{
bak = new bakServer();
if (this.sysDirectory.Checked)
{
if (this.DBList.Items.Count > 0)
{
if (this.DBList.SelectedIndex != -1)
{
this.divMsg.InnerHtml = "数据正在还原中,请稍后……";
this.btnRestore.Enabled = false;
strPath = Server.MapPath("/DBBackUp/") + this.DBList.SelectedItem.Text;
if (bak.RestoreDB(strPath))
{
this.divMsg.InnerHtml = "数据库还原成功。";
this.btnRestore.Enabled = true;
}
else
{
this.divMsg.InnerHtml = "数据库还原失败。";
this.btnRestore.Enabled = true;
}
}
else
{
this.divMsg.InnerHtml = "请选择要还原的数据库文件。";
}
}
else
{
this.divMsg.InnerHtml = "没有可还原的数据库文件。";
this.btnRestore.Enabled = true;
}
}
if (this.otherDirectory.Checked)
{
if (FileUpload1.HasFile)
{
this.divMsg.InnerHtml = "数据正在还原中,请稍后……";
this.btnRestore.Enabled = false;
// 获取要上传文件的名称
string FileName = this.FileUpload1.FileName;
// 获取上传文件的扩展名
string FileExtension = FileName.Substring(FileName.LastIndexOf(".") + 1);
if (FileExtension == "bak")
{
strPath = this.FileUpload1.PostedFile.FileName;
if (bak.RestoreDB(strPath))
{
this.divMsg.InnerHtml = "数据库还原成功。";
this.btnRestore.Enabled = true;
}
else
{
this.divMsg.InnerHtml = "数据库还原失败。";
this.btnRestore.Enabled = true;
}
}
else
{
this.divMsg.InnerHtml = "不是正确的数据库还原文件。";
this.btnRestore.Enabled = true;
}
}
else
{
this.divMsg.InnerHtml = "请选择要还原的数据库文件。";
this.btnRestore.Enabled = true;
}
this.ClientScript.RegisterStartupScript(GetType(), "display", "<script>document.getElementById('trSys').style.display='none';document.getElementById('trOther').style.display='block';</script>");
}
}
catch (Exception ex)
{
Session["errorMsg"] = ex.Message;
Response.Redirect("Error.aspx", false);
}
}
/// <summary>
/// 加载数据备份文件
/// </summary>
private void GetBackUpFile()
{
try
{
DirectoryInfo dir = new DirectoryInfo(Server.MapPath("/DBBackUp/"));
foreach (FileInfo dChild in dir.GetFiles("*.bak"))
{
this.DBList.Items.Add(dChild.Name);
}
}
catch (Exception ex)
{
Session["errorMsg"] = ex.Message;
Response.Redirect("Error.aspx", false);
}
}
}