I had a colleague with problems to attach a SQL Server database with the displaying SQL error 5173:
Error 5173: Cannot associate files with different databases.
Attach database failed for Server Serv4567′. (Microsoft.SqlServer.Smo)
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 to 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.
In this article, we will show how to solve the problem related to the error 5173 in SQL Server.
The following requirements will help you to follow this article:
- This article will use SQL Server in any version.
- Finally, you will need SQL Server Management Studio.
Here we will check the different solutions available—
In databases we have 3 different types of files for SQL Server:
- The primary data file with .MDF extension is the main file and contains the data.
- 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.
- 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.
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
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 here:
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.
Rebuild the log file
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:
<strong>CREATE DATABASE yourdatabase</strong>
<strong>ON (FILENAME = 'c:\yourPrimaryFile_Data.mdf')</strong>
<strong>FOR ATTACH_REBUILD_LOG ;</strong>
The code will create a database based on the mdf file and generate a new log file for the mdf file. The for attach_rebuild_log creates a new log for you.
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.