troubleshooting Question

Cannot get rid of Blob Data in Filestream enabled database

Avatar of hcaltenco
hcaltencoFlag for Denmark asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
2 Comments1 Solution818 ViewsLast Modified:
Hello,

It seems that I have the same data stored in both ways, blobs (inside database) and in file system. I would like to get rid of the data stored in blobs and only have the one in file system.

I have a database that started up using tables for blob storage of images inside the database table. As we started to support other type of media storage, I wanted to move the data files to File Sytem.

I used the following guide to enable FILESTREAM in the corresponding table of the database:
http://www.ceservices.com/adding-filestream-existing-table-database-sql-2008-r2

It sort of worked, since after copying the data to the filestream column, I can see folder where the data files are stored:
E:\sqldata\db_files\a6a081e7-...\

However, even if I dropped the old non-filestream column, the database still occupies the same space as before and I dont have more free space in the database file (70 GB mdf file with only 5 GB free space).

If I make a backup instead of my previous 63 GB backup, I get a 120GB backup, which means that data is stored in both, blobs and file system. How can I get rid of the data stored in blobs. I already dropped the old column that did not include FILESTREAM.

To try to solve this issue, I tried to create a new database from the original schema (Tasks->Generate Scripts in Sql management studio) and Enable FILESTREAM in the database and the corresponding table from the script. I also had to modify the stored procedures to include the new guid column necessary for FILESTREAM.

Afterwards I use the Import and Export Wizzard to import data from the original database. I get fairly small mdf and ldf files and I can see the data files in the file system folders.

However, I get tons of exceptions when attaching that new database to the web application. I would prefer to avoid having to change all the API to run with the new database and new stored procedures just to find out that it was not possible for some other reason.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros