MSSQL一鍵備份與還原
剛好在學校時，XXX系所為了舉辦研討會時，備份需求而想到的開發方式
主要原因她們說備份都用ghost整個硬碟 !!! 驚訝....成本似乎高了點...
本來想說教她們MSSQL內部提供備份就好...最後他們既然說我們不會用資料庫
果然很有電子機械的風格，那也只好放把他做成一個網頁備份了資料庫了
我把備份放置C槽~去做動作
頁前設計
<body>
<form id="form1" runat="server">
<div>
</div>
<p>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="備份" />
</p>
<br />
<br />
<asp:FileUpload ID="FileUpload2" runat="server" />
<p>
<asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="還原" />
</p>
</form>
</body>
頁後設計
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Threading;
public partial class exceltosql : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
//建立目錄
System.IO.Directory.CreateDirectory(@"C:\Users\joe80075\Desktop\CHI\backup");
SqlConnection connect;
//建立連線字串
string con = ConfigurationManager.ConnectionStrings["DA_DatabaseConnectionString"].ToString();
connect = new SqlConnection(con);
connect.Open();
SqlCommand command;
command = new SqlCommand(@"backup database DA_DATABASE to disk ='C:\Users\joe80075\Desktop\CHI\backup\" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss") + ".bak' with init,stats=10", connect);
//執行
command.ExecuteNonQuery();
connect.Close();
}
catch
{
Response.Write("");
}
}
protected void Button2_Click(object sender, EventArgs e)
{
string connection = ConfigurationManager.ConnectionStrings["masterConnectionString"].ToString();
SqlConnection conn = new SqlConnection(connection);
conn.Open();
//查詢該資料庫內SPID 編號
string sql = "SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='DA_DATABASE'";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr;
ArrayList list = new ArrayList();
try
{
dr = cmd.ExecuteReader();
while (dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for (int i = 0; i < list.Count; i++)
{
string temp = list[i].ToString();
cmd = new SqlCommand("kill " + temp.ToString(), conn);//這邊稍微改了一下而已
try
{
if(int.Parse(temp.ToString())>50) //下面其中的一篇參考有提到user Processes是50之後
{
cmd.ExecuteNonQuery();
}
}
catch (Exception eee)
{
throw eee;
}
}
}
catch (Exception eee)
{
// throw eee;
}
finally
{
conn.Close();
}
string filename = FileUpload2.FileName.ToString();
SqlConnection connect;
//建立連線字串
connect = new SqlConnection(connection);
connect.Open();
{
//這邊參考了Will 保哥 提到卸離之前要下的語法
SqlCommand command = new SqlCommand(@" ALTER DATABASE DA_DATABASE SET SINGLE_USER WITH NO_WAIT ALTER DATABASE DA_DATABASE SET SINGLE_USER restore DATABASE DA_DATABASE from disk ='C:\" + FileUpload2.FileName.ToString() + "' with MOVE 'DA_DATABASE' TO 'c:\\DA_DATABASE.mdf',MOVE 'DA_DATABASE_log' TO 'c:\\DA_DATABASE_log1.ldf',REPLACE ALTER DATABASE DA_DATABASE SET MULTI_USER WITH NO_WAIT ALTER DATABASE DA_DATABASE SET MULTI_USER ", connect);
command.ExecuteNonQuery(); //執行
connect.Close();
}
}
}
參考資料
http://blog.miniasp.com/post/2008/12/How-to-detach-SQL-Server-database-successfully.aspx
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/243fba41-4894-41f4-a1a8-ab7421fa5452/
http://www.csharpwin.com/csharpspace/12625r1116.shtml
大家一起加入blogads 賺零用錢!!