Practical tips for restoring and restoring SQL Server_ with ASP.net

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 + ' ' /c0>) ' EXEC (@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, which primarily applies sqldmo to backup and restore Microsoft SQL Server databases
///
public class Dboper
... {
private string server;
private string uid;
private string pwd;
private string database;
private String Conn;
/**////
Constructors for Dboper classes
///
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 Recovery///
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 ();
}
}
}
}