The difference between detached database and attached database

SQL Server provides a variety of database backup and recovery methods such as "detach/attach" database, "backup/restore" database, and replicated database.

Detach/attach method, copy the database file (.MDF) and the corresponding log file (.LDF) to other disks for backup, and then copy these two files to any system that needs this database among.

1. Detaching 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 file (.MDF) and the corresponding The log file (.LDF) is intact. After the separation is successful, we can copy the database file (.MDF) and the corresponding log file (.LDF) to other disks as backups.

2. Attaching a database is to copy the database file (.MDF) and the corresponding log file (.LDF) in a backup disk to the required computer, and add it to a SQL Server database server. , the server manages and uses this database.

Process steps:

1. Detach the database

1. After starting SSMS and connecting to the database server, expand the server node in the Object Explorer. Find the name of the database that needs to be separated under the database object. Here we take the AppCtrl database as an example. Right-click the AppCtrl database and select Properties from the pop-up shortcut menu.

2. Select the "Options" object in the area below the "Selection Page" on the left side of the "Database Properties" window, and then find "Other Options" in the "Other Options" list in the right area. Status" item, click the "Restrict access" text box, and select "SINGLE_USER" in its drop-down list.

3. After clicking the "OK" button in the above picture, a message box will appear, informing us that this operation will close all connections to this database. Do you want to continue this? Operation (as shown below). Note: In a large database system, it is a dangerous action to disconnect other connections to the database at will, because we have no way of knowing what the application connected to the database is doing. Maybe what was disconnected was an application that was performing a complex update operation on the data. and have been running for a long time.

#4. After clicking the "Yes" button, "Single User" is added and displayed after the database name (as shown below). Right-click the database name and select the secondary menu item "Detach" of "Task" in the shortcut menu. The "Detach Database" window shown in the figure below appears.

The name of the database we want to separate is listed in the separate database window in the figure below. Please select the "Update Statistics" checkbox. If there is no active connection shown in the "Message" column, the "Status" column displays "Ready"; otherwise, "Not Ready" is displayed. At this time, the check box in the "Delete Connection" column must be checked.

#5. After the separation database parameters are set, click the "OK" button at the bottom to complete the separation operation of the selected database. At this time, the database name AppCtrl that was just separated cannot be seen in the database object list of the object explorer.

2. Attach the database

1. Attach the database files and logs that need to be attached Copy the file to an already created folder.

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

#3. Click the Add button to add the file with the suffix .MDF.

4. Complete by default.

The above is the detailed content of The difference between detached database and attached database. For more information, please follow other related articles on the PHP Chinese website!