Ошибка 5173 sql

Introduction

I had a colleague who experienced issues attaching a SQL Server database and encountered SQL database error 5173 – Attach database failed to Server:

Error 5173: Cannot associate files with different databases.

Attach database failed for Server Serv4567′.  (Microsoft.SqlServer.Smo)

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5173)

This error-prone database file was an important marketing database actually and the customers were complaining because they could not access the database which my colleague was trying to migrate to a new Server with more RAM, a nice cluster server, and powerful hard drives. This is a famous attach error that can occur in a database.

Requirements

The following requirements will help you to follow this article:

1. This article will use SQL Server in any version.
2. Finally, you will need SQL Server Management Studio.

Getting started

Here we will check the different solutions available—

In databases we have 3 different types of files for SQL Server:

1. The primary data file with .MDF extension is the main file and contains the data.
2. The secondary data file (.NDF) is not created by default but can be created if you want to separate the data in different hard drives for security and performance reasons.
3. The log file (.LDF) stores the log information. It is used to recover the database. It is possible to recover the data to a specific time.

The sp_helpfile system stored procedure shows all the files in a specific database. It will show the size, path, filegroup, and id.

Syntax:

sp_helpfile [ [ @filename= ] 'name' ]

Where, [ @filename = ] ‘name’ is the logical name of any file in the current database.

The problem is usually that the mdf file does not have the same date as that of .LDF file. For example, when a mdf file of June 2 tries to be restored using the ldf file of June 3.

A possible solution to fix SQL database error 5173

If log file is missing or corrupt

Once recovered, if you have the 5173 error and you cannot find the correct log file (.LDF), it is possible to rebuild a .LDF file for the .MDF file restored. The following T-SQL code will help you to rebuild the log file for the .MDF file:

CREATE DATABASE yourdatabase 
ON (FILENAME = 'c:yourPrimaryFile_Data.mdf') 
FOR ATTACH_REBUILD_LOG ;

The code will create a database based on the mdf file and generate a new log file for the mdf file. For attach_rebuild_log creates a new log for you.

If database is corrupt

If your primary data file does not match with the log file and your primary data file is corrupt, you can do the following:

• Verify that the primary file belongs to the log file. Sometimes you are attaching the wrong log file with the primary file.
• Recover the damaged primary data file using Stellar Repair for MS SQL to repair corrupt SQL database primary and secondary file.

You can download the software from here:

• This is the main interface of Stellar Repair for MS SQL

• Select the MDF file which you want to repair

• Click Repair to start scanning and repair process

• Check preview of database

• Stellar Repair for MS SQL software restores your .MDF file, detect your SQL Server database objects and you can export the data to SQL Server (MDF), Excel, CSV, HTML.

The software requires to have the MS SQL Service Stopped before initiating the SQL database recovery process. You can restore the .MDF file in any machine with or without MS SQL Server.

Conclusion

The error 5173 in Microsoft SQL Server is related to a problem with the primary data file and the log file. Due to some accident, the files do not match and the SQL Database assumes that the files belong to different databases.

If the primary data file is corrupt, you can use Stellar Repair for MS SQL. This software can repair a corrupt database using simple software for that.  Once repaired (if necessary), you can rebuild the log file using the create database sentence and use the for attach_rebuild_log. This option will generate a new log for you.

Facing Microsoft SQL Error 5173? Know How to Solve This Server Error

Jason Clark ~
Modified: March 3rd, 2023 ~ SQL ~ 6 Minutes Reading

If you are an SQL Server user, you may come across any error while performing your everyday task. Errors can occur due to different reasons and separate solutions are recommended for each of the errors. The error in discussion today is SQL error 5173. In this post, we will learn the reasons and solutions to SQL Server error 5173 attach database. But first, check out the user queries regarding this error found on tech forums.

Table of Contents

• User Queries
• SQL Error 5173?
• Causes of the Error
• How to Fix the Error
• Best Solution Present
• The Concluding Words

User Queries

User Query 1: “A few days ago, It attempted to transfer my data to another SQL Server. So, I have performed these steps: first I detached the database, then I copied my MDF file (not LDF) to the new Server. At this point, I created a new database with the original DB name in the new Server.

Then I detached the new database and replaced the new MDF file the old (original) MDF file. During the process, I got SQL error number 5173 that says: “cannot associate files with different databases.” please tell me why I am getting this message and what is the solution.”

User Query 2: “A hard disk containing a large sized database had some corruption issues. As there was no backup available, we had to take the help of a freelance technician to recover the database.

After the MDF and LDF files have been recovered, we tried to attach those and failed. Instead, MS SQL error 5173 is what we received. Why are we getting this error message? Can anyone explain its reasons and solutions?”

What Is MS SQL Error 5173?

The Microsoft SQL Server error 5173 occurs due to a problem with both the Primary and Transaction log file. Due to some sort of error, the files don’t match at all. Therefore, the SQL Database thinks that data files belong to different databases & different servers. In case of primary data file corruption, you can use the automated MS SQL Repair Tool.

MS SQL Server Error 5173 – Reasons

If we check carefully, we will find that this error appears because the files being used by the database belong to different databases. This is basically a safety mechanism that prevents accidental overwriting of existing files. That is why during the startup, database checks if all files are from same database and if not, it shows this error message. If the database is corrupt or it is in Recovery Pending state, this error message will get displayed.

This error message appears due to various reasons. The major causes are corrupt MDF file and files used in database are not from the same database. Besides, power failure, insufficient storage space, and defective hardware can be responsible for this error message.

How to Fix MS SQL Error 5173

Depending on the cause, users need to adopt remedial methods to fix this error.

• Method 1: Use DBCC CHECKPRIMARYFILE Command

