Microsoft SQL Server database backup and recovery class)

C # + SQL database backup
1. Add sqldmo DLL file reference to the project (sqldmo (SQL distributed management objects, SQL distributed management object ))
2. Add using sqldmo reference to the corresponding page
3. The following is a class written in C # for Microsoft SQL Server database backup and recovery:

Using system;

Namespace dbservice
{
/// <Summary>
/// Dboper class, mainly used to back up and restore Microsoft SQL Server databases
/// </Summary>
Public sealed class dboper
{
/// <Summary>
/// Dboper Constructor
/// </Summary>
Private dboper ()
{
}

///

// Database Backup
///
Public static void dbbackup ()
{< br> try
{< br> sqldmo. backup obackup = new sqldmo. backupclass ();
sqldmo. sqlserver osqlserver = new sqldmo. sqlserverclass ();
osqlserver. loginsecure = false;
osqlserver. connect ("localhost", "sa", "1234");
obackup. action = sqldmo. sqldmo_backup_type.sqldmobackup_database;
obackup. database = "northwind";
obackup. files = @ "D: \ northwind. bak ";
obackup. backupsetname = "northwind";
obackup. backupsetdescription = "database backup";
obackup. initialize = true;
obackup. sqlbackup (osqlserver);
}< br> catch
{< br> throw;
}< BR >}

/// <Summary>
/// Restore the database
/// </Summary>
Public static void dbrestore ()
{
Try
{
Sqldmo. Restore orestore = new sqldmo. restoreclass ();
Sqldmo. sqlserver osqlserver = new sqldmo. sqlserverclass ();
Osqlserver. loginsecure = false;
Osqlserver. Connect ("localhost", "sa", "1234 ");
Orestore. Action = sqldmo. sqldmo_restore_type.sqldmorestore_database;
Orestore. Database = "northwind ";
Orestore. Files = @ "D: \ northwind. Bak ";
Orestore. filenumber = 1;
Orestore. replacedatabase = true;
Orestore. sqlrestore (osqlserver );
}
Catch
{
Throw;
}
}
}
}
For more information, see use sqldmo in C # To back up and restore a Microsoft SQL Server database.

Http://dev.csdn.net/develop/article/28/28564.shtm

The above method is feasible when you do not use the database to be restored, but when you use the database, you must kill the process.
CodeAs follows:

/// <Summary>
/// Restore database functions
/// </Summary>
/// <Param name = "strdbname"> database name </param>
/// <Param name = "strfilename"> full path name of the database backup file </param>
/// <Returns> </returns>
Public bool restoredb (string strdbname, string strfilename)
{
// Pbar = pgbmain;
Sqldmo. svr SVR = new sqldmo. sqlserverclass ();
Try
{
// Server name, database username, database username and password
SVR. Connect ("localhost", "sa", "Hai ");

Sqldmo. queryresults QR = SVR. enumprocesses (-1 );
Int icolpidnum =-1;
Int icoldbname =-1;
For (INT I = 1; I <= QR. columns; I ++)
{
String strname = QR. get_columnname (I );
If (strname. toupper (). Trim () = "spid ")
{
Icolpidnum = I;
}
Else if (strname. toupper (). Trim () = "dbname ")
{
Icoldbname = I;
}
If (icolpidnum! =-1 & icoldbname! =-1)
Break;
}
// Kill the process that uses the strdbname Database
For (INT I = 1; I <= QR. Rows; I ++)
{
Int LPID = QR. getcolumnlong (I, icolpidnum );
String strdbname = QR. getcolumnstring (I, icoldbname );
If (strdbname. toupper () = strdbname. toupper ())
{
SVR. killprocess (LPID );
}
}

Sqldmo. Restore res = new sqldmo. restoreclass ();
Res. Action = 0;
Res. Files = strfilename;

Res. Database = strdbname;
Res. replacedatabase = true;
Res. sqlrestore (SVR );
Return true;
}
Catch
{
Return false;
}
Finally
{
SVR. Disconnect ();
}
}
You can back up the database through the time set by the user, you can use the timer to implement
Or call the stored procedure:
Method:
Restore the entire database:

Restore database {database_name | @ database_name_var}
[From <backup_device> [,... n]
[
[Restricted_user]
[[,] File = {file_number | @ file_number}]
[[,] Password = {password | @ password_variable}]
[[,] Medianame = {media_name | @ media_name_variable}]
[[,] Mediapassword = {mediapassword | @ mediapassword_variable}]
[[,] Move 'logical _ file_name 'to 'operating _ system_file_name']
[,... N]
[[,] Keep_replication]
[[,] {Norecovery | recovery | standby = undo_file_name}]
[[,] {Norewind | rewind}]
[[,] {Nounload | unload}]
[[,] Replace]
[[,] Restart]
[[,] Stats [= percentage]
]

Restore part of the database:

Restore database {database_name | @ database_name_var}
<File_or_filegroup> [,... n]
[From <backup_device> [,... n]
[
{Partial}
[[,] File = {file_number | @ file_number}]
[[,] Password = {password | @ password_variable}]
[[,] Medianame = {media_name | @ media_name_variable}]
[[,] Mediapassword = {mediapassword | @ mediapassword_variable}]
[[,] Move 'logical _ file_name 'to 'operating _ system_file_name']
[,... N]
[[,] Norecovery]
[[,] {Norewind | rewind}]
[[,] {Nounload | unload}]
[[,] Replace]
[[,] Restricted_user]
[[,] Restart]
[[,] Stats [= percentage]
]

Restore a specific file or file group:

Restore database {database_name | @ database_name_var}
<File_or_filegroup> [,... n]
[From <backup_device> [,... n]
[
[Restricted_user]
[[,] File = {file_number | @ file_number}]
[[,] Password = {password | @ password_variable}]
[[,] Medianame = {media_name | @ media_name_variable}]
[[,] Mediapassword = {mediapassword | @ mediapassword_variable}]
[[,] Move 'logical _ file_name 'to 'operating _ system_file_name']
[,... N]
[[,] Norecovery]
[[,] {Norewind | rewind}]
[[,] {Nounload | unload}]
[[,] Replace]
[[,] Restart]
[[,] Stats [= percentage]
]

Restore transaction logs:

Restore log {database_name | @ database_name_var}
[From <backup_device> [,... n]
[
[Restricted_user]
[[,] File = {file_number | @ file_number}]
[[,] Password = {password | @ password_variable}]
[[,] Move 'logical _ file_name 'to 'operating _ system_file_name']
[,... N]
[[,] Medianame = {media_name | @ media_name_variable}]
[[,] Mediapassword = {mediapassword | @ mediapassword_variable}]
[[,] Keep_replication]
[[,] {Norecovery | recovery | standby = undo_file_name}]
[[,] {Norewind | rewind}]
[[,] {Nounload | unload}]
[[,] Restart]
[[,] Stats [= percentage]
[[,] Stopat = {date_time | @ date_time_var}
| [,] Stopatmark = 'Mark _ name' [After datetime]
| [,] Stopbeforemark = 'Mark _ name' [After datetime]
]
]

<Backup_device >::=
{
{'Logical _ backup_device_name '| @ logical_backup_device_name_var}
| {Disk | TAPE} =
{'Physical _ backup_device_name '| @ physical_backup_device_name_var}
}

<File_or_filegroup >::=
{
File = {logical_file_name | @ logical_file_name_var}
|
Filegroup = {logical_filegroup_name | @ logical_filegroup_name_var}
}