Sql ошибка 5173

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:

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.

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.

Facing Microsoft SQL Error 5173? Know How to Solve This Server Error

Jason Clark ~
Modified: March 3rd, 2023 ~ SQL ~ 6 Minutes Reading

If you are an SQL Server user, you may come across any error while performing your everyday task. Errors can occur due to different reasons and separate solutions are recommended for each of the errors. The error in discussion today is SQL error 5173. In this post, we will learn the reasons and solutions to SQL Server error 5173 attach database. But first, check out the user queries regarding this error found on tech forums.

Table of Contents

• User Queries
• SQL Error 5173?
• Causes of the Error
• How to Fix the Error
• Best Solution Present
• The Concluding Words

User Queries

User Query 1: “A few days ago, It attempted to transfer my data to another SQL Server. So, I have performed these steps: first I detached the database, then I copied my MDF file (not LDF) to the new Server. At this point, I created a new database with the original DB name in the new Server.

Then I detached the new database and replaced the new MDF file the old (original) MDF file. During the process, I got SQL error number 5173 that says: “cannot associate files with different databases.” please tell me why I am getting this message and what is the solution.”

User Query 2: “A hard disk containing a large sized database had some corruption issues. As there was no backup available, we had to take the help of a freelance technician to recover the database.

After the MDF and LDF files have been recovered, we tried to attach those and failed. Instead, MS SQL error 5173 is what we received. Why are we getting this error message? Can anyone explain its reasons and solutions?”

What Is MS SQL Error 5173?

The Microsoft SQL Server error 5173 occurs due to a problem with both the Primary and Transaction log file. Due to some sort of error, the files don’t match at all. Therefore, the SQL Database thinks that data files belong to different databases & different servers. In case of primary data file corruption, you can use the automated MS SQL Repair Tool.

MS SQL Server Error 5173 – Reasons

If we check carefully, we will find that this error appears because the files being used by the database belong to different databases. This is basically a safety mechanism that prevents accidental overwriting of existing files. That is why during the startup, database checks if all files are from same database and if not, it shows this error message. If the database is corrupt or it is in Recovery Pending state, this error message will get displayed.

This error message appears due to various reasons. The major causes are corrupt MDF file and files used in database are not from the same database. Besides, power failure, insufficient storage space, and defective hardware can be responsible for this error message.

How to Fix MS SQL Error 5173

Depending on the cause, users need to adopt remedial methods to fix this error.

• Method 1: Use DBCC CHECKPRIMARYFILE Command

To get rid of this problem, users need to apply DBCC CHECKPRIMARYFILE command to know what is in the primary file header. It is because when a database is attached, reading MDF file information is not feasible. Hence, comes the requirement of DBCC command to open and read the primary file header. To apply the command, use these steps & solve Microsoft SQL server error 5173 attaching database issue:

a. To begin with, users have to create a database that contains two data files and a log file.

b. Then detach the database. Use this command for DB detachment.

c. Run DBCC CHECKPRIMARYFILE command to open file header and read the detailed information.

d. Since the first file is the primary database file, we will get output for that file only (the output will be 1).

e. This command will let users read the properties information from the file header.

Option 1. This displays the file and filegroup ID, physical name, logical name of the files that belong to the same database.
Option 2. It shows the original database where the file belongs, along with database version and collation.
Option 3. This is actually the subset of option no. 1.

• Method 2: Change Recovery Pending State of Database

a. Run this script to set the DB in emergency mode:

 ALTER DATABASE [dbname] SET EMERGENCY

b. Make the database available for multiple users:

 ALTER DATABASE [dbname] SET MULTI_USER

c. Detach the database and then re-attach:

 EXEC sp_detach_db '[dbname]' EXEC sp_attach_single_file_db @dbname = '[dbname]', @physname = N'[mdf path]'

• Method 3: Make Corrupt Database Healthy

If the database has gone corrupt, run this script to repair it:

The Best Solution to Solve Microsoft SQL Error 5173

As this command will result in data loss, experts do not recommend this method. Therefore, it is better to use SQL MDF File Repair Tool. This utility can fix all types of corrupt SQL database without any data loss. Using this application, which is compatible with all latest versions of SQL Server, users can also retrieve deleted database files.

Download Now Purchase Now

Step-1. Launch the Tool to solve Microsoft SQL server error 5173 first.

Step-2. Select the Quick or Advanced Scan for database migration.