To get rid of this problem, users need to apply DBCC CHECKPRIMARYFILE command to know what is in the primary file header. It is because when a database is attached, reading MDF file information is not feasible. Hence, comes the requirement of DBCC command to open and read the primary file header. To apply the command, use these steps & solve Microsoft SQL server error 5173 attaching database issue:

a. To begin with, users have to create a database that contains two data files and a log file.

b. Then detach the database. Use this command for DB detachment.

c. Run DBCC CHECKPRIMARYFILE command to open file header and read the detailed information.

d. Since the first file is the primary database file, we will get output for that file only (the output will be 1).

e. This command will let users read the properties information from the file header.

Option 1. This displays the file and filegroup ID, physical name, logical name of the files that belong to the same database.
Option 2. It shows the original database where the file belongs, along with database version and collation.
Option 3. This is actually the subset of option no. 1.

• Method 2: Change Recovery Pending State of Database

a. Run this script to set the DB in emergency mode:

 ALTER DATABASE [dbname] SET EMERGENCY

b. Make the database available for multiple users:

 ALTER DATABASE [dbname] SET MULTI_USER

c. Detach the database and then re-attach:

 EXEC sp_detach_db '[dbname]' EXEC sp_attach_single_file_db @dbname = '[dbname]', @physname = N'[mdf path]'

• Method 3: Make Corrupt Database Healthy

If the database has gone corrupt, run this script to repair it:

The Best Solution to Solve Microsoft SQL Error 5173

As this command will result in data loss, experts do not recommend this method. Therefore, it is better to use SQL MDF File Repair Tool. This utility can fix all types of corrupt SQL database without any data loss. Using this application, which is compatible with all latest versions of SQL Server, users can also retrieve deleted database files.

Download Now Purchase Now

Step-1. Launch the Tool to solve Microsoft SQL server error 5173 first.

Step-2. Select the Quick or Advanced Scan for database migration.

Step-3. Preview the Database Files & then Go to Export options.

Step-4. Select the Destination Path & Set Export Settings here.

Step-5. Finally, Click on the Export button to get the desired results.

Concluding Words

Here, we discussed all the aspects of SQL error 5173 including its causes and cures. We have provided multiple solutions that can be implemented to get rid of this error for good. We expect that users can follow the guideline described here and get the desired result.

In a nutshell, we can say that it’s quite easy for users to solve this Microsoft SQL server error 5173 with the automated solution. Apart from that, the manual solution works but it is hectic. In order to stay away form hassles, the modern method is the right choice for users. It can be the best choice to solve this critical SQL server error.

Introduction

I had a colleague who experienced issues attaching a SQL Server database and encountered SQL database error 5173 – Attach database failed to Server:

Error 5173: Cannot associate files with different databases.

Attach database failed for Server Serv4567′.  (Microsoft.SqlServer.Smo)

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5173)

This error-prone database file was an important marketing database actually and the customers were complaining because they could not access the database which my colleague was trying to migrate to a new Server with more RAM, a nice cluster server, and powerful hard drives. This is a famous attach error that can occur in a database.

Requirements

The following requirements will help you to follow this article:

1. This article will use SQL Server in any version.
2. Finally, you will need SQL Server Management Studio.

Getting started

Here we will check the different solutions available—

In databases we have 3 different types of files for SQL Server:

1. The primary data file with .MDF extension is the main file and contains the data.
2. The secondary data file (.NDF) is not created by default but can be created if you want to separate the data in different hard drives for security and performance reasons.
3. The log file (.LDF) stores the log information. It is used to recover the database. It is possible to recover the data to a specific time.

The sp_helpfile system stored procedure shows all the files in a specific database. It will show the size, path, filegroup, and id.

Syntax:

sp_helpfile [ [ @filename= ] 'name' ]

Where, [ @filename = ] ‘name’ is the logical name of any file in the current database.

The problem is usually that the mdf file does not have the same date as that of .LDF file. For example, when a mdf file of June 2 tries to be restored using the ldf file of June 3.

A possible solution to fix SQL database error 5173

If log file is missing or corrupt

Once recovered, if you have the 5173 error and you cannot find the correct log file (.LDF), it is possible to rebuild a .LDF file for the .MDF file restored. The following T-SQL code will help you to rebuild the log file for the .MDF file:

CREATE DATABASE yourdatabase 
ON (FILENAME = 'c:yourPrimaryFile_Data.mdf') 
FOR ATTACH_REBUILD_LOG ;

The code will create a database based on the mdf file and generate a new log file for the mdf file. For attach_rebuild_log creates a new log for you.

If database is corrupt

If your primary data file does not match with the log file and your primary data file is corrupt, you can do the following:

• Verify that the primary file belongs to the log file. Sometimes you are attaching the wrong log file with the primary file.
• Recover the damaged primary data file using Stellar Repair for MS SQL to repair corrupt SQL database primary and secondary file.

You can download the software from here:

• This is the main interface of Stellar Repair for MS SQL

• Select the MDF file which you want to repair

• Click Repair to start scanning and repair process

• Check preview of database

• Stellar Repair for MS SQL software restores your .MDF file, detect your SQL Server database objects and you can export the data to SQL Server (MDF), Excel, CSV, HTML.

The software requires to have the MS SQL Service Stopped before initiating the SQL database recovery process. You can restore the .MDF file in any machine with or without MS SQL Server.

Conclusion

The error 5173 in Microsoft SQL Server is related to a problem with the primary data file and the log file. Due to some accident, the files do not match and the SQL Database assumes that the files belong to different databases.

