asp.net 数据库备份与还原

最近在替学校做网站,今天刚大致做好了数据库备份与还原功能,因为网站图片是存放在文件下,所以备份数据库时,图片文件夹要一同考拷贝.具体如下:

DbOper类:

using System.IO;
///
///
public sealed class DbOper
{
///
/// DbOper类的构造函数
///
private DbOper()
{

}
/// <summary>
/// 复制文件
/// </summary>
/// <param name="from"> 要复制文件的源地址 </param>
/// <param name="to"> 要复制到的路径 </param>
public static void copyfolder( string from, string to)
{
string fname = from.Substring(from.LastIndexOf( " \\ " ) + 1 );
if (Directory.Exists(from))
{
Directory.CreateDirectory(from);
}
DirectoryInfo di = new DirectoryInfo(from);
if ( ! Directory.Exists(to + " \\ " + di.Name))
{
Directory.CreateDirectory(to + " \\ " + di.Name);
}
string [] fi = Directory.GetFiles(di.FullName);
for ( int i = 0 ; i < fi.Length; i ++ )
{
string f_name = fi[i].Substring(fi[i].LastIndexOf( " \\ " ) + 1 );
File.Copy(from + " \\ " + f_name, to + " \\ " + fname + " \\ " + f_name, true );
}
DirectoryInfo[] dis = di.GetDirectories();
for ( int j = 0 ; j < dis.Length; j ++ )
{
copyfolder(dis[j].FullName.ToString(), to + " \\ " + fname);
}
}
/// <summary>
/// 数据库备份
/// </summary>
/// <param name="pathfilename"> 备份的路径 </param>
/// <returns> 返回结果信息 </returns>
public static string DbBackup( string pathfilename)
{
string ret;
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = true ; // 验证模式，FALSE 是sql server验证模式 True 是混合验证模式
oSQLServer.Connect( " . " , "" , "" ); // 联接数据服务
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database; // 恢复类型数据库
oBackup.Database = " TzcAlumni " ; // 数据库名称
oBackup.Files = @pathfilename; // 备份文件名,如果是多个设备上的需使用.devices属性
oBackup.BackupSetName = " TzcAlumni " ; // 要备份的数据库
oBackup.BackupSetDescription = " 数据库备份 " ; // 描述说明
oBackup.Initialize = true ; // 表示是追加备份还是重新备份，原来如果有同名的备份是否覆盖它
oBackup.SQLBackup(oSQLServer);
ret = " 备份成功 " ;
}
catch (Exception x)
{
ret = " 备份失败- " + x.ToString();
}
finally
{
oSQLServer.DisConnect();
}
return ret;
}

/// <summary>
/// 数据库恢复
/// </summary>
/// <param name="pathfilename"> 备份数据所在的路径 </param>
/// <returns> 返回结果信息 </returns>
public static string DbRestore( string pathfilename)
{
string ret;
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = true ;
oSQLServer.Connect( " . " , "" , "" );
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = " TzcAlumni " ;
oRestore.Files = @pathfilename;
oRestore.FileNumber = 1 ; // 文件在设备上的ID号如果你只有一个文件就是1
oRestore.ReplaceDatabase = true ; // 替代现有数据库如不存则创建他
oRestore.SQLRestore(oSQLServer); // 调用恢复方法
ret = " 成功恢复 " ;
}
catch (Exception x)
{
ret = " 恢复失败- " + x.ToString();
}
finally
{
oSQLServer.DisConnect();
}
return ret;
}
}

前台:

< asp:UpdatePanel ID = " UpdatePanel1 " runat = " server " >
< ContentTemplate >
< div style = " margin: 20px 20px 20px 20px; " >
< asp:Button ID = " Button1 " runat = " server " Text = " 备份数据库 " OnClick = " Button1_Click " />
< asp:GridView ID = " GridView1 " runat = " server " OnRowCommand = " GridView1_RowCommand " >
< Columns >
< asp:TemplateField ShowHeader = " False " >
< ItemTemplate >
< asp:LinkButton ID = " LinkButton2 " runat = " server " CausesValidation = " false " CommandArgument = " <%#Container.DataItem%> "
CommandName = " recover " Text = " &lt;div οnclick=&quot;JavaScript:return confirm('确定恢复该数据库备份吗？')&quot;&gt;还还数据库&lt;/div&gt; " ></ asp:LinkButton >
</ ItemTemplate >
< ItemStyle CssClass = " trow " Width = " 60px " />
</ asp:TemplateField >
< asp:TemplateField ShowHeader = " False " >
< ItemTemplate >
< asp:LinkButton ID = " LinkButton1 " runat = " server " CausesValidation = " false " CommandArgument = " <%#Container.DataItem%> "
CommandName = " deleted " Text = " &lt;div οnclick=&quot;JavaScript:return confirm('确定删除该数据库备份吗？')&quot;&gt;删除&lt;/div&gt; " >< / asp:LinkButton >
</ ItemTemplate >
< ItemStyle CssClass = " trow " Width = " 40px " />
</ asp:TemplateField >
</ Columns >
</ asp:GridView >
< asp:Label ID = " Label2 " runat = " server " ></ asp:Label >
</ div >
</ ContentTemplate >
</ asp:UpdatePanel >
< asp:UpdateProgress ID = " UpdateProgress1 " runat = " server " >
< ProgressTemplate >
< img src = " Images/等待.gif " />
< br />
< asp:Label ID = " Label1 " runat = " server " Text = " 正在执行相关操作，请稍后... " ></ asp:Label >
</ ProgressTemplate >
</ asp:UpdateProgress >

