Sql server ошибка 5171

Page Content: Fix MDF Is Not A Primary Database File

Applies to: 5171 error in MS SQL Server 2017, 2016, 2014, 2012, 2008, etc.

SQL Server Error 5171: MDF Is Not A Primary Database File

If you receive this error message, it means that you are having Database error 5171 on SQL Server:

«ABC.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)»

Error 5171 is a common issue that many SQL Server administrators have met when they try to log in to SQL or restore the MS SQL database. This error may also happen when you can’t create ‘tempdb’ database or can’t attach an MDF file to the database.

To resolve this error, you should check the causes first so to figure out the right solution:

• MDF files corrupted or not recognized
• Invalid registry entries
• Virus infection
• Power failure
• Drivers damaged

If the 5171 error is caused by the latter four reasons, you are highly suggested to modify the registry entries, clean up the virus and restart SQL, check the power connection, and update drivers. If the problem exists in the MDF database file, read on to find two solutions here, to get rid of this issue. 

Solution 1. Repair MDF File with EaseUS SQL Recovery

When the MDF file is corrupted or saved records are lost in the MDF file, you’ll get this error on SQL. 

A fast and secure way to get rid of this issue is to repair the MDF file with a reliable SQL recovery tool. Notice that when you search online to find such a tool, choose the product provided by an experienced company. Also, it should be risk-free, 100% clean, and with a strong tech support team.

Being experienced in data recovery experience, EaseUS recently released its SQL recovery software to help SQL Server administrators.

With it, you can effortlessly repair the corrupted database, MDF file restores, and even restore deleted records in SQL. Follow the steps to repair the corrupted MDF file:

Step 1. Stop MS SQL Server service

Press «Windows + R» and enter services.msc.

Find and double-click «SQL Server (instant name)».

In the Properties windows, click «Stop» to end the SQL Server and click «Apply» to confirm.

Step 2. Run EaseUS SQL repair tool. In the main interface, click «Browse» (the two dots) to choose the corrupted MDF/NDF file. Then click «Repair» to start analyzing your MDF/NDF file. 

If you know the exact location of the file, click «Browse» to locate the database.

If you don’t know the file location, click «Search» to search for the .mdf or .ndf file in.

Step 3. When it has done, you will see all the database objects listed in the left pane of the window. Choose the database objects you’d like to repair and click «Export».

Step 4. Choose a preferred way to export the database data: «Export to database» or «Export as SQL scripts». If you select «Export to database», you need to further select the server information, log into your account, and select a destination database, either a new one or an existing one.

Step 5. Before you click «OK», now you need to restart the SQL Server.

Press «Windows + R» and enter services.msc.

Find and double-click «SQL Server (instant name)».

In the Properties windows, click «Start» to restart the SQL Server service and click «Apply» to confirm.

Step 6. Click «OK» to save the repaired files to your desired SQL database.

After saving the repaired MDF file, restart SQL Server and log in with your password. Then you should be able to access the MDF data and run SQL Server normally again.

Solution 2. Manually Resolve SQL Server Error 5171

Another method that you can use to run Microsoft SQL Server Management Studio to resolve this error manually:

Step 1. Run Microsoft SQL Server Management Studio and make sure the database is online. 

• If it’s offline, run the command: ALTER DATABASE mydb SET online.

Step 2. Modify MDF file information with ALTER DATABASE MODIFY FILE command.

• Back up the SQL Server database first before you continue with the next step.

Step 3. Right-click on the currently running SQL Server and select «Stop».

Step 4. Copy the MDF and LDF files to a different directory.

Step 5. After that, restart the SQL Server and attach both MDF and LDF files.

Tips to Protect SQL SQL Database 

After resolving the SQL database 5171 error, it’s also essential to learn useful tips to protect SQL Server database. Try the tips here for help:

1. Avoid improper upgrade of SQL Server

Don’t upgrade SQL Server frequently. If the current version is running smoothly on your Server machine, stay with it. If you’ve upgraded the SQL but are having an error like 5171, run sp_attch_db command to resolve this issue:

2. Back up SQL Server database regularly

Automated SQL Server backup software — EaseUS Todo Backup Advanced Server supports you to create a full backup of your huge SQL Server database with ease. 

3. Repair corrupted SQL Server database immediately

Once the database is corrupted on SQL Server, apply EaseUS SQL Recovery to repair the database file and restore all valuable data immediately.

Conclusion

While SQL Server Database Error 5171 can be a frustrating and complex issue to deal with, there are many steps you can take to resolve it. Some common solutions include using SQL recovery software, such as EaseUS MS SQL Recovery, or fixing the SQL error manually. You may also want to consult with a database expert for additional guidance and support. Ultimately, the key is to stay calm, do your research, and take action as soon as possible in order to get your data back quickly and efficiently. With the right tools and strategies, SQL Server Database Error 5171 should not prevent you from accessing your important data again.

Troubleshoot SQL Server Error 5171: “MDF is Not a Primary Database File”

At the time of using SQL Server, there are chances that you may come across a SQL Server login failure issue or stick in a situation where you are not able to restore the Microsoft SQL Server database files. There could be another issue where you cannot create ‘tempdb’ database or unable to attach MDF file in your database. These all problems are associated with the SQL Server error 5171 that states:

“database.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)”

It is a very common issue that occurs during the corruption of SQL file. Because SQL database corruption can affect the MDF files and its performance. As a result, SQL Server encounters an error message that is shown below.

How to Know About SQL Server Error 5171?

Whenever you get this error message “MDF is not a primary database file”, you should read the additional information that pops up with the error message. It helps you to understand the problem in a more clear way. It says:

It states when the SQL error 5171 occurs.

“ABC.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)”

It states  the reason behind the SQL error. If MDF file is corrupted, then SQL identifies it is an invalid file.

Why Does SQL Error Code 5171 Encounter?

Data stored in MDF file saves as pages and the size of each page is only 8 KB. The first page of the MDF file is known as the name of header page and contains crucial database information such as file signature, file size, compatibility, etc. and the actual data is stored in the rest of the pages. If either the header page or other page is not recognized by the SQL Server, then MDF file is not considered as a valid primary database file. This situation becomes the cause of SQL database error 5171 in SQL Server 2008 / 2008 R2 / 2000 / 2005 / 2012 / 2014 / 2016 / 2017. Also, this problem in SQL Server can occur due to invalid registry entries, virus infections, power failure, damaged drivers, etc.

How to Eradicate SQL Error “MDF is Not a Primary Database File”?

In order to resolve the database.mdf is not a primary database file, first, you need to know the different scenarios. Because the process of SQL Server error 5171 recovery totally depends upon the situations you are in. Here, we will discuss two circumstances where SQL error 5171 occurs and different alternatives to solve this problem.

Case 1: The Database Corruption in Mirrored Databases

If you are running SQL Server on your system and you have some mirrored databases, then you need to set it online at the time of using one of the mirrored databases. To do so, you can run the following commands:

ALTER DATABASE mydb SET online

But, instead of setting the mirrored database, “Microsoft SQL Server error 5171” occurs. Hence, the solution is discussed here to help you.

Solution to Fix Error 5171

Step 1. At first, set principal database.

Step 2. Then, modify MDF file information by using “ALTER DATABASE MODIFY FILE” command.

Step 3. Now, stop the currently running SQL server instance by clicking on the Stop option.

Step 4. After that, copy the MDF and LDF files to a different directory.

Step 5. Finally, restart the SQL Server and attach  MDF and LDF files.

Case 2: Improper Upgradation of SQL Server

Another case, if you are using SQL Server 2014 and want to upgrade to the SQL Server 2016. And, after the completion of successful installation of new version, you try to attach the MDF file again and again. Then, it turns out to be MS SQL Server Error 5171.

Solution:

This situation can be removed by running “sp_attach_db” command. It will help you to resolve the SQL error “MDF is not a primary database file”.

Fix SQL Server Error 5171 Using Professional SQL Recovery Tool

With the help of above mentioned resolution techniques, you can easily fix the error message “database.mdf is not a primary database file”. In case, if you are still facing the SQL error code 5171, then you need to opt a trouble-free professional tool to Repair Corrupt MDF File. Because, it is a very beneficial application that can repair the MDF file and fix this error message. This utility can also scan and recover all SQL data items like triggers, rules, functions, tables, stored procedures, etc. You can also download its free version from this link:-

Download Now Purchase Now

Conclusion