If the primary data file is corrupt, you can use Stellar Repair for MS SQL. This software can repair a corrupt database using simple software for that.  Once repaired (if necessary), you can rebuild the log file using the create database sentence and use the for attach_rebuild_log. This option will generate a new log for you.

---

↑
Back to top

---

Introduction

There are different ways of moving a SQL Server database and ‘detaching and reattaching’ remains one of the feasible ways of doing so. Detaching or disengaging something, by definition, means separating or removing it from something that it is connected
to. In SQL Server terminology it refers to separating a database from the SQL instance that it is connected to.

In this article, we will see how ‘detach’ and ‘attach’ operations work and how to migrate a database using this method, gotchas to keep in mind when adopting this method, conditions which might fail this procedure and how the log is rebuilt automatically
and how this new log affects the log backup chain of a database.

↑
Back to top

---

Detaching and re-attaching a database

Simply put, when a SQL database is detached, all the metadata pertaining to the detached database is removed from the SQL instance. The only trace one might be left with is the backup and restore history for that database unless that has already been purged
from the MSDB system database. Even in the event, one is able to retrieve the backup and restore history for it, that information alone would not be sufficient to derive the information about the database contents or structure.

One of the requirements for using this procedure is that there should be no active connections to the database or it will fail. The database being detached can be taken into SINGLE_USER mode WITH ROLLBACK IMMEDIATE to break the currently connected sessions
and roll them back immediately.

---

Detaching a database using sp_detach_db

The SP_DETACH_DB stored procedure is used to cleanly detach a database and the command syntax is given below:

EXEC SP_DETACH_DB <DatabaseName>

One of the benefits of using this method is, SP_DETACH_DB ensures that all dirty data pages are flushed to disk and that there are no uncommitted transactions in the database. The reason is, it needs the database cleanly shut down or it won’t execute. By
not having incomplete transactions in the database, there is no need for the transaction log file to be present in order to re-attach the database because the data files would not have any uncommitted transactions to be rolled back upon re-attaching.

We will see later in this article as to how a database without a transaction log file can be attached using this method. If the log is missing, SQL Server automatically re-builds a transaction log file with the minimum size of 1 MB. Remember, the automatic
rebuilding of transaction log file is possible only when the database is cleanly shut down which is exactly what happens when we run SP_DETACH_DB. As soon as the database is detached, it won’t appear in the SYS.DATABASES system view.

One important thing to note is that the database does not have to be “detached” first (using SP_DETACH_DB) for the “attach” and for the automatic rebuild of the transaction log to work. If the database was cleanly shut down or if there was no activity against
the database when the SQL instance gracefully turned off, it should be able to attach and rebuild the transaction log file automatically, in case it goes missing. That is, the attach post clean SQL restart works exactly as though the database was detached
using sp_detach_db.

On the other hand, if there were ongoing transactions in the database when the SQL instance shut down, then the transaction log file MUST be present for the re-attach to work because SQL Server performs recovery against the database when it is attached.
Hence, the presence of a transaction log file is a must when the database was not cleanly shut down.

↑
Back to top

---

Attaching a detached database

There are two ways to attach a database that is detached. One is to use “FOR ATTACH” option in the “CREATE DATABASE” statement and the second way is to execute “sp_attach_db” stored procedure.

1) FOR ATTACH

This is the supported and the most efficient way to attach a database. It gives the user the control over data and log file paths and can specify 32,767 files and 32,767 filegroups for each database. The syntax for using this method is given below:

It is to be noted that the primary data file contains information about the locations of all other files and as such, must be specified in the<FilesPath>entry. SQL Server would then automatically know where the other files are located. If
the plan is to attach the database to different paths, then provide the new paths in the <FilesPath> entry above otherwise, if unspecified, SQL Server assumes the files to be in last known location.

All data files MUST be available for the attach operation to succeed. Presence of log file is contingent upon whether the database was cleanly shutdown using sp_detach_db or not. As mentioned above, if the database was indeed cleanly shut down using sp_detach_db
or the SQL instance was cleanly shutdown with no dirty pages in memory, then the log file is not required for the attach operation to work. SQL Server, in that case, rebuilds the missing log file when we execute the CREATE DATABASE with FOR ATTACH operation.

One key thing to note here is, FOR ATTACH rebuilds only single log file i.e. if the database being attached had an only one log file that is missing, then FOR ATTACH rebuilds it and updates the information about the new log file in the primary data file.
Whereas, if the database being attached had multiple log files and all are missing, FOR ATTACH fails to rebuild the log file. That is where the other option “ATTACH_REBUILD_LOG” comes into play.

ATTACH_REBUILD_LOG doesn’t have the limitation of rebuilding only single log file. If a database has multiple log files and if all go missing, this option rebuilds the log. If, on the other hand, log files do exist, and we still specify the ATTACH_REBUILD_LOG
option in the CREATE DATABASE statement, SQL Server uses the log files and does not rebuild them. Hence, ATTACH_REBUILD_LOG option is ignored.

It must be noted that if the transaction log is rebuilt upon using the ATTACH_REBUILD_LOG option to attach a database, the log backup chain is broken, and a full backup of the database must be immediately taken after the database is attached. This is demonstrated
in a later example.

2) SP_ATTACH_DB Stored Procedure

Just like SP_DETACH_DB, this stored procedure is used to attach a database. It must be noted that this stored procedure has been deprecated and is not recommended due to its limitations. One of its limitations is, it is limited to only
sixteen files. As mentioned above, FOR ATTACH supports as many as 32,767 files.

Note: This stored procedure was deprecated in SQL 2005 and is not supported. So even if it works on whatever version we are on, we should always consider using the FOR ATTACH method discussed above.

