SQL Server database separation and appending (Graphic tutorial)

I. Overview

SQL Server provides backup and recovery methods for "detach/Attach" databases, "backup/restore" databases, and duplicate databases. This section describes a commonly used "separation/attachment" Method for learning, similar to the familiar "file copy" method, that is, the database file (. MDF) and the corresponding log file (. LDF) copy to another disk for backup, and then copy the two files to any system that requires the database. For example, during the experiment teaching process, students often want to migrate the database they created in the school lab computer to their own computer instead of re-creating the database, you can use this simple method. However, due to the particularity of the database management system, you must use the tools provided by SQL Server to complete the above work.Direct file copying does not work..

This method involves two reciprocal operation tools, SQL Server detaches the database and attaches the database.

1. separating a database is to delete a database (such as student_Mis) from the SQL Server database list so that it is no longer managed and used by SQL Server, but the database files (. MDF) and the corresponding log file (. LDF) intact. After successful separation, we can copy the database file (. MDF) and the corresponding log file (. LDF) to another disk for backup and storage.

2. attaching a database is to back up a database file in the disk (. MDF) and the corresponding log file (. LDF) copy to the desired computer and add it to an SQL Server database Server. The Server manages and uses the database.

2. Database Separation

1. After SSMS is started and connected to the database server, expand the server node in the object Resource Manager. Find the name of the database to be detached under the database object. The student_Mis database is used as an example. Right-click student_Mis database and choose Properties From the shortcut menu.

2. in the "database properties" window, select the "option" object in the "Select page" area on the left, and find the "status" item in the "Other Options" list on the right, click the "restricted access" text box and select"SINGLE_USER".

3. After you click "OK", a message box is displayed, notifying us that this operation will close all connections to the database and whether to continue this operation (for example ). Note:In a large database system, it is dangerous to disconnect other databases at will.Because we cannot know what the application connecting to the database is doing, it may be a transaction that is performing complex update operations on the data and has been running for a long time.

4. Click the "yes" button, and a "single user" (for example) will be added to the database name ). Right-click the Database Name and choose "separate" from the shortcut menu ". The "detach Database" window is displayed.

5. The names of the databases to be detached are listed in the split Database window. Select the "update statistics" check box. If no active connection exists in the "message" column, the "status" column is displayed as "ready"; otherwise, "not ready" is displayed ", in this case, you must select the check box in the "Delete Connection" column.

6. After the database separation parameters are set, click "OK" at the bottom to complete the database separation. In this case, the Database Object List of the object Resource Manager cannot see the database name student_Mis that was just separated (for example ).

Iii. Additional database

1.Copy the database files and log files to a created folder.. For teaching purposes, we copied the file to the directory DATA folder generated when installing SQL Server.

2. In the window, right-click the database object and select the "Attach" command from the shortcut menu to open the "Attach Database" window.

3. In the "Attach Database" window, click the "add" button in the middle of the page to open the window for locating database files, in whichLocate the database file directory in the DATA folder just copied to SQL Server (the DATA file does not have to be placed in the "DATA" Directory)Select the database file to be appended (suffix. MDF, for example ).

4. Click "OK" to complete the settings of the attached database file. In this case, the information about the database to be appended (for example) is listed in the additional database window ). If you need to modify the name of the attached database, modify the name of the database in the "Add as" text box. The default value is used here. Therefore, click OK to complete the additional database tasks.

After completing the above operations, we can see the attached database student_Mis (for example) in the object Resource Manager of SSMS ).

The preceding operations show that if you want to migrate a database to different SQL Server instances on the same computer or the SQL Server systems on other computers, it is useful to separate and attach databases.