数据库的备份与还原
来源：互联网 发布：苹果mac系统如何更新 编辑：程序博客网 时间：2024/05/21 08:02
是用SMO备份的 记着添加SMO相关的dll引用 备份按钮里调备份函数 还原按钮调还原函数就行了
备份函数
private void RestoreBase() { ServerConnection sConn = new ServerConnection("sqlserver所在机器的Ip", "用户名", “密码”); Server s=new Server(sConn); s.ConnectionContext.Connect(); Restore restore=new Restore(); restore.Database = "数据库名"; restore.ReplaceDatabase = true; restore.Action = RestoreActionType.Database;
//D:\testFullBackUp.bak 备份路径 D盘是SQlserve所在的机器D盘 restore.Devices.Add(new BackupDeviceItem(@"D:\testFullBackUp.bak", DeviceType.File)); restore.SqlRestore(s); }
还原函数：
private void RestoreBase() { ServerConnection sConn = new ServerConnection("sqlserver所在机器的Ip", "用户名", “密码”);
Server s=new Server(sConn); s.ConnectionContext.Connect(); Restore restore=new Restore(); restore.Database = "数据库名"; restore.ReplaceDatabase = true; restore.Action = RestoreActionType.Database;
//D:\testFullBackUp.bak D盘是SQlserve所在的机器D盘 restore.Devices.Add(new BackupDeviceItem(@"D:\testFullBackUp.bak", DeviceType.File)); restore.SqlRestore(s); }ASP.NET数据库备份和还原
<%@ page import="org.jfree.chart.JFreeChart"%><%@ page import="org.jfree.chart.ChartFactory"%>先导入Interop.SQLDMO.dllusing System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;/// <summary>/// DBOperater 的摘要说明/// </summary>public class DBOperater{public DBOperater(){ // // TODO: 在此处添加构造函数逻辑 //} //数据库备份 public static string DbBackup(string dbName,string backupDBName) { SQLDMO.Backup oBackup = new SQLDMO.BackupClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { Object aip = ip; oSQLServer.LoginSecure = false; oSQLServer.Connect("127.0.0.1","sa","sa"); oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; oBackup.Database = dbName; oBackup.Files = @"d:\\aaa\\"+backupDBName+".bak"; oBackup.BackupSetName = backupDBName; oBackup.BackupSetDescription = "数据库备份"; oBackup.Initialize = true; oBackup.SQLBackup(oSQLServer); return "数据库已成功经备份到["+oBackup.Files+"]"; } catch(Exception ex) { throw new Exception("数据库备份失败: "+ex.Message); } finally { oSQLServer.DisConnect(); } } /// /// 数据库恢复 /// public static string DbRestore(string dbName,string backupFile) { SQLDMO.Restore oRestore = new SQLDMO.RestoreClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { oSQLServer.LoginSecure = false; oSQLServer.Connect("192.168.1.110", "new", ""); oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; oRestore.Database = dbName; oRestore.Files = @"d:\\aaa\\"+backupFile; oRestore.FileNumber = 1; oRestore.ReplaceDatabase = true; oRestore.SQLRestore(oSQLServer); return "数据库"+dbName+"已经成功恢复!"; } catch(Exception ex) { throw new Exception("数据库恢复失败: "+ex.Message ); } finally { oSQLServer.DisConnect(); } }}
存储过程 CREATE PROCEDURE sp_KillThread @dbname varchar(20) as begin declare @sql nvarchar(500),@temp varchar(1000) declare @spid int set @sql='declare getspid cursor for select spid from master..sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status <> -1 begin set @temp='kill '+rtrim(@spid) exec(@temp) fetch next from getspid into @spid end close getspid deallocate getspid end GO页面using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } //备份 protected void Button1_Click(object sender, EventArgs e) { try { //string path = this.File1.Value;//备份到... string ret = DBOperater.DbBackup(t_db.Text, t_fileName.Text); this.Label1.Text = ret; } catch (Exception ex) { this.Label1.Text = ex.Message; } } //恢复 protected void Button2_Click(object sender, EventArgs e) { string path = "d:\\aaa\\";//this.File1.Value;//恢复文件路径 //杀死所有访问该数据库的进程 string conStr = "data source=localhost;database=master;user id=sa;password=password"; SqlConnection con = new SqlConnection(conStr); string cmdText = String.Format("EXEC sp_KillThread @dbname='{0}'", t_db.Text); SqlCommand com = new SqlCommand(cmdText, con); try { con.Open(); com.ExecuteNonQuery(); con.Close(); //恢复数据库 string ret = DBOperater.DbRestore(t_db.Text, path); this.Label1.Text = ret; } catch (Exception ex) { con.Close(); this.Label1.Text = ex.Message; } }}/// <summary> 2 /// 通过调用MSSQL的SQLDMO.DLL文件来实现备份数据库 3 /// 1.首先在在项目中引用SQLDMO.DLL文件。 4 /// 2.在引用中的SQLDMO.DLL文件右击-->属性-->设置[嵌入互操作类型]为flash 5 /// </summary> 6 public static void DBBackup() 7 { 8 SQLDMO.Backup dbBackup = new SQLDMO.BackupClass(); 9 SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();10 try11 {12 sqlServer.LoginSecure = false;13 sqlServer.Connect("localhost", "sa", "");14 dbBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;15 dbBackup.Database = "kuang"; //数据库名字16 dbBackup.Files = @"d:\kuang_bak.bak"; //备份位置17 dbBackup.BackupSetName = "kuang"; //名称,在企业管理器里面选择备份的时候也有这个，貌似没用。18 dbBackup.BackupSetDescription = "数据库备份"; //描述,在企业管理器里面选择备份的时候也有这个，貌似没用。19 dbBackup.Initialize = true;20 dbBackup.SQLBackup(sqlServer);21 }22 catch23 {24 throw;25 }26 finally27 {28 sqlServer.DisConnect();29 }30 }
1 /// <summary> 2 /// 通过调用MSSQL的SQLDMO.DLL文件来实现从备份文件恢复到是数据库 3 /// 注：恢复是数据库必须是没有链接的。清楚链接的方法：在企业管理器右击数据库-->分离数据库-->点击清楚按钮即可 4 /// </summary> 5 private void DBReply() 6 { 7 SQLDMO.Restore restore = new SQLDMO.RestoreClass(); 8 SQLDMO.SQLServer sqlserver = new SQLDMO.SQLServerClass(); 9 sqlserver.LoginSecure = false;10 sqlserver.Connect("localhost", "sa", "");11 restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;12 restore.Database = "kuang";13 restore.Files = @"d:\kuang_bak.bak";14 restore.FileNumber = 1;15 restore.SQLRestore(sqlserver);16 }http://blog.csdn.net/x276912755/article/details/6087221C#实现SQLSERVER2000数据库备份还原的两种方法: 方法一（不使用SQLDMO）：//////备份方法///SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;");SqlCommand cmdBK = new SqlCommand();cmdBK.CommandType = CommandType.Text;cmdBK.Connection = conn;cmdBK.CommandText = @"backup database test to disk='C:\ba' with init";try{conn.Open();cmdBK.ExecuteNonQuery();MessageBox.Show("Backup successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();conn.Dispose();}//////还原方法///SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");conn.Open();//KILL DataBase ProcessSqlCommandcmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHEREsysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'",conn);SqlDataReader dr;dr = cmd.ExecuteReader();ArrayList list = new ArrayList();while(dr.Read()){list.Add(dr.GetInt16(0));}dr.Close();for(int i = 0; i < list.Count; i++){cmd = new SqlCommand(string.Format("KILL {0}", list), conn);cmd.ExecuteNonQuery();}SqlCommand cmdRT = new SqlCommand();cmdRT.CommandType = CommandType.Text;cmdRT.Connection = conn;cmdRT.CommandText = @"restore database test from disk='C:\ba'";try{cmdRT.ExecuteNonQuery();MessageBox.Show("Restore successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();}方法二（使用SQLDMO）：//////备份方法///SQLDMO.Backup backup = new SQLDMO.BackupClass();SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();//显示进度条SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);backup.PercentComplete += progress;try{server.LoginSecure = false;server.Connect(".", "sa", "sa");backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;backup.Database = "test";backup.Files = @"D:\test\myProg\backupTest";backup.BackupSetName = "test";backup.BackupSetDescription = "Backup the database of test";backup.Initialize = true;backup.SQLBackup(server);MessageBox.Show("Backup successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{server.DisConnect();}this.pbDB.Value = 0;//////还原方法///SQLDMO.Restore restore = new SQLDMO.RestoreClass();SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();//显示进度条SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);restore.PercentComplete += progress;//KILL DataBase ProcessSqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");conn.Open();SqlCommandcmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHEREsysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='test'",conn);SqlDataReader dr;dr = cmd.ExecuteReader();ArrayList list = new ArrayList();while(dr.Read()){list.Add(dr.GetInt16(0));}dr.Close();for(int i = 0; i < list.Count; i++){cmd = new SqlCommand(string.Format("KILL {0}", list), conn);cmd.ExecuteNonQuery();}conn.Close();try{server.LoginSecure = false;server.Connect(".", "sa", "sa");restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;restore.Database = "test";restore.Files = @"D:\test\myProg\backupTest";restore.FileNumber = 1;restore.ReplaceDatabase = true;restore.SQLRestore(server);MessageBox.Show("Restore successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{server.DisConnect();}this.pbDB.Value = 0; 测试通过的proc use master go if object_id('killspid','P') is not null drop proc killspid go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status<>-1 begin exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid end GO public bool killThread(string dbName) { SqlConnection conn= new SqlConnection("server=.;uid=sa;pwd=sa;database=master"); SqlCommand cmd = new SqlCommand("killspid", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@dbname", dbName); try { conn.Open(); cmd.ExecuteNonQuery(); return true; } catch (Exception ex) { return false; } finally { conn.Close(); } } /// <summary> /// 备份数据库 /// </summary> /// <param name="filepath"></param> public static string DBBackup(string filepath,string dbName,string backupDBName) { SQLDMO.Backup oBackup = new SQLDMO.BackupClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { oSQLServer.LoginSecure = false; oSQLServer.Connect(".", "sa", "sa"); oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; oBackup.Database = dbName; oBackup.Files = @"d:\" + backupDBName + ".bak"; oBackup.BackupSetName = backupDBName; oBackup.BackupSetDescription = "数据库备份"; oBackup.Initialize = true; oBackup.SQLBackup(oSQLServer); return "数据库已经成功备份到服务器[" + oBackup.Files + "]"; } catch (Exception ex) { throw new Exception("数据库备份失败: " + ex.Message); } finally { oSQLServer.DisConnect(); } } /// <summary> /// 还原数据库 /// </summary> /// <param name="filepath"></param> public static string DBReply(string filepath,string dbName,string backupFile) { SQLDMO.Restore oRestore = new SQLDMO.RestoreClass(); SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass(); try { oSQLServer.LoginSecure = false; oSQLServer.Connect(".", "sa", "sa"); oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database; oRestore.Database = dbName; oRestore.Files = @"d:\" + backupFile + ".bak"; oRestore.FileNumber = 1; oRestore.ReplaceDatabase = true; oRestore.SQLRestore(oSQLServer); return "数据库" + dbName + "已经成功恢复!"; } catch (Exception ex) { throw new Exception("数据库恢复失败: " + ex.Message); } finally { oSQLServer.DisConnect(); } }
- 数据库的备份与还原:
- 【数据库的备份与还原】
- 数据库的备份与还原
- 【数据库的备份与还原】 .
- 数据库的备份与还原
- 数据库的备份与还原
- 数据库的备份与还原
- 数据库的备份与还原
- 数据库的备份与还原
- 数据库的备份与还原
- 数据库的备份与还原
- 数据库的备份与还原
- 备份与还原数据库
- 数据库备份与还原
- 数据库备份与还原
- 数据库备份与还原
- 数据库备份与还原
- 数据库备份与还原
- C#方法重载（overload）
- perf使用1
- hibernate知识文档
- sql server 2005更改数据的登录用户名与访问权限
- 《基于C/S模式的android手机与PC机通信系统的开发》项目
- 数据库的备份与还原
- OpenTLD 未完成 - 虎头
- JAVA学习网站
- DSPLink环境
- 技术网站收藏
- 字符串模糊匹配使用递归实现
- Cocos2d-x Application Wizard for Visual Studio User Guide
- 静态库和动态库
- 程序人生-3-一位老科学家的自传
