Hello, we are migrating our SQL Server database and one of the tasks is to stop using Filestream. I am wondering how do I extract all the actual files from the filestream data file? How can I get access to the actual files so that we can store them elsewhere? I have tried doing google searches and I can't find anything that actually works or tells me how to extract the files.
Any help is appreciated!
Thanks!
June 19, 2024 at 9:44 am
Have you checked the documentation for Filestream.
The files are not stored in filestream data file, the symbolic links to the OS level file are stored.
I strongly recommend you read all the docs to understand what you're dealing with
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 19, 2024 at 5:16 pm
Yes, I have been scouring documentation for the past 2 days and finding it very difficult to get information on this feature.
I am confused by your statement though because when I performed a backup of the database to a completely fresh server, the filestream files came with it because they are in the filestream data file... if they weren't, then the documents would not have restored with the backup... right? I can navigate to the directory and see all the files when you drill into them... but they are not recognized files.
I am experimenting with using SQL Developer to migrate the sql server database to oracle - but I'm not sure if sql developer is smart enough to convert the filestream type?
But I'm still at a loss to figure out how you extract the files in order to move them to a different dbms. We are in AWS and right now we are using EC2 SQL Server. We either want to move to Oracle RDS or move to SQL RDS and SQL RDS does not support filestream...so either way, I have to convert the data. Each file currently has a unique identifier and primary key record... so I cannot lose that association either.
But I have not been able to locate any reliable documentation on how to get the files out of sql server so they can be migrated. I can't even get it to recognize the .pathname() method as it throws an error when I try to query that.
Thanks!
June 19, 2024 at 6:07 pm
Yes, I have been scouring documentation for the past 2 days and finding it very difficult to get information on this feature.
I found multiple docs on first search
I am confused by your statement though because when I performed a backup of the database to a completely fresh server, the filestream files came with it because they are in the filestream data file... if they weren't, then the documents would not have restored with the backup... right? I can navigate to the directory and see all the files when you drill into them... but they are not recognized files.
correct, file stream data is included in the database backup, when restoring the backup you specify a location and not a file name for the FS data.
the FS catalogs hold references to the NTFS level files.
I am experimenting with using SQL Developer to migrate the sql server database to oracle - but I'm not sure if sql developer is smart enough to convert the filestream type?
developer will work.
But I'm still at a loss to figure out how you extract the files in order to move them to a different dbms. We are in AWS and right now we are using EC2 SQL Server. We either want to move to Oracle RDS or move to SQL RDS and SQL RDS does not support filestream...so either way, I have to convert the data. Each file currently has a unique identifier and primary key record... so I cannot lose that association either.
But I have not been able to locate any reliable documentation on how to get the files out of sql server so they can be migrated. I can't even get it to recognize the .pathname() method as it throws an error when I try to query that.
Thanks!
you need to have a better grasp on FS before going further, check this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 19, 2024 at 6:13 pm
Thank you, I read all that... it didn't really tell me how to do what I wanted. 🙂 I also can't figure out why the .pathname method throws an error when everything is setup properly.
Thank you for confirming that sql developer will accomplish what I wanted to move to Oracle. I am still unsure how I would convert it from SQL Server to SQL Server... would it work to just create a normal blob table and then do a straight insert within sql management studio?
June 19, 2024 at 6:28 pm
You have to export to "regular" files, and then import those files from the filesystem into your database of choice (though I'd recommend at least considering storing files w/ operating system and just storing paths in the database).
e.g.:
https://www.sqlshack.com/export-sql-server-filestream-objects-with-powershell-and-ssis/
https://stackoverflow.com/questions/61200987/how-do-i-retreive-a-file-from-filestream
June 19, 2024 at 7:33 pm
The customer does not want to spend money on code changes really.... so I have to keep everything basically the same but get rid of filestream and move to either RDS SQL Server or Oracle.
June 19, 2024 at 8:28 pm
I assume you have t-sql access to filestream data, as shown here: https://learn.microsoft.com/en-us/sql/relational-databases/blob/access-filestream-data-with-transact-sql?view=sql-server-ver16
If you don't want that data in filestream, create another column or table with a varchar(max) column and insert the data into that table/column. Then drop the filestream tables. This link has more info: https://www.mssqltips.com/sqlservertip/5858/how-to-remove-filestream-from-a-sql-server-database/
June 19, 2024 at 8:45 pm
Thanks Steve,
Forgive my ignorance... but the filestream datatype is varbinary... if I create another table with a varchar(max) data type and insert the data into the new table ... how does that retain the actual files? Wouldn't they be lost?
The link you provided for removing it completely is very helpful though thanks...
June 19, 2024 at 9:02 pm
sorry, varbinary(max), not varchar. Too used to typing that.
The data is stored in the NTFS filesystem, but you can access it through T-SQL as with any other data. You don't get the file per se, you get the stream.
If you want the files, you could stream from a T-SQL table and write out a file if you want.
Alternatively, use the Win32 API, access the Filestream files and stream back out to a file. If you haven't enabled FileTable, you don't have files per se, you have binary streams.
June 19, 2024 at 9:10 pm
So, just to be clear... if I create a brand new table with a varbinary(max) column and then insert the data from the filestream table into that table, I can then remove the filestream table (per the method described in your link) and everything will still work exactly the same way?
I dunno why I can't wrap my head around this... but I'm so confused how creating a brand new table that ISN'T filestream... still keeps the files intact. Where do they go if they aren't in filestream?
June 19, 2024 at 9:46 pm
Thank you for confirming that sql developer will accomplish what I wanted to move to Oracle.
never confirmed that, that’s a whole different ball game.
to get access to the raw files on the ntfs share you need to enable the sql server option.
have a thorough read and get familiar with the feature before attempting any migration
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply