How to Implement SQL database backup and recovery in ASP! Select a blog from hanghwp

1. How to back up and restore SQL databases in ASP!
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.exe cute SQL
On Error resume next
If err <> 0 then
Response. Write "error:" & err. descripting
Else
Response. Write "Data Backup successful! "
End if
%>

2. Recovery
<%
SQL = "Restore 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.exe cute SQL
On Error resume next
If err <> 0 then
Response. Write "error:" & err. descripting
Else
Response. Write "data recovery successful! "
End if
%>

Note: The preceding statement backs up data to the backup directory of the disk. The file name is backuptext. dat.

2. Can I modify the SQL database structure in ASP?
A: ALTER TABLE
Name
Alter table-Modify Table attributes
Syntax
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
Name of the existing table to be modified.
Column
Existing or new column names.
Type
The type of the new column.
Newcolumn
New name of the existing column.
Newtable
The new name of the table.
Table constraint Definition
The new constraint definition of the table.

New Table constraint for the table

Output
Alter
Information returned from the renamed column or table.
Error
Information returned if a column or table does not exist.
Description
Alter table changes the definition of an existing table. Add column to add a new column/field to the table using the same syntax as create table. Alter column allows you to set or delete the default value from the column/field ). Note that 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 relevant table. Therefore, the table or column/field will be of the same size and type after this command is executed. The Add Table constraint definition clause adds a new constraint to the table using the same syntax as create table.

To change the attributes of a table, you must be the table owner.

Note:
The column keyword is redundant and can be omitted.
If "*" is followed by a table name, this command is used to operate the table and all tables with lower inheritance levels than the table. If this command is missing, this attribute (changed) does not add any sub-tables or modify the names of any sub-tables. This is always the case when you add or modify the attributes of a parent table (: a table with a higher inheritance level. Otherwise, the following queries are performed at the inheritance level.

Select newcolumn from superclass *
It will not work, because the sub-table will have one attribute less than the upper-level table.
In the current implementation, the default (value) and constraint clause of the new column/field are ignored. You can then set the default value in the Set default form of alter table ). (You have to use update to update existing rows to the default value .)

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 add check constraints, you need to recreate and reload the table. The parameters used are other parameters of the create table command.

To modify the table structure, you must be the owner of the table. You cannot change any part of the system table structure. The PostgreSQL User Manual contains more information about inheritance.
 

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 );
Rename existing columns:
Alter table distributors rename column address to city;
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 format is compatible, except the default values and constraints mentioned above. The alter column format is fully compatible.
Sql92 declares some additional features not directly supported by S for alter table:

Alter table table drop constraint {restrict | cascade}
Add or delete table constraints (such as check constraints, unique constraints, or foreign key constraints ). To create or delete a unique constraint, create or delete a unique index. To modify other types of constraints, you need to recreate and reload the table and use other parameters of the create table command.
For example, to delete any constraints of the distributors table:

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}
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 (40) not null,
);
Insert into distributors select * from temp;
Drop table temp;
The rename column/field and table name are PostgreSQL extensions. Sql92 does not provide these.