Fix SQL Database Error 5173 – Attach database failed for Server
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.
I have tried many manual approaches to resolve below error, but still can’t succeed. Please provide assistance.
‘Log file [log name] does not match the primary file. It may be from a different database or the log may have been rebuilt previously.’
It is common that a pending state does not get resolved by the manual approach. Try to use automated tool i.e. Stellar repair for MS SQL which repair your database from the Suspect mode and make your database accessible for use.
When I tried to bring SQL database in online mode, I have encountered this error:
“Database [dbname] cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.”
How to resolve this error?
For better & quick approach to resolve SQL server recovery pending state is using Stellar repair for MS SQL.
To fix “SQL database Error 5173 – Attach database failed for server”, I have tried DBCC CHECKPRIMARYFILE command to read the primary file header to know the information stored. In this, I have created the database with two data files & one log file. But after wasting so many hours, still facing same error.
Please help.
Try to avoid DBCC command, it will not work in the highly corrupt database file. Give a try to Stellar repair for MS SQL tool and use free demo version.
Our organization hard drive crashed due to hardware issue. We have not taken backup. Through recovery center, we recover database & log files. When we try to attach the database, we got SQL Server error 5173 & 824.
How to fix both errors and attach to database?
To repair both SQL error 5173 & 824, try professional tool Stellar Repair for MS SQL which can recover the damaged primary data file.