Theofficial documentationsays thatsp_attach_dbstored procedure can only be used on the databases
that were previously detached from the database server by using an explicit sp_detach_db. This isnotalways necessary. As mentioned above, as long as the database was cleanly shutdown and there were no dirty pages in the buffer pool at the
time the SQL instance was taken down, the database is always attachable, and it is as if the database was detached using sp_detach_db.

On the other hand, if the SQL instance was processing a transaction when it went off, in this case, the log file(s) must be present to recover the transaction upon attaching. The log file keeps track of the changes and recovery is performed when the database
is attached to ensure consistency.

↑
Back to top

---

alter database…modify file

Although “FOR ATTACH” is one of the ways to migrate a database to another storage but the most opted and the most preferred approach is to use “ALTER DATABASE…MODIFY FILE”.

One of the advantages of using this method is that if we have a database participating in Log Shipping, the log backup chain is not broken if the files need to be moved to different storage. The next log backup on the new storage follows the log sequence
and backs up all changes from the last log backup which was taken when the database was on the old storage and hence, the log shipping is intact. In contrast, if the database is migrated using detach and attached with log file rebuilt, log backup chain is
broken and log shipping needs to be reinitialized with a fresh full backup.

This procedure is really a four-step process. An example is given later in the article.

1. Run the ALTER DATABASE…MODIFY FILE statement by providing the new data and/or log file paths. As soon as the statement executes, the paths are modified in the internal system tables, but the new paths take effect either when the database is taken offline
   followed by online or after the SQL instance is rebooted.
2. Take the database being migrated offline by running the following statement.
   • ALTER DATABASE <DatabaseName> SET OFFLINE (with ROLLBACK IMMEDIATE, if we want to break the existing connections immediately)
   • Note: The steps 1 and 2 can be executed in any order.
3.  Manually copy the database files to the new locations. Make absolutely sure that these are the paths that were given in the ALTER DATABASE MODIFY FILE command.
4.  Turn the database back online by executing the following statement.

ALTER DATABASE <DatabaseName> SET ONLINE

Optionally, run the following to ensure that new paths are effective.

↑
Back to top

---

Common scenarios where attach may or may not work

Now that we know the underlying concept of detaching and attaching a database, let us see some examples and understand how the mechanism works. The following are several scenarios that we, as DBAs, commonly face in our work environments. Some allow the attach
operation, some allow it by rebuilding the t-log file automatically and some do not allow it due to reasons we will see in the corresponding example.

Usual Detach and Attach to Migrate a Database

The below example creates a new database named “SAMPLE” and detaches it using “SP_DETACH_DB” stored procedure and then re-attaches it, using “CREATE DATABASE…FOR ATTACH”. All database files are intact and hence, the re-attaching succeeds without any issues.

If we notice, the first attempt to detach the database fails due to open connections to the database. As mentioned, SP_DETACH_DB expects no active connections. Hence, the command runs successfully after the database is switched to single user mode.

Outcome: Database SAMPLE re-attaches successfully.

Example:

The database is successfully detached when it is taken in single-user mode. Also, note that the entry fromsys.sysdatabasesview has been removed.

If we would like to migrate the database to a new drive, manually move the database files to the new location while the database is detached and provide the new database files’ path in the «Create Database» statement below. The database would then be attached
to the new location.

↑
Back to top

When SQL instance is cleanly shut down

As discussed above, when SQL instance is cleanly shut down with no uncommitted transactions in the data file(s) and nothing to be recovered from the log file, it is as though the database was detached using “SP_DETACH_DB”. The following example proves that
a database can be re-attached successfully post SQL reboot using “FOR ATTACH”.

As soon as the SQL instance was stopped, the database files were renamed. This caused the database “SAMPLE” to go into “Recovery Pending” upon instance restart. The
database files of the SAMPLE database are attached to a new database “SAMPLE_2”.

Outcome: The database SAMPLE_2 is successfully attached using the data and log files of SAMPLE database. Again, SAMPLE has turned Recovery Pending” as the database files are now being used by SAMPLE_2.

Example:

SQL instance is shut down and the database files are renamed to «*_2». These files are attached as a new database SAMPLE_2.

When a single log file is missing

When a database, being detached has only one log (.ldf) file which goes missing after the database has been detached, then SQL Server rebuilds the log file automatically when the database is attached with only data file using “CREATE DATABASE…FOR ATTACH”.
The condition for the rebuilt of a log file to succeed is that the database must have been cleanly detached either using “SP_DETACH_DB” or the SQL instance was taken down when there was no activity against the database. The reason is, if there are records
in the log file that need to be rolled back or rolled forward then SQL Server cannot rebuild the missing log, in which case the presence of the log file is a must.

A crucial point to note here is, SQL rebuilds the log using “FOR ATTACH” only if the database had a
single log file which is missing. If there were multiple log files that are missing, FOR ATTACH will fail, in which case, “ATTACH_REBUILD_LOG” can be used. This is discussed in the next example.

In the below example, the database SAMPLE_2 is detached and its log file is removed. Because there were no ongoing transactions, the attach using “FOR ATTACH” completed successfully and rebuilt a new log file.

Outcome: SAMPLE_2 successfully attaches using FOR ATTACH by rebuilding the log file.

Example:

↑
Back to top

When multiple log files are missing

When multiple log files are missing, then “FOR ATTACH” fails. In this case, ATTACH_REBUILD_LOG is used to rebuild the log. This is demonstrated in the example below.

In the below example, an additional log file is added to database SAMPLE_2 thus, making the total number of log files as 2. The database is detached, and the two log files are removed. As we can see below, an attempt to attach this database failed since
there are two missing log files and “FOR ATTACH” holds good only when there is a single missing log file. Hence, the attach succeeds only by using the “ATTACH_REBUILD_LOG”.

Outcome: SAMPLE_2 successfully attaches using ATTACH_REBUILD_LOG and a new log file is automatically created.

Example:

Now, using ATTACH_REBUILD_LOG,

↑
Back to top

When a log file cannot be rebuilt

The example below demonstrates the behavior of the attach operation when the log file cannot be rebuilt. This happens when the log file which had records that need to be recovered goes missing. SQL Server cannot recover the database upon reattaching due
to the missing log file and hence the operation fails.

Note: The best way to come out of this situation is by restoring the most recent good backup. If a backup is unavailable, then there is a workaround that Paul Randal talks about in his

blog but it may or may not work. Further, it has many downsides so restoring from the latest backup should be the way to go.

In the below example, a new table named “TEST” is created an insert operation is started. While the insert is still in progress, the SQL instance is stopped. As we can see, the running transaction fails. The log file for this database is then removed and
an attempt to attach this database fails because the database needs to be recovered and the recovery is only possible when there is a log file.

The same error is logged in the SQL error logs because when the SQL instance comes back online, it attempts to rebuild the missing log but since the missing log file had records that need to be rolled back, SQL fails to rebuild the log.

Outcome: The attach of SAMPLE_2 fails due to the missing log file that contained an active transaction.

Example:

While the insert is in progress, SQL instance is stopped and the session is killed. The log file is then removed and an attempt to attach the database as a new database SAMPLE_3 is then made.

A similar error is logged in the SQL Server error logs when the instance came back up.

When the database is read-only

When a database is in read-only mode and goes offline. Further if its log file is removed, SQL Server automatically tries to rebuild its log when it comes online the next time but because the database was read-only when it went offline, the attempt fails
because, as mentioned above, the information about the log file is stored in the primary data file and because the database was read-only, the new log file’s details cannot be updated in the primary file, hence the operation fails. This is demonstrated in
the example below.

Note: The data file can still be attached using “FOR ATTACH” if there were no active records in the missing log that require recovery.

Outcome: SQL Server’s automatic attempt to rebuild a missing log file fails since the database was in read-only mode.

Example:

↑
Back to top

---

How log rebuild breaks the log backup chain

When a database is attached using “ATTACH_REBUILD_LOG”, SQL Server rebuilds a new log file for it. The attached database changes its recovery model to SIMPLE and if there were sequential log backups for it; they will be good only up to the point the database
was detached. A new log backup cannot be taken post attach until the database is switched to full recovery model and re-initialized with a fresh full backup. This behavior is demonstrated in the following example.

Example:

In the below example, a new table is created in the SAMPLE database and records are inserted into it. The first full backup is followed by a couple of T-log backups. Then the database is detached and its log file is removed. The same database is then attached
using «ATTACH_REBUILD_LOG» which rebuilds the log. Finally, an attempt is made to take the third log backup which ends up failing due to the change in recovery model to SIMPLE when the database was attached.

↑
Back to top

---

Detach vs Drop

While detaching a database removes it from the SQL instance and makes the database disappear from the SSMS object explorer and sys.sysdatabases catalog view, it doesn’t delete the underlying database files i.e. the .mdf and the .ldf files. The files continue
to reside on the drives.

In contrast, dropping a database removes all database files and leaves no traces of the database on the SQL instance. Just like detach, SQL Server ensures that there are no active connections to the database being dropped (in which case, the drop operation
fails) but it never checks for dirty pages in the buffer cache or any open user transactions. The only way to get the database back after a drop operation is to restore it from a previous known good backup. If there is none available, then the database is
gone for good.

↑
Back to top

---

Conclusion

Detach and attach can be used to either migrate database files to other locations or attach a database when you are left with only data files with no log files. As long as the database was cleanly detached, it should be attachable. Although Microsoft recommends
using the «ALTER DATABASE…MODIFY FILE» to migrate the database files, detach and attach can still be used as an alternative. One of the occasions where attaching a database proves helpful is when the database’s log file grows very large. You can cleanly
detach the database followed by removing its log file and attaching it with «ATTACH_REBUILD_LOG» so that a new log file with much smaller size is created. This is a quick alternative to shrinking the log.

---

See Also

• Database detach and attach (SQL Server)
• Move a Database using Detach and Attach (Transact-SQL)

---

Troubleshoot SQL Server Error 5171: “MDF is Not a Primary Database File”

At the time of using SQL Server, there are chances that you may come across a SQL Server login failure issue or stick in a situation where you are not able to restore the Microsoft SQL Server database files. There could be another issue where you cannot create ‘tempdb’ database or unable to attach MDF file in your database. These all problems are associated with the SQL Server error 5171 that states:

“database.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)”

It is a very common issue that occurs during the corruption of SQL file. Because SQL database corruption can affect the MDF files and its performance. As a result, SQL Server encounters an error message that is shown below.

How to Know About SQL Server Error 5171?

Whenever you get this error message “MDF is not a primary database file”, you should read the additional information that pops up with the error message. It helps you to understand the problem in a more clear way. It says:

It states when the SQL error 5171 occurs.

“ABC.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)”

It states  the reason behind the SQL error. If MDF file is corrupted, then SQL identifies it is an invalid file.

Why Does SQL Error Code 5171 Encounter?

Data stored in MDF file saves as pages and the size of each page is only 8 KB. The first page of the MDF file is known as the name of header page and contains crucial database information such as file signature, file size, compatibility, etc. and the actual data is stored in the rest of the pages. If either the header page or other page is not recognized by the SQL Server, then MDF file is not considered as a valid primary database file. This situation becomes the cause of SQL database error 5171 in SQL Server 2008 / 2008 R2 / 2000 / 2005 / 2012 / 2014 / 2016 / 2017. Also, this problem in SQL Server can occur due to invalid registry entries, virus infections, power failure, damaged drivers, etc.

