A recent project because the server is in a special room, because of security considerations, we can not provide FTP services to our developers, so each updated version have to run their own, and his room is very far, so I have been thinking can develop a maintenance version of the system, the database and code for online updates, You don't have to run yourself, so you have the following attempt to restore and back up SQL Server online:
<%@ Page language= "C #" autoeventwireup= "true" codebehind= "SqlDbMgmt.aspx.cs" inherits= "syssourcemgmt.sqldbmgmt"% >
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<title></title>
<body>
<form id= "Form1" runat= "Server" >
<div>
<table>
<tr>
<TD style= "width:100px" >
<span style= "FONT-SIZE:9PT" > Operations Database </span>
</td>
<td>
<asp:dropdownlist id= "DropDownList1" runat= "Server" font-size= "9pt" width= "124px" >
</asp:DropDownList>
<asp:textbox id= "Txtdbname" runat= "Server" ></asp:TextBox>
</td>
<TD style= "width:100px" >
</td>
</tr>
<tr>
<TD style= "width:100px" >
<span style= "font-size:9pt" > Backup name and location </span>
</td>
<TD style= "width:100px" >
<asp:textbox id= "TextBox1" runat= "Server" font-size= "9pt" width= "117px" ></asp:TextBox>
</td>
<TD style= "width:100px" >
<span style= "FONT-SIZE:9PT; Color: #ff3300 "> (e.g. D:\beifen) </span>
</td>
</tr>
<tr>
<TD colspan= "3" >
<asp:button id= "Button1" runat= "Server" font-size= "9pt" onclick= "Button1_Click" text= "Backup Database"/>
</td>
</tr>
</table>
</div>
<div style= "width:100%; height:100px ">
<table>
<tr>
<TD style= "width:100px; height:21px ">
<span style= "FONT-SIZE:9PT" > Operations Database </span>
</td>
<td>
<asp:dropdownlist id= "DropDownList2" runat= "Server" font-size= "9pt" width= "124px" >
</asp:DropDownList>
</td>
<TD style= "width:100px; height:21px ">
</td>
</tr>
<tr>
<TD style= "width:100px" >
<span style= "FONT-SIZE:9PT" > Operations Database </span>
</td>
<TD style= "width:100px" >
<asp:fileupload id= "FileUpload1" runat= "Server" font-size= "9pt" width= "190px"/>
</td>
<TD style= "width:100px" >
</td>
</tr>
<tr>
<TD colspan= "3" >
<asp:button id= "Button2" runat= "Server" font-size= "9pt" onclick= "button2_click" text= "Restore Database"/>
<asp:button id= "Button3" runat= "Server" font-size= "9pt" onclick= "Button3_Click" text= "forced Restore Database"/>
</td>
</tr>
</table>
</div>
</form>
</body>
Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Web;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Data.SqlClient;
Using System.IO;
Using System.Data;
Using System.Diagnostics;
Namespace Syssourcemgmt
{
public partial class SqlDbMgmt:System.Web.UI.Page
{
protected void Page_Load (object sender, EventArgs e)
{
if (! IsPostBack)
{
Try
{
String SqlStr1 = "server= (local);D atabase=master; Uid=sa; Pwd= ";
String SqlStr2 = "Exec sp_helpdb";
SqlConnection con = new SqlConnection (SQLSTR1);
Con. Open ();
SqlCommand com = new SqlCommand (SqlStr2, con);
SqlDataReader dr = com. ExecuteReader ();
This. Dropdownlist1.datasource = Dr;
This. Dropdownlist1.datatextfield = "name";
This. Dropdownlist1.databind ();
Dr. Close ();
Con. Close ();
SQLSTR1 = "server= (local);D atabase=master; Uid=sa; Pwd= ";
SQLSTR2 = "Exec sp_helpdb";
con = new SqlConnection (SQLSTR1);
Con. Open ();
com = new SqlCommand (SqlStr2, con);
Dr = Com. ExecuteReader ();
This. Dropdownlist1.datasource = Dr;
This. Dropdownlist1.datatextfield = "name";
This. Dropdownlist1.databind ();
Dr. Close ();
Con. Close ();
}
catch (Exception)
{
}
}
}
protected void Button1_Click (object sender, EventArgs e)
{
String dbname = String. Empty;
if (DropDownList1.Items.Count!= 0)
{
dbname = DropDownList1.SelectedValue.Trim ();
}
Else
{
dbname = TxtDbName.Text.Trim ();
}
String SqlStr1 = "Data source=.\\sqlexpress;initial catalog= '" + dbname + "'; integrated security=true";
String SqlStr2 = "Backup Database" + dbname + "to disk= '" + this. TextBox1.Text.Trim () + ". Bak '";
SqlConnection con = new SqlConnection (SQLSTR1);
Con. Open ();
Try
{
If File.exists (this. TextBox1.Text.Trim ()))
{
Response.Write (' <script language=javascript>alert (' This file already exists, please enter it again! '); location= ' default.aspx ' </script> ');
Return
}
SqlCommand com = new SqlCommand (SqlStr2, con);
Com. ExecuteNonQuery ();
Response.Write (' <script language=javascript>alert (' Backup data Successful! ');' </script> ");
}
catch (Exception error)
{
Response.Write (Error. message);
Response.Write (' <script language=javascript>alert (' Backup data failed! ') </script> ");
}
Finally
{
Con. Close ();
}
}
protected void button2_click (object sender, EventArgs e)
{
String path = this. FileUpload1.PostedFile.FileName; Get backup path and database name
String dbname = String. Empty;
if (DropDownList1.Items.Count!= 0)
{
dbname = DropDownList1.SelectedValue.Trim ();
}
Else
{
dbname = TxtDbName.Text.Trim ();
}
String SqlStr1 = "Data source=.\\sqlexpress;initial catalog= '" + dbname + "'; integrated security=true";
String SqlStr2 = @ "Use master Restore Database" + dbname + "from disk= '" + Path + "'";
SqlConnection con = new SqlConnection (SQLSTR1);
Con. Open ();
Try
{
SqlCommand com = new SqlCommand (SqlStr2, con);
Com. ExecuteNonQuery ();
Response.Write (' <script language=javascript>alert (' Restore data success! ');' </script> ");
}
catch (Exception error)
{
Response.Write (Error. message);
Response.Write (' <script Language=javascript>alert (' failed to restore data! ') </script> ");
Txtdbname.text = SQLSTR2;
}
Finally
{
Con. Close ();
}
}
<summary>
Restore the database, you can choose whether to force restore (that is, when others are in use, can still restore)
</summary>
<param name= "DatabaseName" > database name to be restored </param>
<param name= "Databasefile" > Full path to the restored backup file </param>
<param name= "errormessage" > Recover database failed information </param>
<param name= "Forcerestore" > whether to force a restore (restore), if true, exec killspid ' database name ' ends the process of this database in order to restore the database </param>
<returns></returns>
public bool Restoredatabase (string databasename, String databasefile, ref string returnmessage, bool Forcerestore, Sqlcon Nection conn)
{
BOOL success = TRUE;
string path = Databasefile;
String dbname = DatabaseName;
String restoresql = "Use master;";
if (Forcerestore)//if forced reply
Restoresql = = String. Format ("Use master exec killspid ' {0} ';", databasename);
Restoresql + + "RESTORE Database @dbname from disk = @path;";
SqlCommand mycommand = new SqlCommand (RESTORESQL, conn);
MYCOMMAND.PARAMETERS.ADD ("@dbname", SqlDbType.Char);
mycommand.parameters["@dbname"]. Value = dbname;
MYCOMMAND.PARAMETERS.ADD ("@path", SqlDbType.Char);
mycommand.parameters["@path"]. Value = path;
Response.Write (Restoresql);
Try
{
MyCommand.Connection.Open ();
Mycommand.executenonquery ();
ReturnMessage = "Restore succeeded";
}
catch (Exception ex)
{
ReturnMessage = ex. message;
Success = false;
}
Finally
{
MyCommand.Connection.Close ();
}
return success;
}
protected void Button3_Click (object sender, EventArgs e)
{
String path = this. FileUpload1.PostedFile.FileName; Get backup path and database name
String dbname = String. Empty;
if (DropDownList1.Items.Count!= 0)
{
dbname = DropDownList1.SelectedValue.Trim ();
}
Else
{
dbname = TxtDbName.Text.Trim ();
}
String returnmessage = String. Empty;
String SqlStr1 = "Data source=.\\sqlexpress;initial catalog= '" + dbname + "'; integrated security=true";
SqlConnection con = new SqlConnection (SQLSTR1);
Restoredatabase (txtdbname.text, path, ref returnmessage, True,con);
Response.Write (ReturnMessage);
}
}
}
After testing, the general completion of the functions I need, specific optimization in the late stage.
