asp.net mysql备份数据库备份_asp.net 数据库备份与还原

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

DbOper类:usingSystem.IO;//publicsealedclassDbOper

{//DbOper类的构造函数///privateDbOper()

{

}//复制文件//要复制文件的源地址///要复制到的路径publicstaticvoidcopyfolder(stringfrom,stringto)

{stringfname=from.Substring(from.LastIndexOf("\\")+1);if(Directory.Exists(from))

{

Directory.CreateDirectory(from);

}

DirectoryInfo di=newDirectoryInfo(from);if(!Directory.Exists(to+"\\"+di.Name))

{

Directory.CreateDirectory(to+"\\"+di.Name);

}string[] fi=Directory.GetFiles(di.FullName);for(inti=0; i

{stringf_name=fi[i].Substring(fi[i].LastIndexOf("\\")+1);

File.Copy(from+"\\"+f_name, to+"\\"+fname+"\\"+f_name,true);

}

DirectoryInfo[] dis=di.GetDirectories();for(intj=0; j

{

copyfolder(dis[j].FullName.ToString(), to+"\\"+fname);

}

}//数据库备份//备份的路径///返回结果信息publicstaticstringDbBackup(stringpathfilename)

{stringret;

SQLDMO.Backup oBackup=newSQLDMO.BackupClass();

SQLDMO.SQLServer oSQLServer=newSQLDMO.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();

}returnret;

}//数据库恢复//备份数据所在的路径///返回结果信息publicstaticstringDbRestore(stringpathfilename)

{stringret;

SQLDMO.Restore oRestore=newSQLDMO.RestoreClass();

SQLDMO.SQLServer oSQLServer=newSQLDMO.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号如果你只有一个文件就是1oRestore.ReplaceDatabase=true;//替代现有数据库如不存则创建他oRestore.SQLRestore(oSQLServer);//调用恢复方法ret="成功恢复";

}catch(Exception x)

{

ret="恢复失败-"+x.ToString();

}finally{

oSQLServer.DisConnect();

}returnret;

}

}

前台:后台:protectedvoidPage_Load(objectsender, EventArgs e)

{

BindData();

}privatevoidBindData()

{string[] path=Directory.GetDirectories(@Server.MapPath("Date/DbBackup/"));//路径ArrayList str=newArrayList();foreach(stringfnameinpath)

{

str.Add(fname.Substring(Server.MapPath("Date/DbBackup/").Length));

}

GridView1.DataSource=str;

GridView1.DataBind();

}protectedvoidButton1_Click(objectsender, EventArgs e)

{try{stringstrpath=Server.MapPath("Date/DbBackup/")+System.DateTime.Now.ToString().Replace(":","").Replace("","");

Directory.CreateDirectory(strpath);//创建一个新的文件夹stringpath="["+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());

}

}protectedvoidGridView1_RowCommand(objectsender, GridViewCommandEventArgs e)

{if(e.CommandName.ToString()=="deleted")

{stringAdminID=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=newKillSpidDataClassesDataContext();

kdb.killspid("TzcAlumni");//关闭用户与数据库的连接stringstrpath="["+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"));//复制照片文件夹stringret=DbOper.DbRestore(strpath);//还原数据库Label2.Text=ret;

CMessageBox.ShowAjaxDialog(UpdatePanel1,"1");return;

}catch(Exception x)

{

CMessageBox.ShowAjaxDialog(UpdatePanel1,"数据恢复失败-"+x.ToString());

}

}

}

killspid存储过程:----------------断开所有用户打开的连接,关闭数据库

use master

goifexists (select*from dbo.sysobjectswhereid=object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure')=1)

drop procedure [dbo].[killspid]

GO

create proc killspid

@dbname sysname--要关闭进程的数据库名asdeclare @s nvarchar(1000)

declare tb cursor localforselect s='kill'+cast(spidasvarchar)

from master..sysprocesseswheredbid=db_id(@dbname)

open tb

fetch next from tb into @swhile@@fetch_status=0begin

exec(@s)

fetch next from tb into @s

end

close tb

deallocate tb

go

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