ASP application of creating backup and check by using SQL-DMO skillfully

sql| Backup | create

Overview

SQL Distributed Management Objects (SQL Distributed Management Objects, SQL-DMO) extend SQL Server functionality by providing developers with methods to perform common tasks in both program and scripting languages. This article discusses how to skillfully use SQL-DMO to create a database backup and verification of ASP applications.

premises

You need to have knowledge of SQL Server database backup. Also add a reference to the SQL-DMO library in the Global.asa file. The following is a reference to SQL Server 2000:

<!--METADATA type= "TypeLib" name= "Microsoft SQLDMO Object Library" uuid= "{10010001-e260-11cf-ae68-00aa004a34d5}" version= "8.0"-->

The sample code for this article applies to SQL 7.0, MSDE, and SQL Server 2000.

Joy and Sorrow

Using SQL-DMO objects makes people happy and worried. It provides a lot of functionality so that you don't know how to use it. This article only discusses the object properties and methods involved in the example. You can find a lot of relevant information on SQL Server online teaching.

SQLDMO. SQL Server

To connect to a SQL Server database:

<% Dim srv Set srv = Server.CreateObject ("SQLDMO.") SQL Server ") srv. LoginTimeout = srv. Connect "servername", "username", "password"%>

This connects the SQL Server database by substituting the username and password. If you want to use NT authentication, set its LoginSecure property to True (true), ignore the incoming username and password, and use NT's registration information.

SQLDMO. Database

Lists the databases in the server. In the example of this article, the database that is listed is backed up. The following code lists the database in the server in the Drop-down menu:

<% Dim srv Dim Objdb Set srv = Server.CreateObject ("SQLDMO. SQL Server ") srv. LoginTimeout = srv. Connect "servername", "username", "password" Set objdb = Server.CreateObject ("SQLDMO". Database ")%> <select name=" Fdatabase "><% for each objdb in srv. Databases If objdb.systemobject = False then%> <OPTION><%=objDB.Name%></OPTION>< % End If next%> </SELECT>

SQLDMO. BackupDevice

Lists the backup devices installed on the server. I recommend using a backup device to back up the database. This allows you to use the SQL-DMO checksum to verify the backup. The following code lists the backup devices on the server:

<% Dim srv Dim Objdevice Set srv = Server.CreateObject ("SQLDMO. SQL Server ") srv. LoginTimeout = srv. Connect "servername", "username", "password" Set objdevice = Server.CreateObject ("SQLDMO". BackupDevice ") for each objdevice in srv. BackupDevices Response.Write objdevice.name + "<BR>" next%>

SQLDMO. Backup

This is the backup core object we want to use. It has a number of properties that let us do the same level of backup as the enterprise SQL Manager. Let's discuss the properties used in this example.

BackupSetName-backup filename.

DB-the database to back up.

Action-Full or incremental backup. There are other options, but only these two are used in the example.

BackupSetDescription-Backup description.

Files-file backup options. Indicate the path and name of the backup file, such as: C:\pubs.bak. When you use a file backup, the following backup appliance name is set to NULL.

Devices-Backup device on the server. If you are using a backup device, the file backup option above is set to NULL.

TruncateLog-BACKUP LOG option. The options are:

NoLog-Do not back up the transaction log.

Notruncate-Backup transaction log. The time tag is provided in the log.

Truncate-Backs up the transaction log, but does not keep transactions.

Initialize-If set to True (TRUE), the backup device will be preferred as a replacement for other backup media.

The following is the backup.asp file in the example:

<%@ language=vbscript%>

Backup Checksum

If you use VB or C + + programming, you can use event triggering to verify the backup process, but not in the ASP. We use the Sqldmo.backupdevice object's ReadBackupHeader method to confirm the success of the backup.

The following is the Verify.asp file code that lists the name of the backup appliance and provides information about the most recent backup.

<%@ Language=vbscript%>

The ReadBackupHeader method returns the QueryResults object. You can get the number of records backed up with its rows property. Then make a list of each row of records to search for information.

Other features

SQL-DMO also provides remote backup and recovery capabilities. This article does not cover database recovery, but SQL-DMO has a strong recovery capability.