How to Eradicate SQL Error “MDF is Not a Primary Database File”?

In order to resolve the database.mdf is not a primary database file, first, you need to know the different scenarios. Because the process of SQL Server error 5171 recovery totally depends upon the situations you are in. Here, we will discuss two circumstances where SQL error 5171 occurs and different alternatives to solve this problem.

Case 1: The Database Corruption in Mirrored Databases

If you are running SQL Server on your system and you have some mirrored databases, then you need to set it online at the time of using one of the mirrored databases. To do so, you can run the following commands:

ALTER DATABASE mydb SET online

But, instead of setting the mirrored database, “Microsoft SQL Server error 5171” occurs. Hence, the solution is discussed here to help you.

Solution to Fix Error 5171

Step 1. At first, set principal database.

Step 2. Then, modify MDF file information by using “ALTER DATABASE MODIFY FILE” command.

Step 3. Now, stop the currently running SQL server instance by clicking on the Stop option.

Step 4. After that, copy the MDF and LDF files to a different directory.

Step 5. Finally, restart the SQL Server and attach  MDF and LDF files.

Case 2: Improper Upgradation of SQL Server

Another case, if you are using SQL Server 2014 and want to upgrade to the SQL Server 2016. And, after the completion of successful installation of new version, you try to attach the MDF file again and again. Then, it turns out to be MS SQL Server Error 5171.

Solution:

This situation can be removed by running “sp_attach_db” command. It will help you to resolve the SQL error “MDF is not a primary database file”.

Fix SQL Server Error 5171 Using Professional SQL Recovery Tool

With the help of above mentioned resolution techniques, you can easily fix the error message “database.mdf is not a primary database file”. In case, if you are still facing the SQL error code 5171, then you need to opt a trouble-free professional tool to Repair Corrupt MDF File. Because, it is a very beneficial application that can repair the MDF file and fix this error message. This utility can also scan and recover all SQL data items like triggers, rules, functions, tables, stored procedures, etc. You can also download its free version from this link:-

Download Now Purchase Now

Conclusion

Here, it is discussed that the SQL database can be corrupted because of database mirroring and during the attachment of MDF file in an upgraded SQL Server. We also knew the situation where SQL Server error 5171 occurs and learned the solution to fix it. Also, with the help of SQL Recovery tool, this problem can be resolved easily. Because working with this utility is quite easy and it also saves a lot of time.

Frequently Asked Questions:-

The solutions on this page can be applied to resolve 5123 error with access denied or database attach failure in MS SQL Server 2014/2012/2008 R2/2005.

When you try to attach a database in SQL Server, but get an error message, reporting that «Microsoft SQL Server, Error: 5123» as the image shown below:

You will not be able to access the SQL Server database. Check the list of reasons that cause SQL error 5123, and if you find that your problem matches the listed reasons, read on to find the solution and fix this error:

• Database Attach Failure: Attaching and detaching database data file with several different logins.
• Access Denied: Attach a database which is placed on a different location and doesn’t have sufficient privileges to the folder directory or no proper permission to run the program folder. 

Although, many experienced administrators know how to resolve the 5123 error in SQL Server. It’s still necessary to know better and more effective methods to fix and repair more SQL Server database problems. Read on, and learn how to fix SQL Server 5123 error on your own.

Manual workaround to fix MS SQL Server 5123 error

By following the below manual steps, you’ll be able to resolve the Microsoft SQL Server error 5123 quickly:

• Step 1: Run SQL Server and right-click on the MDF or NDF file that you wish to attack.
• Step 2: Select «Properties» to check the permission of .mdf or .ndf file.
• Step 3: Click the «Add» button to grant the login permission and gain full control during the login process.

Also, you can attempt «Run as Administrator» to gain permission instead of granting permission to a new user.

Recommended tools to resolve SQL Server 5123 error

If you are using MS SQL Server Management Studio (SSMS) or Transaction SQL Server (T-SQL) to configure and manage the SQL Server, you can use these two tools to resolve the 5123 error. 

Also, you can turn to a third party MS SQL Recovery tool for help.

Use SSMS to fix SQL 5123 error

• Step 1: Log in to SSMS: use valid domain credentials and Windows Authentication to log in.
• Step 2: Click on «Object Explorer», select «Database».
• Step 3: Right-click on the database that you are trying to attach, and select «Attach».
• Step 4: Click «Add» when a new attach database dialog box appears.
• Step 5: Select the database, confirm the MDF and LDF files are running alright. And click «OK» to finish.

Then, you can view all attached database files in MS SQL Server again.

If the program reminds you that the MDF file is corrupted, relax, follow the link here and apply EaseUS MS SQL recovery tool to repair corrupted MDF file immediately.

Use T-SQL to resolve MS SQL 5123 error

Transaction SQL Server with its sp_attach_db stored procedure can help you easily attach all desired databases in SQL. 

Here are the detail process of attaching SQL Server databse:

• sp_attach_db
• [@dbname=] ‘dbname’
• ,[@filename1=] ‘filename_n’ [ ,…16 ]
• [@dbname=] – Refers to the name of the database to be attached to the server.
• [@filename1=] – ‘filename_n’ Is the physical name, including path, of a database file

Use SQL Server recovery tool to solve MS SQL error 5123

If the above-provided methods don’t work efficiently to resolve the 5123 attach database error in SQL, you can go for a reliable SQL Server recovery tool for help. It helps you to repair corrupted MFD and NDF file in SQL Server and some other complex database corruption issues. This software enables any user to operate SQL Server repair with its clear and self-explanatory interface.

