Attach DB without FileTable folder
-
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.
-
I don't know how to to what you ask but we backup all files when they're created and then again if they change. For us, there would be no problem because we'd "just" need to do a restore.
Yeah, I know. Sorry about the mostly useless (at this point) post but it will act as a "bump" in the forum to try to help a little bit. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Unfortunately our backups were encrypted by the ransomware. We were only able to recover the raw mdf/ldf's via a recovery service. Luckily, they were not encrypted since they were on a VM and only the VM file was encrypted. The Backups were on a bare metal file server.
-
Thanks for the feedback, Mike. You're the second person I've heard of (I'm sure there are more that I haven't) that had the backups encrypted by ransomware. I think I need to "hide" were the backups are stored by removing the directory paths from the database setup and purging the backup logs after every backup.
I wish I could help but I don't know anything about File Tables. Once again, the best I can do for you is my post will bump this thread back up to the top. Hopefully, someone will be able to answer it for you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What happens when you try to attach the MDF ?
Could you create a new database with the original name, create a filetable for it, detach the new database, then attach the live MDF using the empty filetable you just created ? (Disclaimer...I don't know much about filetables)
-
If I try to use a FileTable folder from a different DB I get this error:
Msg 5173, Level 16, State 2, Line 2
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 1802, Level 16, State 7, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
-
When you say corrupt, do you mean they are encrypted? Are the system files/folders in the FileStream folder available? If so, I'd attach the db with this stuff, then truncate the Filetable table to remove the corrupt images.
I asked in the MVP forum and this was the suggestion. No one seems to have other ideas.
I created a file table and added a file. I then detached this db and moved the mdf/ldf. I renamed the folder for Filetable.
I then created a new db, same Filetable path, to set a blank FT folder. I could not attach the original mdf/ldf, getting a file path error. This was with everything the same. I thought this might work as I've have the filestream.hdr file and $FSLog, but it didn't.
If I renamed the old folder back, I could. Somehow the data in the filestream folder is linked to the database itself.
Without the original system file/folder for filetable, I'm stumped. I'll bump to MS.
I
-
Well, that's a critical situation as backup is also encrypted. Did you get any key from the hacker to decrypt the file?
In my org, we have faced the ransomware issue. One of my colleagues took the help of SQL repair software and fixed the problem; unfortunately, we. We did not recover 100% data, but yes, we have recovered 80% data.
If you want to try, then I can share the software link.
SQL Database Recovery Expert 🙂
-
From MS, this suggestion:
If the user really does not care about the data in the filestream folder structure, it is possible but they would have to drop and recreate the filetable afterwards.
The key to re-attaching the database without getting errors would be to recreate the filestream filegroup path(s) (the entire subdirectory structure would not need to be recreated but up to at least the first level), and if it is possible to also get a copy of a known good filestream header file (filestream.hdr) since the database will look for that metadata during startup. That file (or files if there were multiple filegroups involved) might have also been encrypted since it is in the “root” folder of the filestream path(s). If there is a unaffected backup available, I would just grab the filestream.hdr file from a restore.
When I refer to restoring the path, I am referring to first folder that would contain the filestream log, garbage collection and header file. Not the folders that contain the filestream columns and data. As an example (only the $FSGC, $FSLOG):
The other thing to keep in mind is to make sure that the filestream folder structure has at a minimum the permission added back for the SQL Server service sid (NT Service\MSSQLSERVER for a default instance as an example). Hopefully he has a backup pre-ransomware that has the header.
Other than that a normal attach (i.e. create database for attach) should work without any additional parameters.
-
One other note, this info from MS assumes you have a valid .hdr file from this database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply