SQLTeam.com | Weblogs | Forums

No resources to solve an issue

I have many sql server databases in my computer. I am using SQL Server 2008 R2. But two days ago I got: SQL_1.mdf is not a primary database file. I have no idea what to do after this error. Any variants for recover sql server.

Data in MDF files are stored as pages. The header page is the first page containing crucial database information like file signature, size of the file, compatibility, etc. The actual data is stored in the rest of the pages.

In case these are not recognized by the SQL Server, the entire MDF file is not considered as a valid file. This results in the Error 5171. For solution have a look on SQL Server Error 5171
If this also not resolves the problem then only try this using SQL Server Database Recovery Tool.

If you have got a recent backup I would suggest restoring that to a DIFFERENT, NEW, TEMPORARY !! database name (on a different / DEV server if you like) and checking if everything in that DB is OK. You can use DBCC CHECKDB to test that the restored, temporary-name, database is in good shape.

If that restore is still broken try earlier backup files until you find a Known Good Backup File that restored and tested OK.

Then decide if you can restore the original database from that backup file (AND repeat all the work done since).

If you do NOT have a backup that you can restore from then after you sort out this mess put in a place a proper, frequent, backup strategy and ALSO a strategy to TEST that your backup files are GOOD.

That should include using Page CRC Verification IN the database file and ALSO during both the BACKUP and RESTORE processes. That will give you the earliest possible warning as soon as anything bad happens to your database.

Also perform periodic RESTORE of backups (maybe once a week is enough??) to a different server, to prove that they can be restored :slight_smile:, and then preform a DBCC CHECKDB on that (restored) database copy. All this can be automated, so once set up it will just run regularly and give you some warning when something goes wrong.

Separate to that:

Find what caused the file to become corrupted. Most likely it is a faulty disk controller or memory. Repair that BEFORE committing valuable data to your databases. Or move the databases to another server with no hardware faults until you are sure that the original server is healthy.

(I suppose it could also be some idiot opening the MDF file with, say, Notepad and pressing SAVE ... hopefully its not as simple as that, and IF the database was Open in SQL (which is presumably what normally happens) then the O/S would not have allowed the file to be edited in that way,, so its "unlikely" unless you are using Auto Close on the DB, or SQL Service was stopped)