在网站上备份数据库
1.前台页面
1 <div> 2 需要备份的文件名：<asp:TextBox ID="bfFile" runat="server" Text="shuJuKu"></asp:TextBox> 3 <asp:Button ID="btnBak" runat="server" Text="备份" OnClick="btnBak_Click" /> 4 <asp:Button ID="btnReduct" runat="server" Text="恢复" OnClick="btnReduct_Click" /> 5 <asp:Button ID="btnDelete" runat="server" Text="删除备份文件" OnClick="btnDelete_Click" /> 6 <asp:Button ID="btnDown" runat="server" Text="下载备份文件" OnClick="btnDown_Click" /> 7 </div>
2.后台代码
1 //删除时用到的库 2 [DllImport("kernel32.dll")] 3 public static extern IntPtr _lopen(string lpPathName, int iReadWrite); 4 5 [DllImport("kernel32.dll")] 6 public static extern bool CloseHandle(IntPtr hObject); 7 8 public const int OF_READWRITE = 2; 9 public const int OF_SHARE_DENY_NONE = 0x40; 10 public readonly IntPtr HFILE_ERROR = new IntPtr(-1);
1 protected void btnBak_Click(object sender, EventArgs e) 2 { 3 /// 4 ///备份方法 5 /// 6 SqlConnection conn = new SqlConnection("Server=数据库ip地址;Database=要备份的数据库;User ID=用户名;Password=密码;");//连接数据库语句 7 8 SqlCommand cmdBK = new SqlCommand(); 9 cmdBK.CommandType = CommandType.Text; 10 cmdBK.Connection = conn; 11 cmdBK.CommandText = @"backup database ccd to disk='"+this.bfFile.Text+".bak' with init";//数据备份语句：backup database 数据库名 to disk='保存路径dbName.bak' 12 13 try 14 { 15 conn.Open(); 16 cmdBK.ExecuteNonQuery(); 17 Response.Write("<script>alert("恭喜你，数据成功恢复为所选文档的状态！");</script>"); 18 } 19 catch (Exception ex) 20 { 21 Response.Write("<script>alert("" + ex.Message + "");</script>"); 22 } 23 finally 24 { 25 conn.Close(); 26 conn.Dispose(); 27 } 28 }
1 protected void btnDown_Click(object sender, EventArgs e) 2 { 3 4 //下载的文件和网页的是在同一服务器上 5 6 string fileName = "aaa.bak";//客户端保存的文件名 7 string filePath = Server.MapPath("aaa.bak");//路径 8 9 //以字符流的形式下载文件 10 FileStream fs = new FileStream(filePath, FileMode.Open); 11 byte[] bytes = new byte[(int)fs.Length]; 12 fs.Read(bytes, 0, bytes.Length); 13 fs.Close(); 14 Response.ContentType = "application/octet-stream"; 15 //通知浏览器下载文件而不是打开 16 Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); 17 Response.BinaryWrite(bytes); 18 Response.Flush(); 19 Response.End(); 20 }
1 protected void btnReduct_Click(object sender, EventArgs e) 2 { 3 /// 4 ///还原方法 5 /// 6 7 ///杀死原来所有的数据库连接进程 8 SqlConnection conn = new SqlConnection(); 9 conn.ConnectionString = "Data Source=数据库的IP地址;Initial Catalog=master;User ID=用户名;pwd=密码"; 10 conn.Open(); 11 string sql = "use [master] SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='要备份的数据库'"; 12 SqlCommand cmd1 = new SqlCommand(sql, conn); 13 SqlDataReader dr; 14 ArrayList list = new ArrayList(); 15 try 16 { 17 dr = cmd1.ExecuteReader(); 18 while (dr.Read()) 19 { 20 list.Add(dr.GetInt16(0)); 21 } 22 dr.Close(); 23 } 24 catch (SqlException eee) 25 { 26 Response.Write("<script>alert(""+eee.ToString()+"");</script>"); 27 } 28 finally 29 { 30 conn.Close(); 31 } 32 //MessageBox.Show(list.Count.ToString()); 33 for (int i = 0; i < list.Count; i++) 34 { 35 conn.Open(); 36 cmd1 = new SqlCommand(string.Format("KILL {0}", list[i].ToString()), conn); 37 cmd1.ExecuteNonQuery(); 38 conn.Close(); 39 Response.Write("<script>alert("系统已经清除的数据库线程： " + list[i].ToString() + " 正在还原数据库！"); 40 } 41 //这里一定要是master数据库，而不能是要还原的数据库，因为这样便变成了有其它进程 42 //占用了数据库。 43 string constr = @"Data Source=数据库的IP地址;Initial Catalog=master;User ID=用户名;pwd=密码"; 44 string database = "myNewDb"; 45 //string path = "D:\shuJuKu.bak"; 46 47 48 string path = "" + bfFile.Text + ".bak"; 49 50 string BACKUP = String.Format("RESTORE DATABASE {0} FROM DISK = '{1}' WITH MOVE ", database, path);//数据恢复语句：restore database 数据库名 from disk='保存路径dbName.bak' WITH MOVE 51 52 SqlConnection con = new SqlConnection(constr); 53 con.Open(); 54 55 //string sql2 = "select name from sys.database_files"; 56 string sql2 = "restore filelistonly from disk='" + path + "'"; 57 SqlCommand cmd2 = new SqlCommand(sql2, con); 58 SqlDataReader dr2 = cmd2.ExecuteReader(); 59 string str1 = ""; 60 string str2 = ""; 61 while(dr2.Read()) 62 { 63 if (dr2["PhysicalName"].ToString().Contains("mdf")) 64 { 65 str1 = dr2["LogicalName"].ToString(); 66 } 67 else if (dr2["PhysicalName"].ToString().Contains("ldf")) 68 { 69 str2 = dr2["LogicalName"].ToString(); 70 } 71 } 72 dr2.Close(); 73 74 BACKUP += "'"+str1+"' TO 'd:\sjk.mdf',";///------数据文件还原后存放的新位置 75 BACKUP += "MOVE '"+str2+"' TO 'd:\sjk.ldf'";//----日志文件还原后存放的新位置 76 77 78 //BACKUP += "'" + str1 + "' TO 'd:\shujuku.mdf',";///------数据文件还原后存放的新位置 79 //BACKUP += "MOVE '" + str2 + "' TO 'd:\shujuku.ldf'";//----日志文件还原后存放的新位置 80 81 SqlCommand cmd = new SqlCommand(BACKUP, con); 82 83 try 84 { 85 cmd.ExecuteNonQuery(); 86 Response.Write("<script>alert("还原成功,点击退出系统！");</script>"); 87 //Application.Exit(); 88 conn.Close(); 89 conn.Dispose(); 90 } 91 catch (SqlException ee) 92 { 93 94 Response.Write("<script>alert(""+ee.ToString()+"");</script>"); 95 96 } 97 finally 98 { 99 con.Close(); 100 } 101 }
1 protected void btnDelete_Click(object sender, EventArgs e) 2 { 3 4 //不能在服务器上删除，只能手动删除 5 6 /// 7 ///删除方法 8 /// 9 //本地的文件删除,网页和服务器在同一ip下，删除的文件路径需要完整的文件路径 10 11 string vFileName = @"D:\aaa.bak"; 12 if (!File.Exists(vFileName)) 13 {//判断文件是否存在 14 Response.Write("<script>alert("文件都不存在，你就不要拿来耍了");</script>"); 15 return; 16 } 17 IntPtr vHandle = _lopen(vFileName, OF_READWRITE | OF_SHARE_DENY_NONE); 18 if (vHandle == HFILE_ERROR) 19 {//判断文件是否被占用 20 Response.Write("<script>alert("文件被占用！");</script>"); 21 return; 22 } 23 else 24 { 25 CloseHandle(vHandle); 26 Response.Write("<script>alert("没有被占用！");</script>"); 27 28 29 FileInfo file = new FileInfo("D:\aaa.bak"); 30 file.Delete(); 31 32 } 33 }
我只是实现了在服务器上的备份和还原数据库，网页和数据库在同一服务器上下载文件