2

Is it possible to attach a DB that had a FileTable w/o the FileTable files?

We were hit with ransomware and the image files are corrupt but the mdf/ldf are fine. I can lose the images, but I really need to get the DB back. I have googled all around but can't seem to find a solution to this.

1
  • 6
    Have you tried restoring a backup?
    – Hannah Vernon
    Commented Sep 16, 2021 at 14:54

1 Answer 1

1

As others have mentioned, restoring from known good backups would be preferable.

However, if you have no backups...

You can try the hack-attach method to swap-in the mdf and ldf files, then directly update the system table to remove reference to the FILESTREAM file. I just tried this, and it seemed to work OK.

I suggest you do this on a non-Production Instance, if it works, you'll then be able extract the non-FILETABLE data, or backup the database and restore it elsewhere. If you resurrect or restore the database it will forever remain tainted, so I would say extracting the data to a new clean database would be safer in the long-term.

Most of the process is documented here:- https://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/

Quoting Paul Randal from the page above:-

  1. This only works if the server instance you’re using is the same version as the database you’re trying to attach
  2. Create a dummy database, with the same name, the same number of data and log files, with the exact same file names (extremely important!) and same file IDs (make sure you have instant file initialization enabled so the file creations don’t take ages – see this blog post). This can be tricky to do if you had added or removed files to the database before it was corrupted, but you need to make sure the file IDs are exactly the same.
  3. Set the dummy database offline (ALTER DATABASE RealDatabaseName SET OFFLINE) or shut the server down
  4. Drop in as many of the data and log files from the damaged database as possible
  5. Set the dummy database online (ALTER DATABASE RealDatabaseName SET ONLINE) or start the server if you shut it down for step 2

Step 2 is the crucial step, basically creating an empty shell database with the same file properties as the problem one.

After swapping-in the mdf and ldf files, when you attempt to bring the database online, it will fail and go into "Recovery Pending" with an error message similar to this:-

Msg 5173, Level 16, State 3, Line 20
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.
Msg 5105, Level 16, State 14, Line 20
A file activation error occurred. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\filestream1' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 5181, Level 16, State 5, Line 20
Could not restart database "Test". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 20
ALTER DATABASE statement failed.

In my testing, I was unable to access the database using EMERGENCY mode - as noted as a possibility in the linked page above.

I then restarted the Instance in single-user mode (-m), connected via DAC (ADMIN:ServerName) and deleted the reference to the FILESTREAM file:-

/* sys.sysbrickfiles underlies sys.master_files */
DELETE FROM sys.sysbrickfiles 
WHERE dbid = DB_ID('Test') 
AND lname ='filestream1';

Resulting in the message:-

Warning: System table ID 22 has been updated directly in database ID 1 and cache coherence may not have been maintained. SQL Server should be restarted.
   

I then restarted the Instance normally (without -m) and executed successful DBCC CHECKDBs on both [master] and the specific database.

The FILETABLEs will still be shown in the database, but will be unavailable (and can't even be dropped).

At this point, I would extract data out of the non-FILETABLE tables and copy to a new clean untainted database. The resurrected database could also be backed-up and restored elsewhere - however it would forever remain under suspicion (so I would prefer to extract the data and then destroy the tainted database).

If you don't have a non-Production Instance to perform these steps, I would do everything I could to create one, since doing this on the live server is dangerous and could make the situation worse. If you're not familiar with the steps above, a non-Production server to experiment/learn with is the way to go.

If in doubt, use backups, MS Support or a corruption recovery specialist.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.