In a recent project, the server is deployed on a special data center. due to security concerns, we cannot provide FTP services to our developers. Therefore, we have to take a look at each version update, his data center is far away, so I have been wondering if I can develop a system with a maintenance version. If I update the database and code online, I don't have to run it myself, the following attempt is made to restore and back up SQL Server online:
Front-end code:
Copy codeThe Code is as follows:
<% @ 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">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<Table>
<Tr>
<Td style = "width: 100px">
<Span style = "font-size: 9pt"> operational data base </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"> (for example, D: \ beifen) </span>
</Td>
</Tr>
<Tr>
<Td colspan = "3">
<Asp: Button ID = "Button1" runat = "server" Font-Size = "9pt" OnClick = "button#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"> operational data base </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"> operational data base </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 = "Force Restore database"/>
</Td>
</Tr>
</Table>
</Div>
</Form>
</Body>
</Html>
Background:
Copy codeThe Code is as follows:
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); DataBase = 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); DataBase = 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 button#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 input it again! '); Location = 'default. aspx' </script> ");
Return;
}
SqlCommand com = new SqlCommand (SqlStr2, con );
Com. ExecuteNonQuery ();
Response. Write ("<script language = javascript> alert ('data backed up successful! ');' </Script> ");
}
Catch (Exception error)
{
Response. Write (error. Message );
Response. Write ("<script language = javascript> alert ('data backup failed! ') </Script> ");
}
Finally
{
Con. Close ();
}
}
Protected void Button2_Click (object sender, EventArgs e)
{
String path = this. FileUpload1.PostedFile. FileName; // obtain the 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 ('data restored successfully! ');' </Script> ");
}
Catch (Exception error)
{
Response. Write (error. Message );
Response. Write ("<script language = javascript> alert ('data restoration failed! ') </Script> ");
TxtDbName. Text = SqlStr2;
}
Finally
{
Con. Close ();
}
}
/// <Summary>
/// Restore the database. You can choose whether to force restore the database (that is, when others are using the database, the database can still be restored)
/// </Summary>
/// <Param name = "databasename"> name of the database to be restored </param>
/// <Param name = "databasefile"> full path of the backup file with restoration </param>
/// <Param name = "errormessage"> information about database restoration failures </param>
/// <Param name = "forceRestore"> whether to force restore (recovery). If it is TRUE, exec killspid 'database name' ends the process of the database, in this way, the database can be restored </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) // 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 = "restored successfully ";
}
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; // obtain the 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 the test, I basically completed the functions I needed, and the specific optimization was in progress.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
