Asp.Net 备份和恢复SQL SERVER 数据库 . ASP.NET数据库备份和还原 先导入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 页面 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) {
ASP.NET数据库备份和还原
先导入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
页面
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;
}
}
}
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "USE master; EXEC sp_dboption '" + DataBaseName + "', 'offline', 'TRUE'";
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
Conn.Close();
}
}
}
} //分离数据库
public void detach()
{
try
{
DataBaseHelper dbh = new DataBaseHelper();
dbh.C;
dbh.DataBaseName = "DBName";
dbh.DetachDataBase();
}
catch (Exception ex)
{
throw (ex);
}
} // 附加数据库
public void appenddb()
{
try
{
DataBaseHelper dbh = new DataBaseHelper();
dbh.C;
dbh.DataBaseName = "DBName";
dbh.DataBase_MDF_Address = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName.MDF"; //mdf文件放置的路径.
dbh.DataBase_LDF_Address = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_Log.LDF";
dbh.AppendDataBase();
}
catch (Exception ex)
{
throw (ex);
}
} //备份数据库
public void backup()
{
try
{
DataBaseHelper dbh = new DataBaseHelper();
dbh.C;
dbh.DataBaseName = "DBName";
dbh.DataBaseOfBackupName = "back.bak";
dbh.DataBaseOfBackupPath = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Back\"; //备份路径
dbh.BackupDataBase();
}
catch (Exception ex)
{
throw (ex);
}
} ///还原数据库
public void restore()
{
try
{
DataBaseHelper dbh = new DataBaseHelper();
dbh.C;
dbh.LogicalDataBaseName = "DBName";
dbh.DataBaseName = "DBName";
dbh.DataBaseOfBackupName = "back.bak";
dbh.DataBase_MDF_Address = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName.MDF";
dbh.DataBase_LDF_Address = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_Log.LDF";
dbh.DataBaseOfBackupPath = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Back\";
dbh.RestoreDataBase();
}
catch (Exception ex)
{
throw (ex);
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
namespace WinLibClass.Data.DataProvider
{
public partial class Sql2000 //: IDataProvider
{
#region 核心组
private static object lockHelper = new object();
private static Sql2000 _Instance = null;
public static Sql2000 GetInstance
{
get
{
if (_Instance == null)
{
lock (lockHelper)
{
if (_Instance == null)
{
_Instance = new Sql2000();
_Instance.Sql2000_Elements_Init();
}
}
}
return _Instance;
} } System.Data.Common.DbConnection _CurrentConnection = null;
System.Data.Common.DbTransaction _Trans = null; public DbConnection CurrentConnection
{
get { return _CurrentConnection; }
//set { _CurrentConnection = value; }
} public DbTransaction CurrentTransaction
{
get { return _Trans; }
}
/// <summary>
/// 创建并打开数据库连接
/// </summary>
public void ConnectionCreate()
{
if (_CurrentConnection == null)
{
_CurrentConnection = DbHelper.Factory.CreateConnection();
_CurrentConnection.ConnectionString = WinLibClass.Data.DbHelper.ConnectionString;
}
if (_CurrentConnection != null && _CurrentConnection.State == ConnectionState.Closed)
{
try
{
if(_CurrentConnection.ConnectionString.Trim().Length<1)
_CurrentConnection.ConnectionString = WinLibClass.Data.DbHelper.ConnectionString; _CurrentConnection.Open();
}
catch (System.Exception se)
{
WinLibClass.Common.Files.FileUtils.WriteLog(se.Message + "[" + _CurrentConnection.ConnectionString + "]");
}
} } /// <summary>
/// 关闭并销毁数据库连接
/// </summary>
public void ConnectionDispose()
{
if (_CurrentConnection != null)
{
_CurrentConnection.Close();
_CurrentConnection.Dispose();
}
}
/// <summary>
/// 判断当前对象是否已打开
/// </summary>
/// <returns></returns>
public bool ConnectionIsOpen()
{
bool _IsOpen = false;
if (_CurrentConnection == null)
{
_IsOpen = false;
}
else
{
_IsOpen = (_CurrentConnection.State == ConnectionState.Open);
}
return _IsOpen;
} /// <summary>
/// 事务创建并启动
/// </summary>
public void TransactionBegin()
{
if (_CurrentConnection.State == ConnectionState.Open)
{
_Trans = _CurrentConnection.BeginTransaction(IsolationLevel.RepeatableRead);//创建事务
} } /// <summary>
/// 事务提交
/// </summary>
public void TransactionCommit()
{
if (_CurrentConnection.State == ConnectionState.Open && _Trans != null)
{
_Trans.Commit();
_Trans.Dispose();
}
}
/// <summary>
/// 事务对象销毁
/// </summary>
public void TransactionDispose()
{
if (_Trans != null)
{
_Trans.Dispose();
}
} /// <summary>
/// 事件回滚
/// </summary>
public void TransactionRollback()
{
if (_Trans != null)
{
_Trans.Rollback();
}
}
#endregion #region 数据库备份 #region 默认路径
/// <summary>
/// 数据库备份
/// </summary>
/// <param name="toFileName">文件名</param>
/// <returns></returns>
///
public int Db_BackupDataBase(string toFileName)
{
if (string.IsNullOrEmpty(toFileName))
{
return 0;
}
toFileName = toFileName.Replace("/", "").Replace("\\", "");
return Db_BackupDataBaseByFullPath(WinLibClass.Config.Global.WebSiteConfig.WebSiteRootPhyPath + WinLibClass.Config.Global.WebSiteConfig.DataBaseDbDir + toFileName + ".dbdat");
} /// <summary>
/// 数据库恢复
/// </summary>
/// <param name="fromFileName">文件名</param>
/// <returns></returns>
public int Db_RestoreDataBase(string fromFileName)
{
if (string.IsNullOrEmpty(fromFileName))
{
return 0;
}
fromFileName = fromFileName.Replace("/", "").Replace("\\", "");
return Db_RestoreDataBaseByFullPath(WinLibClass.Config.Global.WebSiteConfig.WebSiteRootPhyPath + WinLibClass.Config.Global.WebSiteConfig.DataBaseDbDir + fromFileName + ".dbdat"); }
/// <summary>
/// 获取备份文件列表
/// </summary>
/// <param name="fromFileName"></param>
/// <returns></returns>
public DataTable Db_GetBackupFileList(string fromFileName)
{
if (string.IsNullOrEmpty(fromFileName))
{
fromFileName = "";
}
fromFileName = fromFileName.Replace("/", "").Replace("\\", "");
return Db_GetBackupFileListByFullPath(WinLibClass.Config.Global.WebSiteConfig.WebSiteRootPhyPath + WinLibClass.Config.Global.WebSiteConfig.DataBaseDbDir + fromFileName);
} /// <summary>
/// 删除指定备份文件
/// </summary>
/// <param name="fromFileName"></param>
/// <returns></returns>
public int Db_DeleteBackupFile(string fromFileName)
{
if (string.IsNullOrEmpty(fromFileName))
{
return 0;
}
fromFileName = fromFileName.Replace("/", "").Replace("\\", "");
return Db_DeleteBackupFileByFullPath(WinLibClass.Config.Global.WebSiteConfig.WebSiteRootPhyPath + WinLibClass.Config.Global.WebSiteConfig.DataBaseDbDir + fromFileName);
} /// <summary>
/// 批量删除备份文件(慎重删除,防止误删)
/// </summary>
/// <param name="fromFileNames"></param>
/// <returns></returns>
public int Db_DeleteBackupFiles(string fromFileNames)
{
if (string.IsNullOrEmpty(fromFileNames))
{
return 0;
} string[] list = fromFileNames.Split(',');
int n = 0;
for (int i = 0; i < list.Length; i++)
{
n += Db_DeleteBackupFile(list[i]);
}
return n;
}
#endregion #region 全路径
/// <summary>
/// 数据库备份(尽量不用)
/// </summary>
/// <param name="toPath">全路径</param>
/// <returns></returns>
public int Db_BackupDataBaseByFullPath(string toPath)
{ //Common.Utils2.ResponseWrite(toPath);
//Common.Files.FileUtils.WriteLog("dbtxt.txt", "Path =[" + toPath + "] ");
System.IO.FileInfo fInfo = new System.IO.FileInfo(toPath);
if (fInfo.Directory.Exists == false)
{ return 0;//目录不存在
}
//return 0;
string sql = " BACKUP DATABASE "
+ WinLibClass.Config.Global.WebSiteConfig.DataBaseDbName
+ " TO DISK='" + (toPath) + "';";
int n = DbHelper.ExecuteNonQuery(sql);
//Common.Files.FileUtils.WriteLog("dbtxt.txt", "执行结果值:" + n + ";Path =[" + toPath + "] ");
return n;
} /// <summary>
/// 数据库恢复(尽量不用)
/// </summary>
/// <param name="fromPath"></param>
/// <returns></returns>
public int Db_RestoreDataBaseByFullPath(string fromPath)
{
System.IO.FileInfo fInfo = new System.IO.FileInfo(fromPath);
if (fInfo.Exists == false)
{
return 0;//目录不存在
}
string sql = "USE MASTER RESTORE DATABASE "
+ WinLibClass.Config.Global.WebSiteConfig.DataBaseDbName
+ " FROM DISK='" + (fromPath) + "';"; int n = 0; ////Common.Utils2.ResponseWrite("[" + DbHelper.ConnectionString + "]");
//SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
//conn.Open();
//SqlCommand cmd = conn.CreateCommand();
//using (SqlTransaction trans = conn.BeginTransaction())
//{
// cmd.Connection = conn;
// cmd.Transaction = trans; // //DbCommand cmd = DbHelper.Factory.CreateCommand();
// //new SqlCommand(sql, conn); // try
// {
// cmd.CommandType = CommandType.Text;
// cmd.CommandText = sql;
// n = cmd.ExecuteNonQuery();
// //DbHelper.ExecuteNonQuery(sql);
// trans.Commit(); // }
// catch (System.Exception se)
// {
// Common.Utils2.ResponseWrite(se.Message);
// trans.Rollback();
// //
// }
//}
//cmd.Dispose();
//conn.Close(); WinLibClass.Data.DbHelper.IsRestoreSql = 1;
WinLibClass.Data.DbHelper.IsSingleRunning = 1;
n = DbHelper.ExecuteNonQuery(sql); WinLibClass.Data.DbHelper.IsSingleRunning = 0;//去掉独占
WinLibClass.Data.DbHelper.IsRestoreSql = 0;//去掉独占
return n;
} /// <summary>
/// 备份文件列表
/// </summary>
/// <param name="dirPath"></param>
/// <returns></returns>
public DataTable Db_GetBackupFileListByFullPath(string dirPath)
{
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(dirPath);
if (dir.Exists)
{
DataTable dt = new DataTable();
dt.Columns.Add("id", Type.GetType("System.Int32"));
dt.Columns.Add("FullName", Type.GetType("System.String"));
dt.Columns.Add("FileName", Type.GetType("System.String"));
dt.Columns.Add("CreationTime", Type.GetType("System.DateTime")); System.IO.FileInfo[] list = dir.GetFiles("*.dbdat");
for (int i = 0; i < list.Length; i++)
{
DataRow dr = dt.NewRow(); dr["id"] = (i + 1);
dr["FullName"] = list[i].FullName;
dr["FileName"] = list[i].Name;
dr["CreationTime"] = list[i].CreationTime;
dt.Rows.Add(dr);
}
return dt; }
else
{
return null;
}
} public int Db_DeleteBackupFileByFullPath(string fromPath)
{
System.IO.FileInfo f = new System.IO.FileInfo(fromPath);
{
try
{
if (f.Exists)
f.Delete();
return 1;
}
catch (System.Exception se)
{
return 0;
}
} }
#endregion
#endregion
}
}
仅供参考