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

 

上次做了个项目，涉及到数据库的还原和恢复，到网上找了一下，是利用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.appsettings["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();

              }

         }

     }

}