后台:

protected void Page_Load( object sender, EventArgs e)
{
BindData();
}
private void BindData()
{
string [] path = Directory.GetDirectories(@Server.MapPath( " Date/DbBackup/ " )); // 路径
ArrayList str = new ArrayList();
foreach ( string fname in path)
{
str.Add(fname.Substring(Server.MapPath( " Date/DbBackup/ " ).Length));
}
GridView1.DataSource = str;
GridView1.DataBind();
}
protected void Button1_Click( object sender, EventArgs e)
{
try
{
string strpath = Server.MapPath( " Date/DbBackup/ " ) + System.DateTime.Now.ToString().Replace( " : " , "" ).Replace( " " , "" );
Directory.CreateDirectory(strpath); // 创建一个新的文件夹
string path = " [ " + strpath + " /TzcAlumni " + " .bak] " ; // 备份路径及文件名
DbOper.DbBackup(path); // 备份数库库
DbOper.copyfolder(Server.MapPath( " ../Class/ClassPhotos " ), strpath); // 复制照片文件夹
((Label)UpdateProgress1.FindControl( " Label1 " )).Text = " 备份成功 " ;
BindData();
CMessageBox.ShowAjaxDialog(UpdatePanel1, " 备份成功 " );
}
catch (Exception x)
{
((Label)UpdateProgress1.FindControl( " Label1 " )).Text = " 备份失败 " ;
CMessageBox.ShowAjaxDialog(UpdatePanel1, " 备份失败- " + x.ToString());
}
}
protected void GridView1_RowCommand( object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.ToString() == " deleted " )
{
string AdminID = Session[ " AdminID " ].ToString();
Directory.Delete(Server.MapPath( " Date/DbBackup/ " ) + e.CommandArgument.ToString(), true ); // 删除所选的备份文件夹
BindData();
CMessageBox.ShowAjaxDialog(UpdatePanel1, " 删除成功 " );
Session[ " AdminID " ] = AdminID;
return ;
}
if (e.CommandName.ToString() == " recover " )
{

try
{
KillSpidDataClassesDataContext kdb = new KillSpidDataClassesDataContext();
kdb.killspid( " TzcAlumni " ); // 关闭用户与数据库的连接
string strpath = " [ " + Server.MapPath( " Date/DbBackup/ " ) + e.CommandArgument.ToString() + " /TzcAlumni " + " .bak] " ;
Directory.Delete(Server.MapPath( " ../Class/ClassPhotos " ), true ); // 删除原来的照片文件夹
DbOper.copyfolder(Server.MapPath( " ../Admin/Date/DbBackup/ " + e.CommandArgument.ToString() + " / " ) + " ClassPhotos " , Server.MapPath( " ../Class " )); // 复制照片文件夹
string ret = DbOper.DbRestore(strpath); // 还原数据库
Label2.Text = ret;
CMessageBox.ShowAjaxDialog(UpdatePanel1, " 1 " );
return ;
}
catch (Exception x)
{
CMessageBox.ShowAjaxDialog(UpdatePanel1, " 数据恢复失败- " + x.ToString());
}
}
}

killspid存储过程:

---------------- 断开所有用户打开的连接,关闭数据库
use master
go

if exists (select * from dbo.sysobjects where id = object_id(N ' [dbo].[p_killspid] ' ) and OBJECTPROPERTY(id, N ' IsProcedure ' ) = 1 )
drop procedure [dbo].[killspid]
GO

create proc killspid
@dbname sysname -- 要关闭进程的数据库名
as
declare @s nvarchar( 1000 )
declare tb cursor local for
select s = ' kill ' + cast(spid as varchar)
from master..sysprocesses
where dbid = db_id(@dbname)

open tb
fetch next from tb into @s
while @@fetch_status = 0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go

大部分代码是从网上搜集而来,发上来以便自己以后查阅,希望能给有同样需求的朋友一个参考.

转载于:https://www.cnblogs.com/zlzly/archive/2011/03/24/1994187.html