最近做的一个项目因为服务器是在特殊机房上的因为安全方面的考虑不能给我们开发者提供FTP服务所以每次更新版本都得自己跑一趟而他的机房有很远所以我一直想能不能开发一个维护版本的系统呢对数据库和代码进行在线更新就不用自己跑了于是就有了下面的尝试在线恢复和备份SQL Server
前台代码
<%@ Page Language=C# AutoEventWireup=true CodeBehind=SqlDbMgmtaspxcs Inherits=SysSourceMgmtSqlDbMgmt %>
<!DOCTYPE html PUBLIC //WC//DTD XHTML Transitional//EN <transitionaldtd>>
<html xmlns=<;>
<head runat=server>
<title></title>
</head>
<body>
<form id=form runat=server>
<div>
<table>
<tr>
<td >
<span >操 作 数 据 库</span>
</td>
<td>
<asp:DropDownList ID=DropDownList runat=server FontSize=pt Width=px>
</asp:DropDownList>
<asp:TextBox ID=txtDbName runat=server></asp:TextBox>
</td>
<td >
</td>
</tr>
<tr>
<td >
<span >备份名称和位置</span>
</td>
<td >
<asp:TextBox ID=TextBox runat=server FontSize=pt Width=px></asp:TextBox>
</td>
<td >
<span >（如D:\beifen）</span>
</td>
</tr>
<tr>
<td colspan=>
<asp:Button ID=Button runat=server FontSize=pt OnClick=Button_Click Text=备份数据库 />
</td>
</tr>
</table>
</div>
<div >
<table>
<tr>
<td >
<span >操 作 数 据 库</span>
</td>
<td>
<asp:DropDownList ID=DropDownList runat=server FontSize=pt Width=px>
</asp:DropDownList>
</td>
<td >
</td>
</tr>
<tr>
<td >
<span >操 作 数 据 库</span>
</td>
<td >
<asp:FileUpload ID=FileUpload runat=server FontSize=pt Width=px />
</td>
<td >
</td>
</tr>
<tr>
<td colspan=>
<asp:Button ID=Button runat=server FontSize=pt OnClick=Button_Click Text=还原数据库 />
<asp:Button ID=Button runat=server FontSize=pt OnClick=Button_Click Text=强制还原数据库 />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
后台
using System;
using SystemCollectionsGeneric;
using SystemLinq;
using SystemWeb;
using SystemWebUI;
using SystemWebUIWebControls;
using SystemDataSqlClient;
using SystemIO;
using SystemData;
using SystemDiagnostics;
namespace SysSourceMgmt
{
public partial class SqlDbMgmt : SystemWebUIPage
{
protected void Page_Load(object sender EventArgs e)
{
if (!IsPostBack)
{
try
{
string SqlStr = Server=(local);DataBase=master;Uid=sa;Pwd=;
string SqlStr = Exec sp_helpdb;
SqlConnection con = new SqlConnection(SqlStr);
conOpen();
SqlCommand com = new SqlCommand(SqlStr con);
SqlDataReader dr = comExecuteReader();
thisDropDownListDataSource = dr;
thisDropDownListDataTextField = name;
thisDropDownListDataBind();
drClose();
conClose();
SqlStr = Server=(local);DataBase=master;Uid=sa;Pwd=;
SqlStr = Exec sp_helpdb;
con = new SqlConnection(SqlStr);
conOpen();
com = new SqlCommand(SqlStr con);
dr = comExecuteReader();
thisDropDownListDataSource = dr;
thisDropDownListDataTextField = name;
thisDropDownListDataBind();
drClose();
conClose();
}
catch (Exception)
{
}
}
}
protected void Button_Click(object sender EventArgs e)
{
string dbName = stringEmpty;
if (DropDownListItemsCount != )
{
dbName = DropDownListSelectedValueTrim();
}
else
{
dbName = txtDbNameTextTrim();
}
string SqlStr = Data Source=\\sqlexpress;Initial Catalog= + dbName + ;Integrated Security=True;
string SqlStr = backup database + dbName + to disk= + thisTextBoxTextTrim() + bak;
SqlConnection con = new SqlConnection(SqlStr);
conOpen();
try
{
if (FileExists(thisTextBoxTextTrim()))
{
ResponseWrite(<script language=javascript>alert(此文件已存在请从新输入！);location=Defaultaspx</script>);
return;
}
SqlCommand com = new SqlCommand(SqlStr con);
comExecuteNonQuery();
ResponseWrite(<script language=javascript>alert(备份数据成功！);</script>);
}
catch (Exception error)
{
ResponseWrite(errorMessage);
ResponseWrite(<script language=javascript>alert(备份数据失败！)</script>);
}
finally
{
conClose();
}
}
protected void Button_Click(object sender EventArgs e)
{
string path = thisFileUploadPostedFileFileName; //获得备份路径及数据库名称
string dbName = stringEmpty;
if (DropDownListItemsCount != )
{
dbName = DropDownListSelectedValueTrim();
}
else
{
dbName = txtDbNameTextTrim();
}
string SqlStr = Data Source=\\sqlexpress;Initial Catalog= + dbName + ;Integrated Security=True;
string SqlStr = @use master restore database + dbName + from disk= + path + ;
SqlConnection con = new SqlConnection(SqlStr);
conOpen();
try
{
SqlCommand com = new SqlCommand(SqlStr con);
comExecuteNonQuery();
ResponseWrite(<script language=javascript>alert(还原数据成功！);</script>);
}
catch (Exception error)
{
ResponseWrite(errorMessage);
ResponseWrite(<script language=javascript>alert(还原数据失败！)</script>);
txtDbNameText = SqlStr;
}
finally
{
conClose();
}
}
/// <summary>
/// 恢复数据库可选择是否可以强制还原（即在其他人在用的时候依然可以还原）
/// </summary>
/// <param name=databasename>待还原的数据库名称</param>
/// <param name=databasefile>带还原的备份文件的完全路径</param>
/// <param name=errormessage>恢复数据库失败的信息</param>
/// <param name=forceRestore>是否强制还原（恢复）如果为TRUE则exec killspid 数据库名 结束此数据库的进程这样才能还原数据库</param>
/// <returns></returns>
public bool RestoreDataBase(string databasename string databasefile ref string returnMessage bool forceRestore SqlConnection conn)
{
bool success = true;
string path = databasefile;
string dbname = databasename;
string restoreSql = use master;;
if (forceRestore)//如果强制回复
restoreSql += stringFormat(use master exec killspid {}; databasename);
restoreSql += restore database @dbname from disk = @path;;
SqlCommand myCommand = new SqlCommand(restoreSql conn);
myCommandParametersAdd(@dbname SqlDbTypeChar);
myCommandParameters[@dbname]Value = dbname;
myCommandParametersAdd(@path SqlDbTypeChar);
myCommandParameters[@path]Value = path;
ResponseWrite(restoreSql);
try
{
myCommandConnectionOpen();
myCommandExecuteNonQuery();
returnMessage = 还原成功;
}
catch (Exception ex)
{
returnMessage = exMessage;
success = false;
}
finally
{
myCommandConnectionClose();
}
return success;
}
protected void Button_Click(object sender EventArgs e)
{
string path = thisFileUploadPostedFileFileName; //获得备份路径及数据库名称
string dbName = stringEmpty;
if (DropDownListItemsCount != )
{
dbName = DropDownListSelectedValueTrim();
}
else
{
dbName = txtDbNameTextTrim();
}
string returnMessage = stringEmpty;
string SqlStr = Data Source=\\sqlexpress;Initial Catalog= + dbName + ;Integrated Security=True;
SqlConnection con = new SqlConnection(SqlStr);
RestoreDataBase(txtDbNameText path ref returnMessage truecon);
ResponseWrite(returnMessage);
}
}
}
