How to complete remove filestream and all attached files

Question

I have tried the FILESTREAM feature for MSSQL (2008R2 Data Center) on a local database, to experiment. The real database is running on a server. I have setup the whole FILESTREAM, using this query:

/* CREATE FILESTREAM AND FILESTREAM TABLE */
USE [master]
GO
ALTER DATABASE SenONew
ADD FILEGROUP [FileStream]
CONTAINS FILESTREAM
GO
ALTER DATABASE SenONew
ADD FILE
(
NAME = 'fsSenONew',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SenONew.ndf'
)
TO FILEGROUP [FileStream]
GO

USE [SenONew]
GO
CREATE TABLE Filestore(
    FileID int PRIMARY KEY,
    RowID uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
    FileDescr nvarchar(max),
    FileIndex varbinary(max) FILESTREAM NULL)
GO

And I was experimenting with adding a few files then deleting them.

Now since this was only meant to be an experiment, I also want to get rid of it. I'm using my local server for the development of the database that will be used on the real server, thus I'm creating BackUp's on my local server then Restore this on the real server, so it gets updated (software is in development, so the database structure changes much as well as the data and I need to do a full restore to the real server, where the software is being tested on).

After hours of searching, I couldn't find anything on my problem.

I understand that I need to:

1. Remove the database table storing the FILESTREAM information
2. I need to remove the FILE of the FILESTREAM
3. Remove the filegroup

So I'm using this query to get rid of everything I set up in the first place:

/* DROP FILESTREAM TABLE AND FILEGROUP */
USE SenONew
DROP TABLE Filestore
GO

ALTER DATABASE SenONew
REMOVE FILE fsSenONew

ALTER DATABASE SenONew
REMOVE FILEGROUP [FileStream]
GO

So I do everything as I should and it completes without error as well. So when I enter my filegroups, files and my file location, I see they are all completely removed:

But when I do a BACKUP of my local database (which include the deleted FILESTREAM, file path and filegroup) and try to restore the server with it, I get errors.

SQL to create a BACKUP:

/* CREATE BACKUP OF DATABASE WITHIN CURRECT CONNECTION */
DECLARE @FileName2 nvarchar(250)
SELECT @FileName2 = (SELECT 'C:\SenO BackUp\' + convert(nvarchar(200),GetDate(),112) + ' SenONew.BAK')
BACKUP DATABASE SenONew TO DISK=@FileName2
GO

Then do the Restore on the server:

/* CREATE RESTORE OF DATABASE WITHIN REAL SERVER CONNECTION */
use master
alter database SenONew set offline with rollback immediate;

DECLARE @FileName2 nvarchar(250)
SELECT @FileName2 = (SELECT '' + convert(nvarchar(200),GetDate(),112) + ' SenONew.BAK')
RESTORE DATABASE SenONew
FROM DISK = @FileName2

alter database SenONew set online with rollback immediate;

I get the following error:

*(Msg 5121, Level 16, State 2, Line 7 The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SenONew.ndf" is not in a valid directory.

Msg 3156, Level 16, State 3, Line 7 File 'fsSenONew' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\SenONew.ndf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 7 Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 7 RESTORE DATABASE is terminating abnormally. )*

I deleted the .ndf FILESTREAM location, why is it a specified path? Also, why is fsSenONew trying to restore? I can't get my head around it. Are there paths internally that I need to delete?

Answer 1

You can check:

SELECT * FROM SenONew.sys.data_spaces WHERE name = 'FileStream' 

it should return 0 rows.

There is a procedure to remove FILESTREAM features from a SQL Server 2008 database :

ALTER TABLE Filestore DROP column FileIndex
GO
ALTER TABLE Filestore SET (FILESTREAM_ON="NULL")
GO
ALTER Database SenONew REMOVE FILE fsSenONew
GO
ALTER Database SenONew REMOVE FILEGROUP [FileStream]
GO

as described in this article. But the steps you did should do the same thing.

Your problem is certainly strange, but I suggest that you try using following

USE SenONew
EXEC Sp_help 
EXEC Sp_helpfile 
EXEC Sp_helpfilegroup 

You may find something suspicious there like another table using that FILEGROUP.

I have done exactly the steps you describe and cannot reproduce your problem. Check how your Restore database screen looks like.

Answer 2

1.Remove the FILESTREAM attribute from columns and tables. You'll need to move data to a new column.

ALTER TABLE MyTable
ADD FileData varbinary(max) NULL;
GO
update MyTable
set FileData = FileStreamData
GO  
ALTER TABLE MyTable 
DROP column FileStreamData
GO
ALTER TABLE MyTable SET (FILESTREAM_ON="NULL")
GO
EXEC sp_RENAME 'MyTable.FileData', 'FileStreamData', 'COLUMN'
GO

2.Remove files from the FILESTREAM and drop the FILE and FILESTEAM.

ALTER DATABASE [MyDatabase] SET RECOVERY Simple 
GO

EXEC SP_FILESTREAM_FORCE_GARBAGE_COLLECTION 

ALTER DATABASE [MyDatabase] REMOVE FILE [MyFile]
GO
ALTER DATABASE [MyDatabase] REMOVE FILEGROUP [MyFileGroup]
GO

ALTER DATABASE [MyDatabase] SET RECOVERY FULL
GO