用asp.net还原与恢复sqlserver数据库

时间:2008-10-07 12:20:42  来源:第二电脑网上收集  作者:

  第二电脑网导读:动识别web.config里 的数据库连接字符串,可以通过变量设置还原恢复的信息。需要注意的时还原,还原的时候问题最大了,有别的用户使用数据库的时候无法还原,解决办法就是在MASTER数据库中...
  正文: 利用SQLDMO实现的,只要添加SQLDMO引用就好了,然后利用下边的类的方法就可以实现了。
我把原作者的类扩充了一下,可以自动识别web.config里 的数据库连接字符串,可以通过变量设置还原恢复的信息。

需要注意的时还原,还原的时候问题最大了,有别的用户使用数据库的时候无法还原,解决办法就是在MASTER数据库中添加一个存储过程:


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


在还原之前先执行这个存储过程,需要传递dbname,就是你的数据库的名字。下边是类的原代码:(web.config里的数据库连接字符串是constr)
 

using System;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

namespace web.base_class

{

     
/// <summary>

     
/// DbOper类,主要应用SQLDMO实现对Microsoft SQL Server数据库的备份和恢复

     
/// </summary>


     
public class DbOper

     
{

          
private string server;

          
private string uid;

          
private string pwd;

          
private string database;

          
private string conn;

         
/// <summary>

         
/// DbOper类的构造函数

         
/// </summary>


         
public DbOper()

         
{

              conn
=System.Configuration.ConfigurationSettings.Apphotoshop/ target=_blank class=infotextkey>psettings["constr"].ToString();

              server
=cut(conn,"server=",";");

              uid
=cut(conn,"uid=",";");

              pwd
=cut(conn,"pwd=",";");

              database
=cut(conn,"database=",";");

         }


         
public string cut(string str,string bg,string ed)

         
{

              
string sub;

              sub
=str.Substring(str.IndexOf(bg)+bg.Length);

              sub
=sub.Substring(0,sub.IndexOf(";"));

              
return sub;

         }


 

         
/// <summary>

         
/// 数据库备份

         
/// </summary>


         
public  bool DbBackup(string url)

         
{

              SQLDMO.Backup oBackup 
= new SQLDMO.BackupClass();

              SQLDMO.SQLServer oSQLServer 
= new SQLDMO.SQLServerClass();

              
try

              
{

                   oSQLServer.LoginSecure 
= false;

                   oSQLServer.Connect(server,uid, pwd);

                   oBackup.Action 
= SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;

                   oBackup.Database 
= database;

                   oBackup.Files 
= url;//"d:/Northwind.bak";

                   oBackup.BackupSetName 
= database;

                   oBackup.BackupSetDescription 
= "数据库备份";

                   oBackup.Initialize 
= true;

                   oBackup.SQLBackup(oSQLServer);

                   
return true;

              }


              
catch

              
{

                   
return false;

                   
throw;

              }


              
finally

              
{

                   oSQLServer.DisConnect();

              }


         }


 

         
/// <summary>

         
/// 数据库恢复

         
/// </summary>


         
public string DbRestore(string url)

         
{

              
if(exepro()!=true)//执行存储过程

              
{

                   
return "操作失败";

              }


              
else

              
{

                   SQLDMO.Restore oRestore 
= new SQLDMO.RestoreClass();

                   SQLDMO.SQLServer oSQLServer 
= new SQLDMO.SQLServerClass();

                   
try

                   
{

                        oSQLServer.LoginSecure 
= false;

                        oSQLServer.Connect(server, uid, pwd);

                        oRestore.Action 
= SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;

                        oRestore.Database 
= database;

                        oRestore.Files 
= url;//@"d:/Northwind.bak";

                        oRestore.FileNumber 
= 1;

                        oRestore.ReplaceDatabase 
= true;

                        oRestore.SQLRestore(oSQLServer);

                       
return "ok";

                   }


                   
catch(Exception e)

                   
{

                       
return "恢复数据库失败";

                       
throw;

                   }


                   
finally

                   
{

                        oSQLServer.DisConnect();

                   }


              }


         }


          
private bool exepro()

         
{

              SqlConnection conn1 
= new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");

              SqlCommand cmd 
= new SqlCommand("killspid",conn1);

              cmd.CommandType 
= CommandType.StoredProcedure;

              cmd.Parameters.Add(
"@dbname","port");

              
try

              
{

                   conn1.Open();

                   cmd.ExecuteNonQuery();

                   
return true;

              }


              
catch(Exception ex)

              
{

                   
return false;

              }


              
finally

              
{

                   conn1.Close();

              }


 

         }


     }


}


用asp.net还原与恢复sqlserver数据库》由第二电脑网原创提供,转载请注明:http://www.002pc.com/master/College/Programming/ASP/2008-10-07/3410.html


关键字:

关于《用asp.net还原与恢复sqlserver数据库》文章的评论

站内搜索: 高级搜索

热门搜索: Windows style 系统 tr IP QQ CPU 安装 function 注册 if td