备份函数
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.dll 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; /// <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 |
/// <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 try 11 { 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 catch 23 { 24 throw; 25 } 26 finally 27 { 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/6087221 C#实现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 Process 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(); } 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 Process SqlConnection 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(); } }