Restore and restore SQL server with ASP. NET

A project was created last time, involving database restoration and restoration. I found it online and implemented it using SQLDMO. Just add SQLDMO reference, then, use the method of the following class to implement it.

I expanded the original author's class to automatically identify the database connection strings in web. config, and use variable settings to restore restored information.

You need to pay attention to the restoration, which is the most serious problem during restoration. Other users cannot restore data when using the database. The solution is to add a stored procedure to the MASTER database:

Create proc killspid (@ dbname varchar (20 ))
As begin declare @ SQL nvarchar (500)
Declare @ spid int set @ SQL = 'descare 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

Run the stored procedure before restoration. You need to pass dbname, which is the name of your database. Below is the original code of 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 using SQLDMO to back up and restore the Microsoft SQL Server database
///
Public class DbOper
...{
Private string server;
Private string uid;
Private string pwd;
Private string database;
Private string conn;
/**////
/// DbOper Constructor
///
Public DbOper ()
...{
Conn = System. Configuration. ConfigurationSettings. receivettings ["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. 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 = "database backup"; oBackup. Initialize = true; oBackup. SQLBackup (oSQLServer );
Return true;
}
Catch
...{
Return false;
Throw;
}
Finally
...{
OSQLServer. DisConnect ();
}
}
/// Restore the database ///
Public string DbRestore (string url)
{If (exepro ()! = True)
// Execute the Stored Procedure {return "operation failed ";}
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 "failed to restore Database ";
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 ();
}
}
}
}