有哪位朋友知道可以实现数据库备份还原的一个组件 用sql语句作吧restore之类的具体请查看sql帮助:)SqlCommand足够了 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 web数据备份准备工作:1.先在项目中添加backfile.xml文件,根结点为<root></root>.2.打开库master,新建存储过程,旨在杀掉其它用户的访问。其中@dbname为备份数据库的名称(以上实例为hdnjw,根据你的数据库名改。) CREATE PROCEDURE killspid @dbname varchar(20)asbegindeclare @sql nvarchar(500),@temp varchar(1000)declare @spid intset @sql='declare getspid cursor forselect spid from master..sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spidwhile @@fetch_status <> -1begin set @temp='kill '+rtrim(@spid) exec(@temp)fetch next from getspid into @spidendclose getspiddeallocate getspidendGO 3.打开hdnjw,新建存储过程. CREATE PROCEDURE BackDataBase @backPath varchar(100)AS BACKUP DATABASE hdnjw TO DISK = @backPath WITH NOINITGO 4.根椐我的经验,最好添加一个能访问,操作数据表的用户。以下为备份与恢复的源程序.//备份数据库(hdnjw),备份文件存于../../databackup/下 private void btnBack_Click(object sender, System.EventArgs e) { SqlDataBase.DbBase conn = new SqlDataBase.DbBase(); DateTime dt = DateTime.Now ; string sPath = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString() ; System.IO.DirectoryInfo oD = new DirectoryInfo( Server.MapPath( "../../DataBackUp/" ) ) ; string sLinkName = "../../DataBackUp/" + sPath ; //判断是否存在此目录,否则新建目录 if ( !oD.Exists ) oD.Create() ; conn.BackDataBase( Server.MapPath( sLinkName ) ) ; //XML格式为<root><backfile><id></id><title></title><createtime></createtime><linkname></linkname></backfile></root> XmlDocument xmlDoc = new XmlDocument() ; xmlDoc.Load( Server.MapPath( "backfile.xml" ) ) ; //获取最后一个节点的递一个元素的值(ID) + 1 成为新的元素的ID值 XmlNode node = xmlDoc.LastChild.LastChild ; int intID = 1 ; if ( node != null ) intID += Convert.ToInt32( node.FirstChild.InnerText ) ; //添加一个backfile节点 XmlElement elem = xmlDoc.CreateElement( "backfile" ) ; XmlElement elem1 ; XmlText xmlTxt ; //添加backfile节点的id元素 elem1 = xmlDoc.CreateElement( "id" ) ; xmlTxt = xmlDoc.CreateTextNode( intID.ToString() ) ; //文件名 elem1.AppendChild( xmlTxt ) ; elem.AppendChild( elem1 ) ; //添加backfile节点的filename元素 elem1 = xmlDoc.CreateElement( "title" ) ; xmlTxt = xmlDoc.CreateTextNode( txtTitle.Text.Trim() ) ; //文件名 elem1.AppendChild( xmlTxt ) ; elem.AppendChild( elem1 ) ; //备份时间 elem1 = xmlDoc.CreateElement( "createtime" ) ; xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( "yyyy-MM-dd" ) ) ; elem1.AppendChild( xmlTxt ) ; elem.AppendChild( elem1 ) ; //文件路径 elem1 = xmlDoc.CreateElement( "linkname" ) ; xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( sPath ) ) ; elem1.AppendChild( xmlTxt ) ; elem.AppendChild( elem1 ) ; //添加操作,方便DataGrid取数据 elem1 = xmlDoc.CreateElement( "dosomething" ) ; xmlTxt = xmlDoc.CreateTextNode( "恢复" ) ; elem1.AppendChild( xmlTxt ) ; elem.AppendChild( elem1 ) ; //添加backfile节点 xmlDoc.DocumentElement.AppendChild( elem ) ; xmlDoc.Save( Server.MapPath( "backfile.xml" ) ) ; Response.Write( "<script language=javascript>alert('已成功备份!');var win =window.open('index.aspx','main');</script>" ) ; }//恢复数据库private void BackUpDataBase ( string strID ){ string strPath = "" ; XmlDataDocument dom=new XmlDataDocument(); dom.Load ( Server.MapPath( "backfile.xml" ) ) ; XmlElement root = dom.DocumentElement; //查找id.value=Request["id"]的节点 XmlNode node = root.SelectSingleNode( "//backfile[id='"+strID +"']"); if( node != null ) //找到了 { //文件路径节点 strPath = node.ChildNodes.Item(3).InnerText ; } if ( strPath != "" ) { CommonOperation.Operation Common = new CommonOperation.Operation(); SqlDataBase.DbBase conn = new SqlDataBase.DbBase(); //以数据库管理员(sa)恢复数据,首先杀掉所有用户进程 conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConnAdmin"] ; conn.DynamicStoredProcedure( "killspid" ) ; SqlParameter sp_temp1; sp_temp1 = conn.m_Command.Parameters.Add("@dbname",SqlDbType.VarChar,200); sp_temp1.Direction = ParameterDirection.Input; sp_temp1.Value = "hdnjw" ; try { string FilePath = Server.MapPath( "../../DataBackUp/" + strPath ) ; conn.ExecuteSqlForDynamic() ; conn.ExecuteSql( "alter database hdnjw set RESTRICTED_USER with rollback immediate" ) ;//恢复数据时禁止除SA外的任何人访问库 string sRestroe = "Restore DataBase hdnjw From disk='"+ FilePath +"' " ; conn.ExecuteSql( sRestroe ) ; conn.ExecuteSql( "alter database hdnjw set MULTI_USER with rollback immediate" ) ;//充许访问库 conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConn"] ; Response.Write( "<script language=javascript>alert('成功恢复数据!');window.close();</script>" ) ; } catch ( System.Exception ee ) { Common.MsgBox( this,ee.ToString() ) ; } finally { conn.Close() ; } } } Convert.ToInt32(SysGlobalRP.SysParam[2]) 高分求解!!!IE在切换选项卡时地址栏值是怎么改变的 c# Pos 打印走纸过多 敏感信息 c#如何自动更换代理IP 各位大哥,怎么能让 Label 不还行呢?(C# WinForm) 如何将一个数组存储在一个Array中(内详)??????? C#命令行参数的问题(help me) 分数不高 请帮忙 谢谢! 反序列化出错!(见代码) 我的studio.net怎么没有提示,跟写字板没啥区别了? 如何将一个Form变成模态的 请问在程序里面怎么结束其他程序的进程
准备工作:
1.先在项目中添加backfile.xml文件,根结点为<root></root>.
2.打开库master,新建存储过程,旨在杀掉其它用户的访问。其中@dbname为备份数据库的名称(以上实例为hdnjw,根据你的数据库名改。)
CREATE PROCEDURE killspid @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
3.打开hdnjw,新建存储过程.
CREATE PROCEDURE BackDataBase @backPath varchar(100)
AS
BACKUP DATABASE hdnjw TO DISK = @backPath WITH NOINIT
GO
4.根椐我的经验,最好添加一个能访问,操作数据表的用户。
以下为备份与恢复的源程序.//备份数据库(hdnjw),备份文件存于../../databackup/下
private void btnBack_Click(object sender, System.EventArgs e)
{
SqlDataBase.DbBase conn = new SqlDataBase.DbBase();
DateTime dt = DateTime.Now ;
string sPath = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString() ;
System.IO.DirectoryInfo oD = new DirectoryInfo( Server.MapPath( "../../DataBackUp/" ) ) ;
string sLinkName = "../../DataBackUp/" + sPath ;
//判断是否存在此目录,否则新建目录
if ( !oD.Exists )
oD.Create() ;
conn.BackDataBase( Server.MapPath( sLinkName ) ) ;
//XML格式为<root><backfile><id></id><title></title><createtime></createtime><linkname></linkname></backfile></root>
XmlDocument xmlDoc = new XmlDocument() ;
xmlDoc.Load( Server.MapPath( "backfile.xml" ) ) ;
//获取最后一个节点的递一个元素的值(ID) + 1 成为新的元素的ID值
XmlNode node = xmlDoc.LastChild.LastChild ;
int intID = 1 ;
if ( node != null )
intID += Convert.ToInt32( node.FirstChild.InnerText ) ;
//添加一个backfile节点
XmlElement elem = xmlDoc.CreateElement( "backfile" ) ;
XmlElement elem1 ;
XmlText xmlTxt ; //添加backfile节点的id元素
elem1 = xmlDoc.CreateElement( "id" ) ;
xmlTxt = xmlDoc.CreateTextNode( intID.ToString() ) ; //文件名
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ; //添加backfile节点的filename元素
elem1 = xmlDoc.CreateElement( "title" ) ;
xmlTxt = xmlDoc.CreateTextNode( txtTitle.Text.Trim() ) ; //文件名
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//备份时间
elem1 = xmlDoc.CreateElement( "createtime" ) ;
xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( "yyyy-MM-dd" ) ) ;
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//文件路径
elem1 = xmlDoc.CreateElement( "linkname" ) ;
xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( sPath ) ) ;
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ; //添加操作,方便DataGrid取数据
elem1 = xmlDoc.CreateElement( "dosomething" ) ;
xmlTxt = xmlDoc.CreateTextNode( "恢复" ) ;
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//添加backfile节点
xmlDoc.DocumentElement.AppendChild( elem ) ;
xmlDoc.Save( Server.MapPath( "backfile.xml" ) ) ; Response.Write( "<script language=javascript>alert('已成功备份!');var win =window.open('index.aspx','main');</script>" ) ;
}
//恢复数据库private void BackUpDataBase ( string strID )
{
string strPath = "" ;
XmlDataDocument dom=new XmlDataDocument();
dom.Load ( Server.MapPath( "backfile.xml" ) ) ;
XmlElement root = dom.DocumentElement;
//查找id.value=Request["id"]的节点
XmlNode node = root.SelectSingleNode( "//backfile[id='"+strID +"']");
if( node != null ) //找到了
{
//文件路径节点
strPath = node.ChildNodes.Item(3).InnerText ;
}
if ( strPath != "" )
{
CommonOperation.Operation Common = new CommonOperation.Operation();
SqlDataBase.DbBase conn = new SqlDataBase.DbBase();
//以数据库管理员(sa)恢复数据,首先杀掉所有用户进程
conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConnAdmin"] ;
conn.DynamicStoredProcedure( "killspid" ) ;
SqlParameter sp_temp1;
sp_temp1 = conn.m_Command.Parameters.Add("@dbname",SqlDbType.VarChar,200);
sp_temp1.Direction = ParameterDirection.Input;
sp_temp1.Value = "hdnjw" ;
try
{
string FilePath = Server.MapPath( "../../DataBackUp/" + strPath ) ;
conn.ExecuteSqlForDynamic() ;
conn.ExecuteSql( "alter database hdnjw set RESTRICTED_USER with rollback immediate" ) ;//恢复数据时禁止除SA外的任何人访问库
string sRestroe = "Restore DataBase hdnjw From disk='"+ FilePath +"' " ;
conn.ExecuteSql( sRestroe ) ;
conn.ExecuteSql( "alter database hdnjw set MULTI_USER with rollback immediate" ) ;//充许访问库
conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConn"] ;
Response.Write( "<script language=javascript>alert('成功恢复数据!');window.close();</script>" ) ;
}
catch ( System.Exception ee )
{
Common.MsgBox( this,ee.ToString() ) ;
}
finally
{
conn.Close() ;
}
}
}