Note: Mostly, EaseUS SQL Server Recovery can always work to fix this issue. But if it fails in a rare case, you can turn to Microsoft customer support team for help.

Step 1. Run EaseUS MS SQL Recovery.

Step 2. Select the MDF/NDF file: Click «Browse» or «Search” to navigate the location of your MDF or NDF file > Click «Repair».

Step 3. Select the database objects you want to recover: When the process has completed, select the database objects you want to recover and click «Export».

Step 4. Export the database to the database or export the selected items as SQL scripts. If you choose «Export to database», you will need to enter the required information and select the destination database, either a new one or an existing one.

Hence, we would like to recommend you always to keep this software handy. In addition to solving this error, EaseUS SQL Server Recovery can be used to rectify other SQL database problems too:

• Resolve SQL Database corruption with 823/824/825 error
• Recover deleted SQL records
• Repair MDF/NDF file
• Recover ROW and PAGE compressed data
• Free to preview the repaired database before activation
• Save the repaired file in MS SQL (MDF), HTML, XLS & CSV formats
• Support transferring the license to another system (one key for three systems)

Symptoms

Consider the following scenario:

• You install an instance of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, or of Microsoft SQL Server 2008 R2.

• The instance of SQL Server is named INST1 and hosts a database that is named Test_RO_FG_DB.

• The database contains the following file groups:

  • Primary

  • RO_FG

  • RW_FG

• The filegroup that is named RO_FG is marked as READ_ONLY.

• You install a new instance of Microsoft SQL Server 2012. This instance of SQL Server 2012 is named INST2.

• You detach the Test_RO_FG_DB database from INST1.

• You try to attach the Test_RO_FG_DB database to INST2.

• You receive an error message that resembles the following:

  Msg 3415, Level 16, State 2, Line 1
  Database ‘Test_RO_FG_DB’ cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

• You try to reattach the Test_RO_FG_DB database to INST1.

In this scenario, you cannot reattach the database to INST1. And, you receive the following error message in the SQL Server error log:

Note This issue only occurs when you try attach a database that contains a filegroup that is marked READ_ONLY. This issue does not occur when you try to move a READ_ONLY database in which all the data is marked READ_ONLY.

Cause

This issue occurs because SQL Server 2012 does not detect the read-only filegroup before it starts to upgrade the database. After the upgrade has started, SQL Server 2012 writes entries to the transaction log. Earlier versions cannot read the new transaction log entries.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

Resolution

Cumulative update information

SQL Server 2012

The fix for this issue was first released in Cumulative Update 2 for SQL Server 2012. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2703275 Cumulative update package 2 for SQL Server 2012Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012.

Workaround

To work around this issue, use one of the following methods.

Method 1

Restore a backup of the database from INST1 on INST2.

Note The issue that is described in the «Symptoms» section does not occur in SQL Server 2012 when you restore a backup from an earlier version.

Method 2

Perform an in-place upgrade of the earlier version of SQL Server to SQL Server 2012.

Method 3

Move a database that contains a read-only filegroup to an instance of SQL Server 2012. To do this, follow these steps.

Note Perform steps 4 through 11 on the server that is running SQL Server 2012. For example, perform steps 4 through 11 on INST2.

1. On INST1, detach the database. For example, detach the Test_RO_FG_DB database.

2. Move the database files to the server that hosts the INST2 instance.

3. Try to attach the database to INST2. The following sample code shows how to do this:

   CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RO_FG.mdf' ), 
   FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RO_FG_File1.ndf' ), 
   FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RW_FG_File1.ndf' )
   LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RO_FG_log.ldf' )
   FOR ATTACH;
   GO

   Note You will receive the 3425 error message that is mentioned in the «Symptoms» section.

4. At a command prompt, rename the database files. The following sample command shows how to do this:

   rename Test_RO_FG.mdf original_Test_RO_FG.mdf
   rename Test_RO_FG_File1.ndf original_Test_RO_FG_File1.ndf
   rename Test_RW_FG_File1.ndf original_Test_RW_FG_File1.ndf
   rename Test_RO_FG_log.ldf original_Test_RO_FG_log.ldf 

5. In SQL Server Management Studio, create a database that has the same name and physical structure as the database that you want to attach. The following sample code shows how to do this:

   CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG_DB', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RO_FG_DB.mdf' , SIZE = 4072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
   FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RO_FG_File1.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
   FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RW_FG_File1.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
   LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATATest_RO_FG_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
   GO

6. Set the database to offline. To do this, run the following command:

   ALTER DATABASE [Test_RO_FG_DB] SET OFFLINE
   GO 

7. At a command prompt, rename the files in the new database. The following sample command shows how to do this:

   rename Test_RO_FG.mdf new_Test_RO_FG.mdf
   rename Test_RO_FG_File1.ndf new_Test_RO_FG_File1.ndf
   rename Test_RW_FG_File1.ndf new_Test_RW_FG_File1.ndf
   rename Test_RO_FG_log.ldf new_Test_RO_FG_log.ldf 

8. At a command prompt, rename the files in database that you moved in step 2. Rename the files to match the database that you created in step 4. The following sample command shows how to do this:

   rename original_Test_RO_FG.mdf Test_RO_FG.mdf 
   rename original_Test_RO_FG_File1.ndf Test_RO_FG_File1.ndf 
   rename original_Test_RW_FG_File1.ndf Test_RW_FG_File1.ndf 
   rename original_Test_RO_FG_log.ldf Test_RO_FG_log.ldf 

9. Set the database to ONLINE. To do this, run the following command:

   ALTER DATABASE [Test_RO_FG_DB] SET ONLINE
   GO 

