Restore and restore SQL Server with ASP.net

asp.net|server|sql| Recovery

Last done a project, involving the restoration of the database and recovery, to the Internet to find a bit, is the use of SQLDMO implementation, as long as the addition of SQLDMO reference is good, and then use the next class method can be achieved.

I expanded the original author's class, can automatically identify the database connection string in Web.config, you can set the restore information through the variable settings.

When you need to be aware of restores, when the problem is the most problematic, different users can not restore when using the database, the solution is to add a stored procedure in the master database:

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 + ' ') ' ex EC (@sql)
Open Getspid fetch NEXT from Getspid to @spid while @ @fetch_status <>-1 begin EXEC (' kill ' + @spid)
FETCH NEXT from Getspid to @spid end close Getspid deallocate getspid end Go

Execute this stored procedure before restoring, and you need to pass dbname, which is the name of your database. Below is the original code for the class: (The database connection string in Web.config is Constr)

using System;


using System.Configuration;


using System.Data.SqlClient;


Using System.Data;


namespace Web.base_class


    ... {


    /**////


///Dboper class, mainly applies sqldmo to implement backup and recovery of Microsoft SQL Server databases


    ///


public class Dboper


    ... {


private string server;


private string uid;


private string pwd;


private string database;


private String conn;


    /**////


///Dboper class constructors


    ///


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;


    }


   /**////


///Database Backup


    ///


public bool DbBackup (string URL)


... {SQLDMO. Backup obackup = new SQLDMO. Backupclass ();


SQLDMO. SQL Server 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 = "Database Backup"; obackup.initialize = true; Obackup.sqlbackup (oSQLServer);


return true;


    }


catch


    ... {


return false;


throw;


    }


finally


   ... {


Osqlserver.disconnect ();


    }


   }


///Database Restore///


public string dbrestore (string url)


{if (Exepro)! =true)


//Execute stored Procedure {return "operation failed";}


else {SQLDMO. Restore Orestore = new SQLDMO. Restoreclass ();


SQLDMO. SQL Server 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 "restore Database Failed";


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


 }


}


}


}