none
We use MS SQL Server 2016 in our office space

    Question


  • We have common database where we store our every day data. This database updates day by day. Today it has bugged. At opening SQL Server we saw a message - The header for the file 'Common.mdf' is not a valid database file header. We launched SQL Server Management Studio to repair ms sql server database, but it could get back only some tables. Fortunately error at opening disappeared, but the rest of data is still inaccessible.

    Thursday, June 28, 2018 4:56 PM

All replies

  • If you have been taking regular database backups as you should, then you should be able to recover the lost data by restoring the most recent database backup.

    If you don't have backups then your only other option would be to try some of the repair options of DBCC CHECKDB. Please review documentation for guidance on how to accomplish this:
    DBCC CHECKDB (Transact-SQL)

    Please ensure that you have the most recent SQL Service Pack installed for best stability. If you don't know what version, just issue the following command in SSMS query window and post back output here:

    SELECT @@VERSION

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, June 28, 2018 5:08 PM
  • What is the state of the database? is it online?
    Thursday, June 28, 2018 5:14 PM

  • We launched SQL Server Management Studio to repair ms sql server database, but it could get back only some tables. Fortunately error at opening disappeared, but the rest of data is still inaccessible.

    Did you mean you have already performed the repair? If yes, what repair type? REPAIR_ALLOW_DATA_LOSS?

    What does the following command return now?

    DBCC CheckDB ('DBNAME') WITH NO_INFOMSGS, ALL_ERRORMSGS

    If you've already repaired with data loss option (and notice missing tables), then your only best bet to get the missing data back is by restoring a most recent good backup of the DB. If you don't have one then, unfortunately, the missing data has gone for good.  

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Thursday, June 28, 2018 5:32 PM
  • I just hope you have  latest good backup,  have you looked into ERROR.LOG ? What errors have you seen over there?

    https://blog.sqlauthority.com/2017/02/07/sql-server-header-file-path-not-valid-database-file-header-pageaudit-property-incorrect/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Friday, June 29, 2018 3:10 AM
    Moderator

  • We have common database where we store our every day data. This database updates day by day. Today it has bugged. At opening SQL Server we saw a message - The header for the file 'Common.mdf' is not a valid database file header. We launched SQL Server Management Studio to repair ms sql server database, but it could get back only some tables. Fortunately error at opening disappeared, but the rest of data is still inaccessible.

    You should not have run the repair this is the blunder lot of people do when dealing with corrupt database. Repair should be the last option when you have exhausted all the possible ways to get the data. I am sure you could have put database in emergency mode, which would have allowed you to access database, and you could have extracted as much data as possible. Now would have tested how much of data you could recover. After that you could have run repair .

    There are lot of 3rd party tools which does good job in this field, I happen to use one lately. If repair has deleted the data you have no option to recover that.

    Let me also add there is no replacement of good backup so start taking one.

    BTW now when you run checkdb what does it return, does it return any error


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, June 29, 2018 6:55 AM
    Moderator