Back up and restore a database using sqldmo

Sqldmo. dll is released along with SQL Server2000. Sqldmo. dll itself is a COM Object

Sqldmo (SQL distributed management objects, SQL distributed management object) encapsulates objects in the Microsoft SQL Server 2000 database. SQL-DMO allows applications to be written in languages Supporting automation or comProgramTo manage all parts installed on SQL Server. SQL-DMO is the application interface (API) used by SQL Server Enterprise Manager in SQL Server 2000; therefore, applications that use SQL-DMO can execute all the functionality that SQL Server Enterprise Manager performs.
------
General Relationship Between sqlserver:
Application --> sqlserver --> Database
------
The instance sqldmo mainly uses the following classes:
Sqldmo. Application (created using sqldmo. applicationclass ),
Sqldmo. sqlserver (created using sqldmo. sqlserverclass and used its connect to the database server ),
Sqldmo. namelist (you can use it and application to obtain the server set. For other information, see its API)
Sqldmo. Database (you can use it to obtain a database set with sqlserver. databases)

Key to personal database backup and RestorationCode:

/// File name: sqlserverbackuprestore. CS
/// Function: Back up and restore the sqlserver Database
/// Author: Yang Chun
/// Last modification date: 22:35:00
Using system;
Using system. Data. sqlclient;

/// <Summary>
/// Summary of sqlserverbackuprestore
/// Sqlserver data backup and Restoration
/// </Summary>
Public class sqlserverbackuprestore: idisposable
{
Private string _ servername = string. empty;
Private string _ userid = string. empty;
Private string _ userpassword = string. empty;
Private string _ databasename = string. empty;
Private string _ backfile = string. empty;
Sqldmo. sqlserverclass Server = NULL;
Sqldmo. backupclass backup = NULL;
Sqldmo. restoreclass restore = NULL;

/// <Summary>
// Restore an SQL Server backup
/// </Summary>
Public sqlserverbackuprestore ()
{
}
/// <Summary>
// Restore an SQL Server backup
/// </Summary>
/// <Param name = "connectionstring"> database connection string </param>
/// <Param name = "backfile"> Backup file name </param>
Public sqlserverbackuprestore (string connectionstring, string backfile)
{
Sqlconnectionstringbuilder connectionbuilder = new sqlconnectionstringbuilder (connectionstring );
_ Servername = connectionbuilder. datasource;
_ Userid = connectionbuilder. userid;
_ Userpassword = connectionbuilder. Password;
_ Databasename = connectionbuilder. initialcatalog;
_ Backfile = backfile;
}
/// <Summary>
// Restore an SQL Server backup
/// </Summary>
/// <Param name = "servername"> server name or IP address </param>
/// <Param name = "userid"> database account </param>
/// <Param name = "userpassword"> Database Password </param>
/// <Param name = "databasename"> database name </param>
/// <Param name = "backfile"> Backup file name </param>
Public sqlserverbackuprestore (string servername, string userid, string userpassword, string databasename, string backfile)
{
_ Servername = servername;
_ Userid = userid;
_ Userpassword = userpassword;
_ Databasename = databasename;
_ Backfile = backfile;
}
/// <Summary>
/// Server name or IP address
/// </Summary>
Public String servername
{
Get {return _ servername ;}
Set {_ servername = value ;}
}
/// <Summary>
/// Database username
/// </Summary>
Public String userid
{
Get {return _ userid ;}
Set {_ userid = value ;}
}
/// <Summary>
/// Database Password
/// </Summary>
Public String Password
{
Get {return _ userpassword ;}
Set {_ userpassword = value ;}
}
/// <Summary>
/// Database Name
/// </Summary>
Public String databasename
{
Get {return _ databasename ;}
Set {_ databasename = value ;}
}
/// <Summary>
/// Backup file name
/// </Summary>
Public String backupfile
{
Get {return _ backfile ;}
Set {_ backfile = value ;}
}
/// <Summary>
/// Back up the database
/// </Summary>
/// <Returns> Successful, true, failed, false </returns>
Public bool backup ()
{
Bool result = true;
Server = new sqldmo. sqlserverclass ();
Backup = new sqldmo. backupclass ();
Try
{
Server. loginsecure = false;
Server. Connect (servername, userid, password );
Backup. Action = sqldmo. sqldmo_backup_type.sqldmobackup_database;
Backup. Database = databasename;
Backup. Files = backupfile;
Backup. backupsetname = databasename;
Backup. backupsetdescription = system. datetime. Now. tostring ("yyyy-mm-dd hh: mm: SS ");
Backup. initialize = true;
Backup. sqlbackup (server );
}
Catch (exception ex)
{
Result = false;
Throw ex;
}
Finally
{
Server. Disconnect ();
}
Return result;
}
/// <Summary>
/// Restore the database
/// </Summary>
/// <Returns> Successful, true, failed, false </returns>
Public bool restore ()
{
Bool result = false;
Server = new sqldmo. sqlserverclass ();
Try
{
Server. loginsecure = false;
Server. Connect (servername, userid, password );
Sqldmo. queryresults queryrestlts = server. enumprocesses (-1 );
Int icolpidnum =-1;
Int icoldbname =-1;
For (INT I = 1; I <= queryrestlts. columns; I ++)
{
String strname = queryrestlts. get_columnname (I );
If (strname. toupper (). Trim () = "spid ")
{
Icolpidnum = I;
}
Else if (strname. toupper (). Trim () = "dbname ")
{
Icoldbname = I;
}
If (icolpidnum! =-1 & icoldbname! =-1)
Break;
}

For (INT I = 1; I <= queryrestlts. Rows; I ++)
{
Int LPID = queryrestlts. getcolumnlong (I, icolpidnum );
String strdbname = queryrestlts. getcolumnstring (I, icoldbname );
If (strdbname. toupper () = databasename. toupper ())
Server. killprocess (LPID );
}

Restore = new sqldmo. restoreclass ();
Restore. Action = sqldmo. sqldmo_restore_type.sqldmorestore_database;
Restore. Database = databasename;
Restore. Files = backupfile;
Restore. filenumber = 1;
Restore. replacedatabase = true;
Restore. sqlrestore (server );
Result = true;
}
Catch (exception ex)
{
Throw ex;
}
Finally
{
Server. Disconnect ();
}
Return result;
}
# Region idisposable Member
/// <Summary>
/// Release the resource and display the call after use
/// </Summary>
Public void dispose ()
{
If (server! = NULL)
{
Server. Disconnect ();
Server = NULL;
}
If (backup! = NULL)
{
Backup = NULL;
}
If (Restore! = NULL)
{
Restore = NULL;
}
GC. Collect ();
GC. waitforpendingfinalizers ();
}

# Endregion
}

Download related code and DLL