Today, the editor will share with you a detailed tutorial on backup and recovery of SQL in Asp. Interested friends, let’s learn about it with the editor!
1. Backup
SQL="backupdatabase database name todisk='"&Server.MapPath("backup")&"/"&"backuptext.dat"&"'"
setcnn=Server.createobject("adodb.connection")
cnn.open"driver={SQLServer};Server=server name;uid=sa;pwd="
cnn.executeSQL
onerrorresumenext
iferr0then
response.write"Error:"&err.Descripting
else
response.write "Data backup succeeded!"
endif
%>
2. Recovery
SQL="Restoredatabase database name fromdisk='"&Server.MapPath("backup")&"/"&"backuptext.dat"&"'"
setcnn=Server.createobject("adodb.connection")
cnn.open"driver={SQLServer};Server=server name;uid=sa;pwd="
cnn.executeSQL
onerrorresumenext
iferr0then
response.write"Error:"&err.Descripting
else
response.write "Data recovery succeeded!"
endif
%>
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 SQL database structure be modified in ASP?
Answer: ALTERTABLE
name
ALTERTABLE—Change table properties
grammar
ALTERTABLEtable[*]
ADD[COLUMN]columntype
ALTERTABLEtable[*]
ALTER[COLUMN]column{SETDEFAULTvalueDROPDEFAULT}
ALTERTABLEtable[*]
RENAME[COLUMN]columnTOnewcolumn
ALTERTABLEtable
RENAMETOnewtable
ALTERTABLEtable
ADDtableconstraintdefinition
Inputs
table
The name of the existing table that was attempted to change.
column
Existing or new column name.
type
Type of the new column.
newcolumn
New name of the existing column.
newtable
The new name of the table.
tableconstraintdefinition
New constraint definition for tables.
Newtableconstraintforthetable
Output
ALTER
Information returned from the renamed column or table.
ERROR
If a column or table does not exist, the returned information is returned.
describe
ALTERTABLE changes the definition of an existing table. The ADDCOLUMN form adds a new column/field to the table with the same syntax as CREATETABLE. The ALTERCOLUMN form allows you to set or delete 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 column/field will still be of the same size and type after this command is executed. The ADDtableconstraintdefinition clause adds a new constraint to the table with the same syntax as CREATETABLE.
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 "*" is followed by a table name, it means that the command is to operate on the table and all tables with inheritance levels below that table; by default, the attribute (change) will not be added to any subtable or the relevant names of any subtable are modified. This should always be the case when adding or modifying the attributes of a superior table (translation note: tables with high inheritance levels). Otherwise, the query at the inheritance level is carried out like the following
SELECTNewColumnFROMSuperClass*
Will not work because the subtable will have one less attribute than the previous table.
In the current implementation, the default (value) and constraint clauses of new columns/fields are ignored. You can then set the default (value) using the SETDEFAULT form of ALTERTABLE. (You also have to use UPDATE to update existing rows to default values.)
In the current implementation, only the FOREIGNKEY constraint can be added to the table. To create or delete a unique constraint, you can create a unique index (see CREATEINDEX). To add a check (check) constraint, you need to rebuild and overload the table, using the parameters used by other parameters of the CREATETABLE command.
To modify the structure of a table, you must be the owner of the table. No changes to any part of the system table structure are allowed. There is more information about inheritance in the PostgreSQL user manual.
Please refer to the CREATETABLE section for more descriptions of valid parameters.
usage
Add a VARCHAR column to the table:
ALTERTABLEdistributorsADDCOLUMNaddressVARCHAR(30);
Rename the existing column:
ALTERTABLEdistributorsRENAMECOLUMNaddressTOcity;
Rename the existing table:
ALTERTABLEdistributorsRENAMETOsuppliers;
Add a foreign key constraint to the table:
ALTERTABLEdistributorsADDCONSTRAINTdistfkFOREIGNKEY(address)REFERENCESaddresses(address)MATCHFULL
compatibility
The SQL92ADDCOLUMN form is compatible, except for the default (value) and constraints mentioned above. The ALTERCOLUMN form is fully compatible.
SQL92 declares some additional features that Postgres does not currently directly support for ALTERTABLE:
ALTERTABLEtableDROPCONSTRAINTconstraint{RESTRICTCASCADE}
Add or delete constraints for tables (such as check constraints, unique constraints or foreign key constraints). To create or delete a unique constraint, create or delete a unique index accordingly, to modify other types of constraints, you need to rebuild and overload the table, using other parameters of the CREATETABLE command.
For example, delete any constraints of table distributors:
CREATETABLEtempASSELECT*FROMdistributors;
DROPTABLEdistributors;
CREATETABLEdistributorsASSELECT*FROMtemp;
DROPTABLEtemp;
ALTERTABLEtableDROP[COLUMN]column{RESTRICTCASCADE}
Before, to delete an existing column, the table must be recreated and reloaded:
CREATETABLEtempASSELECTdid, cityFROMdistributors;
DROPTABLEdistributors;
CREATETABLEdistributors(
didDECIMAL(3)DEFAULT1,
nameVARCHAR(40)NOTNULL,
);
INSERTINTOdistributorsSELECT*FROMtemp;
DROPTABLEtemp;
Rename column/field and table names is a PostgreSQL extension. SQL92 does not provide these.
The above is a detailed tutorial on backup and recovery of SQL in Asp. I believe you have learned about it. For more related content, please continue to pay attention to the Wuxin Technology Channel.