Cannot get rid of Blob Data in Filestream enabled database
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\a6a081
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.
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\a6a081
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER