SQL Server database detach and attach

I. Overview

SQL Server provides backup and recovery methods for a variety of databases, such as detach/attach database, backup/restore database, copy database, and so on. This article introduces a commonly used "detach/Attach" method in learning, similar to the familiar "file copy" method, that is, the database file (. MDF) and the corresponding log file (. LDF) to the other disk for backup, and then copy the two files to any system that needs the database. For example, in the course of experimental teaching, students often want to move the database they created in the school lab computer to their own computer and do not want to recreate the database, you can use this simple method. However, because of the particularity of database management system, it is necessary to use the tools provided by SQL Server to accomplish the above work, and the direct file copy is not feasible.

This approach involves both the SQL Server detach database and the additional database interoperability tools.

1. Detaching a database is the removal of a database (such as Student_mis) from the list of SQL Server databases so that it is no longer managed and used by SQL Server, but the database file (. MDF) and the corresponding log file (. LDF) are intact. Once the separation is successful, we can put the database file (. MDF) and the corresponding log file (. LDF) Copy to the other disk as a backup save.

2, the additional database is a backup disk of the database files (. MDF) and the corresponding log file (. LDF) to the required computer and add it to a SQL Server database server, which is used by the server to administer and use the database.

Ii. Separation of databases

1. After you start SSMs and connect to the database server, expand the server node in Object Explorer. Under Database objects, find the name of the database that needs to be detached, as an example of the Student_mis database. Right-click the Student_mis database and select Properties from the popup shortcut menu.

2. Select the Options object in the area below the Select page on the left side of the Database Properties window, and then find the status item in the other options list in the right area, click the Restrict Access text box, and choose Single_user in its drop-down list.

3. When the OK button is clicked in, a message box informs us that this operation will close all connections to the database and continue with the operation (for example). Note: In a large database system, it is dangerous to disconnect other connections from the database, because we cannot know what the application is doing on the database, or it may be disconnected from a transaction that is having a complex update operation on the data and has been running for a long time.

4. After clicking the "Yes" button, add "Single user" (for example) after the database name. Right-click the database name and choose task's two-level menu item Detach from the shortcut menu. The Detach Database window appears as shown.

5. The database name that we want to detach is listed in the Detach Database window. Please select the Update STATISTICS check box. If an active connection does not appear in the Message column, the Status column appears ready, otherwise "not ready" and the check box for the Delete connection column must be checked.

6. After separating the database parameter settings, click the OK button at the bottom to complete the detach operation for the selected database. At this point in the Object Explorer's list of database objects, you will not see the database name that was just separated student_mis (for example).

Third, additional database

1. Copy the database files and log files that you want to attach to an already created folder. For instructional purposes, we copy the file to the Directory data folder that was generated when we installed SQL Server.

2. In the window, right-click the database object and choose Attach 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 that locates the database file, where you will locate the database file directory that you just copied to the Data folder in SQL Server (it does not have to be placed in the "Files" directory). Select the database file (suffix) you want to attach. MDF, for example).

4. Click the "OK" button to complete the setup of the attached database file. At this point, the Additional Database window lists information (such as) that requires the database to be attached. If you need to modify the attached database name, modify the database name in the Append as text box. We use the default values here, so click the OK button to complete the database's additional tasks.

By doing this, we can see the database Student_mis (for example) just attached in SSMs's Object Explorer.

As you can see, the method of detaching and attaching a database is useful if you want to migrate a database to a different SQL Server instance of the same computer or to a SQL Server system on another computer.

SQL Server database detach and attach