Here, it is discussed that the SQL database can be corrupted because of database mirroring and during the attachment of MDF file in an upgraded SQL Server. We also knew the situation where SQL Server error 5171 occurs and learned the solution to fix it. Also, with the help of SQL Recovery tool, this problem can be resolved easily. Because working with this utility is quite easy and it also saves a lot of time.

Frequently Asked Questions:-

Hi

My database Drive got crashed and eventually I have to recover the MDF’s, LDF’s from 3rd party recovery software. I dont have the backup of these databases.

When trying to attach these DB’s in SQL Server 2008. I am getting this error

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

E:DataDatabase.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5171&LinkId=20476

Original Database is of SQL Server 2008 and restoring on the same version

Know How to Fix SQL Error 5171 Interruption

SQL Server database is a relational database management system, which is very helpful in handling huge data. However, from time to time SQL users encounters several errors while working on it. One such interruption is SQL error 5171. When such interruption occurs, an error message appears on screen like:

” An exception occurred while executing a Transact-SQL statement or batch._db.mdf is not a primary database file. (Microsoft SQL Server, Error:5171)”.

It is seen this error 5171 occurs in SQL Server database when the header page or any other page in the database is corrupt. Due, to which user is not able to access the database or open MDF files present in database. There are reasons also behind this error which are as follows:

Root causes of SQL Error 5171:

• Abrupt system shutdown.
• Virus/ Malware attack.
• Or, there is severe corruption in the file header.
• There is either damaged index or damaged media.

When Microsoft SQL Server Error 5171, occurs data becomes inaccessible and if in case there is no backup then resolving this error is bit tedious. There are some scenarios in which such errors took place.

Scenarios in which SQL Server Error 5171 occurs:

Scenario 1: When a user is using a mirror database in SQL Server database & you get the database online by giving ALTER DATABASE command.

How to Fix: Procedure to be followed are as follows:

• Set Principal database & then use ‘ALTER DATABASE MODIFY FILE’ command.
• After this change the file information.
• Now, immediately stop the running server.
• And, then restart the SQL Server database.
• Attach the database files MDF & LDF.

By, doing this you will lose the mirrored database & again you have to perform database mirroring.

Scenario 2: When you are trying to upgrade your SQL database from one version to another. You may be wondering, how will I encounter SQL error 5171, while doing so. But, during the whole process when you detach the database and after installation reattaching it by adding MDF & LDF files. You might encounter this SQL Server error 5171.

Know How to Fix SQL Error 5171

To fix it, what you can do is use ‘sp_attach_db’ in order to attach your database.

sp_attach_db @dbname = N’mydb’

@filename1 =

N’C :Program Files Microsoft SQL ServerMSSQL13.MSSQLSERVERMSSQLDatamydb_Data.mdf’ ,

@filename2 =

N’C :Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLData mydb_log.ldf’;

Another Approach to Fix SQL Error 5171:

If even after following these ways, you are not able to fix the Microsoft SQL server error 5171, then you need to opt for an SQL Database Recovery tool. It will help you resolve several errors in SQL database immediately and fixes issues in Page-compression and Server-row compression in SQL database. You can try its demo version in order to evaluate its performance and get your recovered output.

Download Now

Conclusion

As, you can see it is difficult to resolve SQL error 5171, by manual technique. So, to fix it on an immediate basis, you can opt for a recovery tool and resolve issues related to database files immediately. It will also help you to restore database objects like foreign keys, identities, user-defined functions, default constraints, etc. in minimum time. Overall it is a complete solution to resolve issues present SQL Server Database.

Related Post

«ABC.mdf рассматривается не только как первичный файл базы данных. (Ошибка Microsoft SQL Server: 5171)

Ошибка 5171 также является распространенной проблемой, с которой сталкиваются многие администраторы SQL Server при просмотре веб-страниц SQL или восстановлении базы данных MS SQL. Кроме того, эта ошибка может возникнуть, если вы не можете создать базу данных tempdb или просто не можете прикрепить файл MDF к базе данных.

Чтобы исправить эту конкретную ошибку, вы должны сначала проверить причины, чтобы найти правильную замену:

• Поврежденная или нераспознанная инструкция MDF.
• Недействительные записи реестра
• Заражение Vi Ous
• Сбой питания
• Поврежденный драйвер

