{"id":28404,"date":"2018-11-27T11:08:44","date_gmt":"2018-11-27T11:08:44","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=28404"},"modified":"2024-01-24T11:56:51","modified_gmt":"2024-01-24T11:56:51","slug":"sql-database-error-5173-attach-database-failed","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/sql-database-error-5173-attach-database-failed\/","title":{"rendered":"Fix SQL Database Error 5173 – Attach database failed for Server"},"content":{"rendered":"

Introduction<\/strong><\/h2>

I had a colleague who experienced issues attaching a SQL Server database and encountered SQL database error 5173 – Attach database failed to Server:<\/p>

Error 5173: Cannot associate files with different databases.<\/strong><\/p>

Attach database failed for Server Serv4567′.  (Microsoft.SqlServer.Smo)<\/em><\/span><\/p>

\n
\"SQL<\/figure>\n<\/div>

Additional Information:<\/strong><\/span><\/p>

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)<\/span><\/p>

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.<\/span>
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5173)<\/span><\/p>

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.<\/p>

Requirements<\/strong><\/h2>

The following requirements will help you to follow this article:<\/p>

    \n
  1. This article will use SQL Server in any version.<\/li>\n\n\n\n
  2. Finally, you will need SQL Server Management Studio.<\/li>\n<\/ol>

    Getting started<\/strong><\/h2>

    Here we will check the different solutions available:<\/p>

    In databases, we have 3 different types of files for SQL Server:<\/strong><\/h3>
      \n
    1. The primary data file with .MDF extension is the main file and contains the data.<\/li>\n\n\n\n
    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.<\/li>\n\n\n\n
    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.<\/li>\n<\/ol>

      The sp_helpfile<\/strong> system stored procedure shows all the files in a specific database. It will show the size, path, filegroup, and id.<\/p>

      Syntax<\/strong>:<\/p>

      sp_helpfile [ [ @filename= ] 'name' ]<\/code><\/pre>
      Where, [ @filename = ] ‘name’ is the logical name of any file in the current database. <\/p>
      \"Stellar\"<\/figure>
      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.<\/p>
      A possible solution to fix SQL database error 5173<\/strong><\/h3>
      If log file is missing or corrupt<\/strong><\/h4>
      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:<\/p>
      CREATE DATABASE yourdatabase \nON (FILENAME = 'c:\\yourPrimaryFile_Data.mdf') \nFOR ATTACH_REBUILD_LOG ;<\/code><\/pre>
      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.<\/p>
      If database is corrupt<\/strong><\/h4>
      If your primary data file does not match with the log file and your primary data file is corrupt, you can do the following:<\/p>