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.
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:
- This article will use SQL Server in any version.
- 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:
- 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.
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.
