Question on FILESTREAM integrity issue.

chrisrdba 361 Reputation points
2021-03-22T19:51:24.03+00:00

Greetings. I've got an integrity check that blows up for the reasons listed here.

I took the same actions listed for 9 files in non-prod, and now the integrity check works w/o issue.

Does anyone know of a good reason to not do this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-22T22:49:18.15+00:00

    I think the proper way would be to a point-in-restore of the filegroup. That would bring back the original files. Then again, given that they are infected, maybe you don't want them back.

    Now you still have a corrupt database in some meaning, although SQL Server does really notice. But if you would modify these files with an UPDATE, and you would later restore a backup from before the AV incident and apply logs, you might get a mess, because this will restore the files that were never updated.

    Then again, you could argue that this is largely academic.

  2. CarrinWu-MSFT 6,851 Reputation points
    2021-03-23T05:47:11.82+00:00

    Hi @chrisrdba ,

    From the link which you shared here, the Poster got an error after DBCC CHECKDB. If any errors are reported by DBCC CHECKDB, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. If no backup exists, running repair corrects the errors reported. The repair option to use is specified at the end of the list of reported errors. However, correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require deleting some pages, and therefore some data. Please refer to DBCC CHECKDB (Transact-SQL) to get more details.

    Does anyone know of a good reason to not do this?

    Do you mean the reason not to do DBCC CHECKDB? I suggest you do DBCC CHECKDB regularly. DBCC CHECKDB warns you of corruption so that you can fix it before it gets too bad. But if you have a very large database, running a full DBCC CHECKDB will take some or more time. You can refer to CHECKDB (Part 6): Consistency checking options for a VLDB to get more information about the plan for consistency checking options for very large databases.

    If I misunderstand your questions, please let me know and describe your question more clearly. Thank you.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-29T21:29:18.803+00:00

    How does one tie the user data to the documents?

    I guess you that your question is "how do I find the rows to which the missing document belongs".

    I will to confess that I don't know, and I don't really have the time to research for the next few days.

    One idea is to loop over the table and for every row select datalength(filestreamcol) before you restore the files and trap any errors. Not that I know if you get errors - maybe you only get NULL back. While you could do this in T-SQL, it may be better to do it in PowerShell, because the you get may be vile and terminate the session.

    Then again, what if you do this after you have added the files back? You could insert simple text files with a known message.

    Then again, it should be able to use the ROWGUIDCOL in the filestream table, to map the file, but you would need to poke around to figure out how that mapping is.

    If you have a multiple tables with FILESTREAM column it gets worse, since you would need to know which table. Or try all of them.