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

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

createprockillspid(@dbnamevarchar(20))

as

begin

declare@sqlnvarchar(500)

declare@spidint

set@sql='declaregetspidcursorfor

selectspidfromsysprocesseswheredbid=db_id('''+@dbname+''')'

exec(@sql)

opengetspid

fetchnextfromgetspidinto@spid

while@@fetch_status<>-1

exec('kill'+@spid)

end

closegetspid

deallocategetspid

go

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

using system;

using system.configuration;

using system.data.sqlclient;

using system.data;

namespace web.base_class

{

/// <summary>

/// </summary>

public class dboper

private string server;

private string uid;

private string pwd;

private string database;

private string conn;

/// dboper类的构造函数

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;

/// 数据库备份

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();

/// 数据库恢复

public string dbrestore(string url)

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

return "操作失败";

else

sqldmo.restore orestore = new sqldmo.restoreclass();

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 "恢复数据库失败";

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");

conn1.open();

cmd.executenonquery();

catch(exception ex)

conn1.close();

最新内容请见作者的github页：http://qaseven.github.io/