How to implement SQL database backup, restore in ASP!

Choose a Blog from HANGHWP

1, how to implement the ASP SQL database backup, Recovery!
Answer: ASP online Backup SQL Server database:
1, Backup
<%
sql= "BACKUP database name to disk= '" &server.mappath ("Backup") & "\" & "Backuptext.dat" & ""
Set Cnn=server.createobject ("Adodb.connection")
Cnn.open "Driver={sql Server}; server= server name; uid=sa;pwd= "
Cnn.execute SQL
On Error Resume Next
If Err<>0 Then
Response.Write "Error:" &err. Descripting
Else
Response.Write "Data Backup success!" "
End If
%>

2. Recovery
<%
sql= "Restore database database name from disk= '" &server.mappath ("Backup") & "\" & "Backuptext.dat" & "" "
Set Cnn=server.createobject ("Adodb.connection")
Cnn.open "Driver={sql Server}; server= server name; uid=sa;pwd= "
Cnn.execute SQL
On Error Resume Next
If Err<>0 Then
Response.Write "Error:" &err. Descripting
Else
Response.Write "Data Recovery success!" "
End If
%>

Note: The above statement is to back up the data to disk in the backup directory, the file name is Backuptext.dat.

2, ASP can modify the structure of SQL database?
Answer: ALTER TABLE
Name
Alter table-change Table properties
Grammar
ALTER table Table [*]
ADD [Column] column type
ALTER table Table [*]
ALTER [Column] column {SET DEFAULT value | DROP DEFAULT}
ALTER table Table [*]
RENAME [column] column to Newcolumn
ALTER Table Table
RENAME to NewTable
ALTER Table Table
ADD table Constraint definition
Inputs
Table
The name of the existing table you are trying to change is www.knowsky.com.
Column
The existing or new column name.
Type
The type of the new column.
Newcolumn
The new name of the existing column.
NewTable
The new name of the table.
Table constraint definition
The new constraint definition for the table.

NEW table constraint for the table

Output
Alter
The information returned from the renamed column or table.
ERROR
If a column or table does not exist, the returned information is not present.
Describe
ALTER TABLE changes the definition of an existing table. Add column forms a new column/field in the table with the same syntax as CREATE table. The ALTER column form allows you to set or remove the default (value) from the column/field. Note The default (value) is only valid for newly inserted rows. The RENAME clause can change the name of a table or column/field without affecting any data in the related table. Therefore, the table or column/field will remain the same size and type after this command is executed. The Add Table constraint definition clause adds a new constraint to the table with the same syntax as the CREATE table.

If you want to change the properties of a table, you must be the owner of the table.

Attention
The COLUMN keyword is superfluous and can be omitted.
If "*" is followed by a table name, it means that the command operates on the table and all tables that have a lower inheritance level than the table; By default, this property (change) does not increase to any child tables or modifies the related names of any child tables. This should always be the case when adding or modifying the attributes of a superior table, which inherits the highest level of the table. Otherwise, queries made at the inheritance level like the following

SELECT Newcolumn from superclass*
will not work because the child table has one less attribute than the parent table.
In the current implementation, the default (value) and constraint clauses for the new column/field are ignored. You can then set the default (value) with the Set default form of ALTER TABLE. (You also have to update the existing row to the default value with update.) ）

In the current implementation, only the FOREIGN KEY constraint can be added to the table. To create or delete a unique constraint, you can create a unique index (see CREATE Index). To increase the check (check) constraint, you need to rebuild and overload the table with the arguments that are other parameters of the CREATE Table command.

To modify the structure of a table, you must be the owner of the table. Changing any part of the system table structure is not allowed. More information about inheritance is available in the PostgreSQL user's manual.

Please refer to the CREATE TABLE section for more valid parameter descriptions.

Usage
Add a VARCHAR column to the table:
ALTER TABLE Distributors ADD COLUMN address VARCHAR (30);
To rename an existing column:
ALTER TABLE Distributors RENAME COLUMN address to City;
To rename an existing table:
ALTER TABLE Distributors RENAME to suppliers;
Add a FOREIGN KEY constraint to the table:
ALTER TABLE Distributors ADD CONSTRAINT DISTFK FOREIGN KEY (address) REFERENCES addresses [address] MATCH full
Compatibility

The Sql92add COLUMN form is compatible except for the default (value) and constraints mentioned above. The ALTER COLUMN form is fully compatible.
SQL92 a number of additional postgres features that are not currently directly supported by ALTER TABLE:

ALTER table Table DROP CONSTRAINT CONSTRAINT {RESTRICT | CASCADE}
Add or remove constraints on the table (such as CHECK constraints, unique constraints, or foreign KEY constraints). To create or delete a unique constraint, to create or delete a unique index, to modify other types of constraints, you need to rebuild and reload the table, using the other parameters of the CREATE Table command.
For example, delete any constraints on table distributors:

CREATE TABLE Temp as SELECT * from distributors;
DROP TABLE Distributors;
CREATE TABLE Distributors as SELECT * from temp;
DROP TABLE temp;
ALTER table Table DROP [column] column {RESTRICT | CASCADE}
Before, to delete an existing column, the table must be re-created and reloaded:
CREATE TABLE Temp As SELECT did, city from distributors;
DROP TABLE Distributors;
CREATE TABLE Distributors (
Did DECIMAL (3) DEFAULT 1,
Name VARCHAR not NULL,
);
INSERT into Distributors SELECT * from temp;
DROP TABLE temp;
Renaming columns/fields and table names is an PostgreSQL extension. SQL92 did not provide these.