Step-3. Preview the Database Files & then Go to Export options.

Step-4. Select the Destination Path & Set Export Settings here.

Step-5. Finally, Click on the Export button to get the desired results.

Concluding Words

Here, we discussed all the aspects of SQL error 5173 including its causes and cures. We have provided multiple solutions that can be implemented to get rid of this error for good. We expect that users can follow the guideline described here and get the desired result.

In a nutshell, we can say that it’s quite easy for users to solve this Microsoft SQL server error 5173 with the automated solution. Apart from that, the manual solution works but it is hectic. In order to stay away form hassles, the modern method is the right choice for users. It can be the best choice to solve this critical SQL server error.

This is a very common error which may occur during migration
of SQL Database files. The reason may be due to the corruption of files or due
to different file names. Therefore the user encounters error 5173 cannot
associate files with different databases. In this article, I am going to
discuss how to resolve this error manually.

Explanation of Error
5173

In a layman term, this error occurs when the user tries to
open the SQL database and due to corruption or missing content the file doesn’t
open. This is an attach error where the user has moved from one database to
another but after migration, he is not able to access the file.

There is a lot more to tell about this error but we can
discuss it later. First of all, let’s see how we can resolve this error.

Types of Files on the
SQL Server

• MDF Files–
  This is a primary data file with an extension and all the other details.
• NDF Files–
  This is a secondary data file which is not created by default. When the user
  wants to separate the data into different hard drives for security reasons then
  this file is created.
• LDF Files–
  This file contains the log information. It helps in recovering the data from
  the database.

You can use the sp_helpfile system to view all the files
present on the database. It will show you the file size, location, and ID.

You May Also Read- Fix SQL Server Error 945.

Fix Cannot Associate
Files with Different Databases Error

The primary data file doesn’t match the log file and
therefore the user faces such errors. It can be fixed:-

• Inspect whether the primary data file matches the log file or not. Sometimes the user attaches the wrong log file with the Primary file.
• If the primary data is missing then you have to use the SQL Repair Tool to fix the corruption and resolve the error 5173.

You can Download the Software form below:-

The MS SQL Database Recovery software helps the user to
repair the MDF file and then the user will not face such errors. It also
supports BLOB, Images, XML and large page data. This tool is compatible with
all the versions of windows.

If you are having some problem with the LDF file then you
can recreate it.

Recreate LOG File

If you are unable to find the correct LDF file then you can
rebuild it for the MDF file that you have recently recovered. Enter the
following SQL code to Rebuild the File.

CREATE DATABASE yourdatabase

ON (FILENAME =
‘c:\yourPrimaryFile_Data.mdf’)

FOR ATTACH_REBUILD_LOG ;

A new database would be created on the MDF file. By this attach_rebuild_log code, you can create a new log file for the MDF files.

Conclusion

So the error 5173 cannot associate files with different databases
occurs when there is a problem in the MDF file and in the Log file. Due to some
negligence, the two files don’t match each other and the system interprets it
as the files of two different databases.

But if the primary file is corrupted then you can repair it
by using the third party software. Once you repaired it then you can match both
the file by creating a new Log file using attach_build_log. Hope you liked this
article.

Related Post

SQL Server Error: 5173
Severity: 16
Event Logged or not: Yes
Description:
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.
Severity 16 Description:
Indicates general errors that can be corrected by the user.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

Alternate Solutions

1. Restarting SQL Server Service(non production instances only)

• To Restart, Start or Stop the SQL Server instance by right click on sql server instance in SSMS or in SQL. You may need to open SSMS as administrator to start, stop the instance.

• Other ways for restarting SQL server Service

1. From SQL Configuration manager from Start menu
2. From Services in Windows server
3. From Cmd using net start and net stop

2.Checking SQL Performance metrics like CPU, Memory

Check SQL Server CPU, Memory usage, longest running queries, deadlocks etc.. using activity monitor or sp_who2.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.

Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor

It shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

For information on SQL Server Activity monitor go to https://sqlserver-dba.co.uk/sql-server-administration-basics/activity-monitor

Or using SQL Query analyzer window to run sp_who2 command which is less resource intensive and gives same information as activity monitor.

2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.

1. Open task manager to check CPU, Memory usage etc.
2. Open file explorer to check Disk space on each drive.

SQL Server Error Code and solution summary

SQL Server Error: 5173
Severity: 16
Event Logged or not: Yes
Description:
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.