Restore and restore an SQL Server database using ASP. NET

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 = 'Clare getspid cursor
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

{

/// <Summary>

/// Dboper class, mainly using sqldmo to back up and restore the Microsoft SQL Server database

/// </Summary>

Public class dboper

{

Private string server;

Private string uid;

Private string PWD;

Private string database;

Private string conn;

/// <Summary>

/// Dboper Constructor

/// </Summary>

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;

}

/// <Summary>

/// Database Backup

/// </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 = "database backup ";

Obackup. initialize = true;

Obackup. sqlbackup (osqlserver );

Return true;

}

Catch

{

Return false;

Throw;

}

Finally

{

Osqlserver. Disconnect ();

}

}

/// <Summary>

/// Restore the database

/// </Summary>

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

}

}

}

}