SQL Server database backup and restore

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

(1) Database backup

Backup and restore operations for databases between the same SQL Server version are relatively straightforward.

First find the database instance that needs to be backed up, "right", "Tasks", "Backup ...", select the backup path below the interface, click OK, after the backup is successful, you can find the backup file with the suffix named. bak under the selected backup path.

(2) Database restore

For a. bak database file that needs to be restored, it is typically copied to

C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\backup\ the path below.

Then open the Local database connection, select Database, right-click Restore Database, restore destination select (or create) the target database, restore source Select the source device, add the. bak database file that needs to be restored, tick the "Select the backup set to restore" record. There are two nodes in the Select a page directory tree on the left side of the interface: general, Options. In the option operator interface, restore database files to is used to specify the database to be restored and the log files to be restored to the specified path, typically C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data, if the input is not valid, you will be given an error when restoring the specified database file.

2. Backup database file for low SQL Server version (2005/2000), revert to high version (2008 for example) server

Because of the software's up-to-date compatibility principle, for a lower version of the database file, the operation is similar to the same version when restoring to a high-version server.

3, the High SQL Server version (2008 for example) of the backup database file, restore to the lower version (2005 for example) server

This is a relatively cumbersome situation, and you can no longer use regular backup and restore methods, you need to export your instance to SQL script when backing up a high-version database, then create a new database in the lower version, and then create a new query to execute the SQL scripts that you exported earlier.

(1) How do I export a specified database as a SQL script?

Select the DB instance you want to export, right-click Tasks, build Script ..., use the Generate SQL Server Script Wizard, Next, Next, select the DB instance, and under the interface, tick "script all objects in the selected database"- > Next Select the Scripting option, which has a few details to note, here are mainly two options: Table/View options and General options, in the table/view options, you need to change the "Write Data Script" and "Write Index script" to True, other default can, if not rest assured, You can check all the items in this option to true, and in the general options, "Scripting for server versions", change the "next" output option that requires restoring the version of the database server (for example, SQL Server 2005), select "Save script to File" in script mode. Specify the save path and SQL script file name.

(2) Restore a high-version exported SQL script to a low-version db instance

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

Note: Sometimes when you export a database in SQL Server 2008 as a SQL Server 2005 SQL script, there are some data types that do not support errors, such as when a Date Type field exists in a SQL Server 2008 database. This data type is not supported in 2005, then an error occurs when exporting the script, and the general method is to modify the Date Type field to 2005 of the supported DateTime fields.

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

Select the DB instance you want to export, right-click Tasks, build Script ..., use the Generate SQL Server Script Wizard, Next, Next, select the DB instance, next select the Script option, in table/view options, Script that writes data to True, "scripts written for server version" In general, select the corresponding server version, "Next" Select object type, tick "table", "Next" Select Table, tick the table that needs to be exported > "Next" Output option, in script mode select " Save the script to a file, specifying the save path and the SQL script file name.

This article is from the "Sharemi" blog, make sure to keep this source http://sharemi.blog.51cto.com/11703359/1790655

SQL Server database backup and restore