10. Verify that the database is online, and reestablish the Service Broker functionality.

11. Delete the database files that are not needed. The following sample command shows how to do this:

    del /P new_Test_RO_FG.mdf
    del /P new_Test_RO_FG_File1.ndf
    del /P new_Test_RW_FG_File1.ndf
    del /P new_Test_RO_FG_log.ldf 

Method 4

Reattach a database that contains a read-only filegroup to the earlier instance of SQL Server. To do this, follow these steps.

Notes

• The database also contains the new transaction log entries from the failed upgrade.

• Perform steps 3 through 10 on the server that is running an earlier version of SQL Server. For example, perform steps 3 through 10 on INST1.

1. Move the database files to the instance of SQL Server that is hosting INST1.

2. Try to attach the database to INST1. The following sample code shows how to do this:

   CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG_DB', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RO_FG_DB.mdf' ), 
   FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RO_FG_File1.ndf' ), 
   FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RW_FG_File1.ndf' )
   LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RO_FG_log.ldf' )
   FOR ATTACH
   GO

   Note You will receive the 3624 error message that is mentioned in the «Symptoms» section. You will also receive an 1813 error message.

3. At a command prompt, rename the database files on INST1. The following sample command shows how to do this:

   rename Test_RO_FG.mdf original_Test_RO_FG.mdf
   rename Test_RO_FG_File1.ndf original_Test_RO_FG_File1.ndf
   rename Test_RW_FG_File1.ndf original_Test_RW_FG_File1.ndf
   rename Test_RO_FG_log.ldf original_Test_RO_FG_log.ldf 

4. In SQL Server Management Studio, create a database that has the same name and physical structure as the database that you want to attach. The following sample code shows how to do this:

   CREATE DATABASE [Test_RO_FG_DB] ON PRIMARY ( NAME = N'Test_RO_FG_DB', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RO_FG_DB.mdf' , SIZE = 4072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
   FILEGROUP [RO_FG] ( NAME = N'Test_RO_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RO_FG_File1.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
   FILEGROUP [RW_FG] ( NAME = N'Test_RW_FG_File1', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RW_FG_File1.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
   LOG ON ( NAME = N'Test_RO_FG_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_RO_FG_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
   GO

5. Set the database to offline. To do this, run the following command:

   ALTER DATABASE [Test_RO_FG_DB] SET OFFLINE
   GO 

6. At a command prompt, rename the files in the new database. The following sample command shows how to do this:

   rename Test_RO_FG.mdf new_Test_RO_FG.mdf
   rename Test_RO_FG_File1.ndf new_Test_RO_FG_File1.ndf
   rename Test_RW_FG_File1.ndf new_Test_RW_FG_File1.ndf
   rename Test_RO_FG_log.ldf new_Test_RO_FG_log.ldf 

7. At a command prompt, rename the files in database that you moved in step 2. Rename the files to match the database that you created in step 4. The following sample command shows how to do this:

   rename original_Test_RO_FG.mdf Test_RO_FG.mdf 
   rename original_Test_RO_FG_File1.ndf Test_RO_FG_File1.ndf 
   rename original_Test_RW_FG_File1.ndf Test_RW_FG_File1.ndf 
   rename original_Test_RO_FG_log.ldf Test_RO_FG_log.ldf 

8. Set the database to EMERGENCY mode, and perform a repair. To do this, run the following command.

   Note The database transaction logs are rebuilt during this step. This may result in data loss. Therefore, we recommend that you back up the database before you perform this step.

   ALTER DATABASE Test_RO_FG_DB SET EMERGENCY
   GO
   ALTER DATABASE Test_RO_FG_DB SET SINGLE_USER
   GO
   DBCC CHECKDB (Test_RO_FG_DB, repair_allow_data_loss) WITH ALL_ERRORMSGS
   GO
   ALTER DATABASE Test_RO_FG_DB SET MULTI_USER
   GO 

9. Verify that the database is online, and reestablish the Service Broker functionality.

10. Delete the database files that are not needed. The following sample command shows how to do this:

    del /P new_Test_RO_FG.mdf
    del /P new_Test_RO_FG_File1.ndf
    del /P new_Test_RW_FG_File1.ndf
    del /P new_Test_RO_FG_log.ldf 

More Information

There are several steps that occur when a database is attached to an instance of SQL Server. These steps include recovering the database and upgrading the files from earlier versions of SQL Server.

In the issue that is described in the «Symptoms» section, SQL Server 2012 begins the upgrade process before the read-only files in the database are detected. The upgrade steps include starting a transaction to clear the «cleanly shut down» bit in the boot page of the database. Earlier versions of SQL Server cannot read the begin transaction record. Therefore, the database is not usable in earlier versions of SQL Server, and SQL Server generates the 3624 error.

In-place upgrades when a database is marked as read-only

When you perform an in-place upgrade of an instance of SQL Server that contains a read-only database that is named Test_RO_DB to SQL Server 2012, you may receive error messages that resemble the following in the SQL Server error log:

At the end of the upgrade process, the Test_RO_DB database will be in the RECOVERY_PENDING state. You must use the ALTER DATABASE command to set the database to READ_WRITE. Then use the ALTER DATABASE command to set the database to READ_ONLY. This lets the SQL Server engine upgrade the database to the correct version.

In-place upgrades when a read/write database contains file groups that are marked as read-only

When you perform an in-place upgrade to SQL Server 2012, you may receive messages that resemble the following in the SQL Server error log. This issue occurs when the earlier instance of SQL Server hosts a read/write database and contains file groups that are marked READ_ONLY. However, the upgrade process finishes as expected, and the database starts online.

Note In the following error message, the database is named Test_RO_FG: