Table of Content
    SQL Database Repair

    Fix SQL Database Error 5173 – Attach database failed for Server


    Table of Content

      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)

      SQL Database Error 5173

      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.

      Stellar

      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:

      free download
      • This is the main interface of Stellar Repair for MS SQL
      interface of Stellar Repair for MS SQL
      • Select the MDF file which you want to repair
      Select the MDF file which you want to repair
      • Click Repair to start scanning and repair process
      Click Repair to start scanning and repair process
      • Check preview of database
      Selected MS SQL database repaired successful
      • 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.
      Save Database window

      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.

      Was this article helpful?

      No NO

      About The Author

      Priyanka Chauhan linkdin

      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.

      8 comments

      1. 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.’

        1. 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.

      2. 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?

      3. 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.

        1. 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.

      4. 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?

        1. To repair both SQL error 5173 & 824, try professional tool Stellar Repair for MS SQL which can recover the damaged primary data file.

      Leave a comment

      Your email address will not be published. Required fields are marked *

      Image Captcha
      Refresh Image Captcha

      Enter Captcha Here :

      Related Posts

      WHY STELLAR® IS GLOBAL LEADER

      Why Choose Stellar?

      • 0M+

        Customers

      • 0+

        Years of Excellence

      • 0+

        R&D Engineers

      • 0+

        Countries

      • 0+

        PARTNERS

      • 0+

        Awards Received