1. First join the SQL Server Management Object Interop.sqldMo.dll in the project (it is a COM component, the name is Microsoft SqldMo Object Library)
2. Create a class for it to implement two methods (backup and recovery)
Using system;
Namespace MaxClient {///
/// Database Backup and Recovery
///
Public Class Dboperater
{
Public dboperater ()
{
//
// TODO: Add constructor logic here
//
}
///
/// database backup
///
Public Static String Dbckup (String Servername, String Username, String Password, String Dbname, String Backupdbname, String Topath)
{
SqldMo.backup Obackup = new sqldmo.backupClass ();
Sqldmo.sqlserver osqlserver = new sqldmo.sqlserverclass ();
Try
{
OSQLServer.loginsecure = false;
OSQLServer.connect (ServerName, Username, Password);
Obackup.action = SqldMo.sqldMo_Backup_type.sqldmobackup_database;
OBACKUP.DATABASE = DBNAME;
OBACKUP.FILES = @ Topath Backupdbname ". Bak";
OBACKUP.BACKUPSETNAME = backupdbname;
OBACKUP.BACKUPSETDESCRIPTION = "Database Backup";
OBACKUP.INITIALIZE = True;
Obackup.sqlbackup (OSQLServer);
Return "Database has been successfully backed up to [" Obackup.files "]";
}
Catch (Exception EX)
{
Throw New Exception ("Database Backup Fails:" EX.MESSAGE);
}
Finally
{
OSQLServer.disconnect ();
}
}
///
/// Database Recovery
///
Public Static String Dbrestore (String ServerName, String Username, String Password, String Dbname, String Backupfile)
{
SqldMo.Restore orestore = new sqldmo.restoreClass ();
Sqldmo.sqlserver osqlserver = new sqldmo.sqlserverclass ();
Try
{
OSQLServer.loginsecure = false;
OSQLServer.connect (ServerName, Username, Password);
Ostore.Action = sqldmo.sqldmo_restore_type.sqldmoStore_Database;
Ostore.Database = dbname;
orestore.files = @Backupfile;
orestore.filenumber = 1;
orestore.replacedatabase = true;
Ostore.sqlrestore (OSQLServer); Return "Database" DBNAME "has successfully recovered!";
}
Catch (Exception EX)
{
Throw New Exception ("Database Recovery Failed:" EX.MESSAGE);
}
Finally
{
OSQLServer.disconnect ();
}
}
}} 3. Also create a stored procedure in the local Master database to kill all access to all access to the database when recovery
CREATE PROCEDURE sp_KillThread @dbname varchar (20) asbegindeclare @sql nvarchar (500), @ temp varchar (1000) declare @spid intset @ sql = 'declare getspid cursor forselect spid from master..sysprocesses where dbid = db_id (' '' @ dbname '' ')' exec (@sql) open getspid fetch next from getspid into @spidwhile @@ fetch_status <> -1begin set @ temp = 'kill' rtrim (@spid) exec (@temp) fetch next from getspid INTO @spidendclose getspidDeallocate getspidGo4. Backup and Recovery Action:
Private void button1_click (object sender, system.eventargs e) {try1.value; // backup to ... string ret = dboperater.dbbackup (t_server.text, t_user.text, password1.value, T_db.text, t_filename.text, @ path); this.label1.text = re;} catch (exception ex) {this.label1.text = ex. measure;}}
Private void button2_click (object sender, system.eventargs e) {string path = this.file1.value; // Restore file path // String constr = "data source = localhost; data source = localhost; datarable = mAns user id = sa; password = password "; SqlConnection con = new SqlConnection (conStr); string cmdText = String.Format (" EXEC sp_KillThread @dbname = '{0}' ", t_db.Text); SqlCommand com = new SqlCommand ( CMDText, Con); try {con.open (); com.executenonquery () ;con.close (); // Restore database string ret = dboperater.dbrestore (t_server.text, t_user.text, password1.value, t_db. THST, PATH); this.label1.text = ret;
This program can only restore DBs of local backups or backups of other machines of the local SQLServer installation path.
