SQL Server Database detach and attach (graphics tutorial) _mssql

I. Overview

SQL Server provides backup and recovery methods for multiple databases, such as the detach/attach database, the backup/restore database, the replication database, and so on. This article introduces a common "detach/Attach" method in learning, similar to the familiar "file copy" method, that is, the database file (. MDF) and corresponding log files (. LDF) are copied to other disks for backup, and then copies the two files to any system that requires the database. For example, in the course of experimental teaching, students often want to use the database that they created in the school laboratory computer to move to their computer without recreating the database, so this simple method can be used. However, because of the particularity of database management system, it is necessary to use the tools provided by SQL Server to complete the above work, and direct file copy is not feasible.

This approach involves two inverse operating tools, SQL Server detach database and additional database.

1. Detaching a database is removing 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. After the separation is successful, we can put the database file (. MDF) and corresponding log files (. LDF) are copied to other disks to be saved as backups.

2, the additional database is a backup disk of the database files (. MDF) and the corresponding log file (. LDF) are copied to the desired computer and added to a SQL Server database server, which is used by the server to manage and use the database.

ii. separation of databases

1. After starting ssms and connecting to the database server, expand the server node in Object Explorer. In the database object, locate the name of the database you want to detach, taking the Student_mis database as an example. Right-click the Student_mis database and select Properties from the shortcut menu that pops up.

2. On the left of the Database Properties window, select the Options object in the area under selection, and then locate the status item in the other options list in the right area, click the Restrict Access text box, and select Single_user in its Drop-down list.

3. When you click the OK button in the previous illustration, a message box informs us that this operation will close all connections to this database and continue this operation (figure below). Note: in a large database system, it is risky to disconnect from any other connection to the database because we cannot know what the application that is connecting to the database is doing, or what is being disconnected is a transaction that is undergoing a complex update of data and has been running for a long time.

4. After the "Yes" button is clicked, the database name is appended with a "single user" (as shown below). Right-click the database name and select "Detach" from the two-level menu item in the shortcut menu. The Detach Database window appears as shown in the following illustration.

5. The database name we want to detach is listed in the Detach Database window of the following illustration. Please select the Update STATISTICS check box. If an active connection is not displayed in the Message column, the Status column appears ready, or if it is not ready, you must select the check box for the Delete connection column.

6. After the separation of the database parameter settings, click on the bottom of the "OK" button to complete the selected database separation operation. In this case, you will not see the database name Student_mis in the Object Explorer's list of database objects (pictured below).

Third, additional database

1. Copy the database files and log files that need to be attached to a folder that has already been created. For instructional purposes, we copy the file to the Directory data folder that was generated when you installed SQL Server.

2. In the following illustration window, right-click the database object and select Attach on the shortcut menu to open the Attach Database window.

3. In the Additional Database window, click the Add button in the middle of the page to open the window where you want to locate the database file, locating the database file directory in the data folder that you just copied to SQL Server (the data file does not have to be in the "Data" directory). Select the database file (suffix) to attach. MDF, as shown below).

4. Clicking the OK button completes the setup of the additional database file. In this case, the Additional Database window is listed with information that needs to be attached to the database (pictured below). If you need to modify the appended database name, modify the database name in the Attach as text box. We use the default values here, so click the OK button to complete the additional task for the database.

By doing so, we can see the database Student_mis just attached in the SSMs Object Explorer (pictured below).

The above shows how it is useful to detach and attach a database 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.