Summary of how MSSQL2012 attaches database errors

MSSQL2012 append database 5173, error handling

When you attach a ms SQL SERVER database, you may encounter errors of 9003 or 9004, which are usually caused by mismatch between log files and data files. It is best to restore the backup file directly with backup. If there is no backup, it is still relatively simple to solve this problem. This method also applies to databases with no log files attached.

The general idea is:

Create a database with the same name
Stop SQL service
Overwrite the MDF file, delete the log file, and then start the SQL service
Change the database from challenge mode to single-user emergency mode
Fix DBCC CHECKDB

The following describes the specific operation details. Steps 1st, 2, and 3 do not need to be said. Starting from Step 4, we assume that an error is reported when a database named testdb is attached.

Step 3: overwrite the file. After the SQL service is restarted, the database will be in the "questionable" status. Create a new query and execute the following SQL statement,

USE MASTERGOexec sp_configure 'allow updates', 1 GOreconfigure with overrideGOALTER DATABASE testdb SET SINGLE_USERWITH rollback immediate; goalter databas 'testdb' SET EMERGENCYGO

After the execution is complete, run the following query to restore the database:

Dbcc checkdb ('testdb', REPAIR_ALLOW_DATA_LOSS)

Then execute the following SQL statement to bring the database online.

Alter database testdbSET MULTI_USERWITH rollback immediate; goalter database testdb SET ONLINE

MSSQL2012 attaching database 5120 error (Access Denied) handling method

The following error is prompted when MSSQL attaches a database:

The physical file "***. mdf" cannot be opened ". Operating system error 5: "5 (access denied .)". (Microsoft SQL Server, error: 5120)

This experience describes how to handle the error and then attach the database.

Attach database method

1. Open SQL Server Management Studio and connect to the database. Right-click "database" and choose "add" from the shortcut menu.

2. Click "add" on the additional database page to browse the database files to be attached.

3. After selecting the database file to be appended, the data file and log file of the database are displayed in the database details list.

4. Click "OK" on the additional database page to report an error.

Error message:

The physical file "***. mdf" cannot be opened ". Operating system error 5: "5 (access denied .)". (Microsoft SQL Server, error: 5120)

Modify the access permission of the database file to be attached

Right-click the database file to be appended. In the displayed Properties dialog box, select the Security tab.

Find the Authenticated Users user name.

If not, add the Authenticated Users user name.

Add the Authenticated Users user name.

1. Click the edit button on the Security tab, and then click add to display the select user or group dialog box.

2. Click "Advanced" and then click "search now" on the page. All users and groups are displayed in the search results.

3. Select the Authenticated Users user and click OK. The Authenticated Users user name is displayed in the select user or group dialog box.

4. Click OK.

The Authenticated Users user is displayed in the user group on the Security tab. Assign full control permissions to Authenticated Users.

4. Attach the database again. The database is attached successfully.