Summary: In this article, we will discuss different solutions to fix the error 5173 in SQL Server. The solutions include rebuilding a missing or corrupt log file (.ldf) and repairing primary data file (.mdf) using SQL repair tool.
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)
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.
The following requirements will help you to follow this article:
In databases we have 3 different types of files for SQL Server:
The sp_helpfile system stored procedure shows all the files in a specific database. It will show the size, path, filegroup, and id.
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.
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 your primary data file does not match with the log file and your primary data file is corrupt, you can do the following:
You can download the software from here:
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.
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.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
Stellar Repair for MS SQL is an enterpriRead More
3-in-1 software package, recommended byRead More
Stellar Converter for Database is an effRead More
Powerful tool, widely trusted by users &Read More