Selected from hanghwp's Blog
1. How to implement SQL database backup and recovery in ASP!
Answer: ASP online backup sql server database:
1. Backup
<%
SQL="backup database 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 successful!"
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 successful!"
end if
%>
Note: The above statement is to back up the data to the backup directory of the disk, and the file name is backuptext.dat.
2. Can the SQL database structure be modified in ASP?
Answer: ALTER TABLE
name
ALTER TABLE — alter 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
The name of the existing table that is being attempted to be changed www.downcodes.com .
column
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
New constraint definition for 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.
describe
ALTER TABLE changes the definition of an existing table. The ADD COLUMN form adds a new column/field to the table using the same syntax as CREATE TABLE. The ALTER COLUMN form allows you to set or remove defaults (values) from columns/fields. 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 related table. Therefore, the table or columns/fields will still be 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.
If you want to change the properties of a table, you must be the owner of the table.
Notice
The COLUMN keyword is redundant and can be omitted.
If "*" follows a table name, it means that the command will operate on the table and all tables with inheritance levels lower than this table; by default, this attribute (change) will not be added to any child table or modify any The relative name of the child table. This should always be done when adding or modifying attributes of a parent table. Otherwise, a query like the following at the inheritance level
SELECT NewColumn FROM SuperClass*
will not work because the child table will have one less attribute than the parent table.
In the current implementation, default (value) and constraint clauses for new columns/fields are ignored. You can later set the default (value) using the SET DEFAULT form of ALTER TABLE. (You also have to use UPDATE to update existing rows to the default values.)
In the current implementation, only FOREIGN KEY constraints can be added to the table. To create or delete a unique constraint, create a unique index (see CREATE INDEX). To add a check constraint, you need to rebuild and reload the table using the 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. The PostgreSQL User's Manual has more information about inheritance.
Please refer to the CREATE TABLE section for a description of the valid parameters.
Usage Add a VARCHAR column to the table:
ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
Rename an existing column:
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 form is compatible, except for the defaults (values) and constraints mentioned above. The ALTER COLUMN form is fully compatible.
SQL92 declares some additional functionality for ALTER TABLE that Postgres does not currently directly support:
ALTER TABLE table DROP CONSTRAINT constraint { RESTRICT | CASCADE }
Add or delete table constraints (such as check constraints, unique constraints or foreign key constraints). To create or drop a unique constraint, and correspondingly create or drop a unique index, and 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, to remove 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 }
Previously, to drop an existing column, the table had to be recreated 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;
Renaming column/field and table names is a PostgreSQL extension. SQL92 does not provide these.