Если предыдущие четыре причины вызывают ошибку 5171, обычно настоятельно рекомендуется изменить записи реестра, удалить вирусы и перезапустить SQL, найти подключение к электросети и обновить драйверы. p>

Если проблема этого типа сохраняется в базе данных MDF на моем изображении, ознакомьтесь с двумя приведенными ниже решениями, которые помогут ее решить.

Решение 1. Восстановление файла MDF с помощью EaseUS SQL Recovery

Если файл MDF перегружен или какие-либо сохраненные записи внезапно потеряны при выполнении файла MDF, вы получите одно сообщение об ошибке в SQL.

Быстрый и надежный способ решить эту проблему – начать использовать сложный инструмент восстановления SQL для восстановления этого mdf-файла. Обратите внимание: если вы хотите использовать такой инструмент в Интернете, конкретный продукт будет предоставлен опытными производителями. Он также должен быть безопасным, на 100% в хорошем состоянии и иметь сильную команду технической поддержки.

Поскольку EaseUS хорошо разбирается в средствах защиты данных, EaseUS недавно выпустила свое программное обеспечение для восстановления SQL в помощь администраторам SQL Server.

С его помощью можно легко восстановить поврежденные базы данных, восстановить презентации MDF и даже восстановить удаленные записи SQL.

Нажмите “Остановить” в окнах свойств, чтобы остановить каждый из SQL Server, и нажмите “Применить” для подтверждения.

Шаг 2. Запустите пакет EaseUS Repair SQL. В главном интерфейсе нажмите кнопку управления «Обзор» (пара точек), чтобы выбрать поврежденный файл PC MDF / NDF. Затем нажмите «Восстановить», чтобы облегчить сканирование собственного файла MDF / NDF.

Если вы знаете точное местоположение большинства файлов кликов, нажмите “Обзор”, чтобы чаще выполнять поиск в базе клиентов.

Если вы не знаете, где находится какой-либо файл, нажмите «Найти», чтобы найти отдельный файл .mdf или .ndf.

Шаг 3. По завершении вы увидите все объекты, проверенные в базе данных, на левой панели с помощью окна. Выберите элементы базы данных, которые вам необходимо восстановить, и нажмите «Экспорт» рукой ребенка.

Шаг 4. Выберите предпочтительный метод отправки данных из каталога: «Экспорт в другую базу данных», «Экспорт как сценарий SQL». Если вы выберете «Экспорт в базу данных», вам также может не понадобиться выбирать руководство по серверу, входить в систему с основной учетной записью и выбирать новую или более новую целевую базу данных.

Шаг 5. Перед тем, как нажать OK, вам необходимо обработать SQL-сервер.

На ветровом экране “Свойства” нажмите “Пуск”, перезапустите службу SQL Server и нажмите “Применить для проверки”.

Шаг 6. Нажмите «ОК», чтобы сохранить восстановленные файлы в нашей базе данных SQL.

После сохранения восстановленного реестра перезапустите SQL Server mdf и войдите в систему со своим паролем. Тогда вы, как правило, снова сможете получить доступ к типу потока данных MDF и SQL-серверу.

Несколько решений: исправить ошибку SQL Server 5171 вручную

Другой метод, который можно использовать для запуска Microsoft SQL Server Management Studio, чтобы исправить ошибку вручную:

Консультации по охвату базы данных SQL

Кроме того, после устранения ошибки 5171 предоставления данных SQL важно получить верный совет о том, как защитить базу данных SQL Server.

1. Избегайте неправильного обновления SQL Server

Не обновляйте SQL Server часто. Если текущая версия должна корректно работатьb на сервере, оставьте ее.

Если вы изменили SQL, но получили ошибку, включающую 5171, используйте команду sp_attch_db, чтобы решить эту проблему:

2. Регулярно создавайте резервную копию базы данных SQL Server

Программное обеспечение для автоматического резервного копирования SQL Server – EaseUS Backup todo Advanced Server помогает любому легко создать полную резервную копию чрезвычайно популярного списка серверов SQL.

3. Немедленно восстановите поврежденную коллекцию SQL Server

После повреждения базы данных на SQL Server используйте EaseUS SQL Recovery, чтобы немедленно восстановить часть файлов веб-сайта и всю важную личную информацию.