Shulou(Shulou.com)06/01 Report--

1. Database backup and restore between the same SQL Server version (2008 as an example)

(1) Database backup

Backup and restore of databases between the same SQL Server server versions is relatively simple.

First, find the database instance that needs to be backed up, [right]-> [Task]-> [backup.]. Select the backup path under the interface, and click OK. After the backup is successful, you can find the backup file with the suffix .bak under the selected backup path.

(2) Database restore

For the .bak database files that need to be restored, they are usually copied to the

Under the C:\ Program Files\ Microsoft SQL Server\ MSSQL10.MSSQLSERVER\ MSSQL\ Backup\ path.

Then open the local database connection, select "Database", [right]-> [restore Database], "restore Target" Select (or create) the target database, "restore Source" select the source device, add the .bak database file to be restored, and check the "Select backup set for restore" record. There are two nodes in the selection page directory tree on the left side of the interface: general and options. In the option operation interface, "restore database files to" is used to specify that the database and log files that need to be restored will be restored to the specified path, usually C:\ Program Files\ Microsoft SQL Server\ MSSQL10.MSSQLSERVER\ MSSQL\ data. If the path entered is illegal, an error will be reported when restoring the specified database files.

2. Restore the backup database files of the low SQL Server server version (2005 / 2000) to the higher version (2008 as an example) server

Due to the upward compatibility principle of the software, for the lower version of the database file, when restoring to the higher version server, the operation is similar to the same version, so I will not repeat it here.

3. Restore the backup database files of the high SQL Server server version (2008 as an example) to the lower version (2005) server

This situation is relatively troublesome, and you can no longer use conventional backup and restore methods. When backing up a high-version database, you need to export its instance as a SQL script, then create a new database in the lower version, and then create a new query to execute the previously exported SQL script.

(1) how to export the specified database as a SQL script?

Select the database instance to be exported, [right]-> [Task]-> [generate script...], use the generate SQL Server script wizard, "next"-> "next" Select the database instance, and check "script all objects in the selected database"-> "next" to select the script option. There are some details in this interface that need to be noted. There are mainly two options: table / view options and general options. In the table / view options, you need to change "write data script" and "write index script" to true, and the other defaults. If you are not assured, you can check all items in this option to true. In the general options, "script for server version", change the version of the database server that needs to be restored (such as SQL Server 2005)-> [next] output option, select "Save script to File" in script mode, and specify the save path and the file name of the SQL script.

(2) restore the SQL script exported from the high version to the lower version database instance

Create a new database in an earlier version (SQL Server 2005) server, and then export the SQL script before the query interface executes.

Note: sometimes when exporting a database in SQL Server 2008 to a SQL Server 2005 SQL script, there will be some data type unsupported errors, for example, if there is an Date type field in the SQL Server 2008 database, and this data type is not supported in 2005, then an error will occur when exporting the script. The general method is to modify the Date type field to the datetime field supported by 2005.

4. Export the specified table (with data) to a SQL script file

Select the database instance to be exported, [right]-> [Task]-> [generate script...], use the generate SQL Server script wizard, [next]-> [next] Select the database instance-> [next] Select the script option, and change the script to true in the Table / View options. In "General", select the corresponding server version-> [next] Select the object type, check "Table"-> [next] Select the table, check the table to be exported > [next] output option, select "Save script to File" in script mode, specify the save path and SQL script file name.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.