Dynamic Backup Sql-server Database--SQLDMO

Reprint: http://www.cnblogs.com/liulanglang/archive/2007/12/04/981812.html

Last week to write a sql-server database backup restore program, very no idea, just remember to go to school with SQL statements to complete the backup and restore of the database, the rest of the way has not been tried. After data search finally found a solution, the effect is good, so write down to share.

The most common is to use SQL commands for database backup,

The most common SQL statements are as follows:

Backup [dbname] to disk=[path + file name]

Restore [dbname] from disk=[path + file name]

In addition, you can use SQLDMO to implement the function of database backup.

The SQLDMO (SQL Distributed Management Objects,sql Distributed management Object) encapsulates objects in a Microsoft SQL Server 2000 database. SQL-DMO allows applications to be written in languages that support automation or COM to manage all parts of the SQL Server installation. SQL-DMO is the application interface (API) used by SQL Server Enterprise Manager in SQL Server 2000, so applications that use SQL-DMO can perform all the functions that SQL Server Enterprise Manager performs.

To use SQLDMO.DLL in asp.net2005, first register this component. To register the SQLDMO.DLL component:

1. "Item"-"Add Reference"-"COM"--"SQLDMO". DLL "So you can register the SQLDMO.DLL component.

2. SQLDMO.DLL files in the dynamic-link library under C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory.

3. Copy the DLL file to the C:\WINDOWS\system32 directory

4. Create a new folder in the C:\WINDOWS\system32 directory: resources, build a new folder in the inside 2052, and then copy the Rll file to 2052. Put it on the C:\WINDOWS\system32\resources\. 2052 in the Catalog

5. Register: At start >> run >> (input) Regsvr32 C:\Program Files\Microsoft SQL Server\80\tools\binn\sqldmo.dll Enter.

After registering the SQLDMO.DLL component, it is a little different to use this component for development in C #.

Use this component in a C # Web program to add a reference to the SQLDMO.DLL component in the Solution Explorer. Then add "using SQLDMO;" In the project's namespace. namespace, you can use this component in your Web project to complete the above operations.

Specific steps: Select the "Resolve Explorer" project, right-click, "Add Reference", and then select the SQLDMO.DLL component in the COM option to add.

This is not a hassle in Windows projects, as long as you complete the steps of the SQLDMO.DLL component registration step, you can add this reference "using SQLDMO;" Directly in the Windows program, and you can use this component directly.

The following is how the backup and recovery of the Microsoft SQL Server database is written in the C # language:

(Reminder: First to register the SQLDMO.DLL component .) Use the SQLDMO.DLL component in your Web program to add a SQLDMO.DLL reference, and use SQLDMO in your Windows project . DLL components, directly added using SQLDMO after registration ; )

<summary>

Database backup

Parameter information the name of the database to back up backup. Database = "Northwind";

The other is the path to the backup file and the file name backup. Files = @ "C:\databak.bak";

</summary>

private void Button1_Click (object sender, System.EventArgs e)

{

SQLDMO. Backup backup = new SQLDMO. Backupclass ();

SQLDMO. SQL Server SQL Server = new SQLDMO. Sqlserverclass ();

Try

{

Sqlserver.loginsecure = false;

Sqlserver.connect ("IP", "USERNAME", "PWD");

Backup. Action = SQLDMO. Sqldmo_backup_type. Sqldmbackup _database;

Backup. Database = "Northwind";//the names of the databases to be backed up, such as Northwind

Backup. Files = @ "C:\databak.bak";//store path, and backup file name

Backup. BackupSetName = "Northwind";

Backup. backupsetdescription = "Database Backup";

Backup. Initialize = true;

Backup. SQLBackup (SQL Server);

}

Catch (Exception ex)

{

Throw ex;

}

Finally

{

Sqlserver.disconnect ();

}

}

<summary>

Database recovery

Parameter information the name of the database to restore restore. Database = "Northwind_res";

The other is the path and file name where the database backup file is stored

Backup. Files = @ "C:\databak.bak"; used to restore the database

</summary>

private void Button2_Click (object sender, System.EventArgs e)

{

SQLDMO. Restore restore = new SQLDMO. Restoreclass ();

SQLDMO. SQL Server SQL Server = new SQLDMO. Sqlserverclass ();

Try

{

Sqlserver.loginsecure = false;

Sqlserver.connect ("IP", "USERNAME", "PWD");

Restore. Action = SQLDMO. Sqldmo_restore_type. Sqldmrestore _database;

Restore. Database = "Northwind_res";

Restore. Files = @ "C: \ Databak.bak";

Restore. FileNumber = 1;

Restore. ReplaceDatabase = true;

Restore. SQLRestore (SQL Server);

}

catch (Exception ex)

{

Throw ex;

}

Finally

{

Sqlserver.disconnect ();

}

}

}

In fact, this approach is to call the Sql-server database comes with the data backup function, this function is to use the above is said to be two SQL statements, but is formed in the form of stored procedures. In the sql-server system there are stored procedures generated by these two SQL commands, as follows.

Backup [dbname] to disk=[path + file name]

Restore [dbname] from disk=[path + file name]
In fact there is another way to implement the database backup restore method, after trying I use the second method. This method is implemented using a custom stored procedure, which declares that the stored procedure in the following article is reproduced, but the method is very good! http://www.cnblogs.com/liulanglang/archive/2007/12/04/981821.html

Dynamic Backup Sql-server Database--SQLDMO