SQL Server 2014 – How to Remove Filestream from a Database

filestreamsql serversql server 2014

I am trying to remove filestream from my database, so I have to do the following

I would like to know how to remove the following:

ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );`

Then I need to Add a column with no filestream which probably is the following code:

ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max );
Update [AMGR_Letters_Tbl]
SET TextCol1 = TextCol;
ALTER TABLE [AMGR_Letters_Tbl] DROP COLUMN TextCol;
/*Now i just rename the column*/

I also need to fix the FTS index (Which i do not understand) after which is the following:

    IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
                EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
            END

/****** I need to fix filestream on both tables:  ******/
/****** This is what the whole thing looks like (for 2 tables):  ******/
IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
    if not exists   ( select name from syscolumns where id IN 
                ( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Letters_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
                ) AND name = 'UId' )
    BEGIN
        -- add unique ROWGUIDCOL column
        ALTER TABLE AMGR_Letters_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
        ALTER TABLE AMGR_Letters_Tbl ADD CONSTRAINT AMGR_Letters_Tbl_UId_Unique UNIQUE( UId );
        EXEC( 'ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max ) FILESTREAM NULL' );
        -- will need to re-create FTS index after
        IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
            EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
        END
        EXEC( 'UPDATE AMGR_Letters_Tbl SET TextCol1 = TextCol;' );
        ALTER TABLE AMGR_Letters_Tbl DROP COLUMN TextCol;
        EXEC sp_rename 'AMGR_Letters_Tbl.TextCol1', 'TextCol', 'COLUMN';
        EXEC ('DBCC CLEANTABLE (0,''AMGR_Letters_Tbl'',1000) WITH NO_INFOMSGS;');
    END
END 
GO
------------------------------------------------------------

IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
    if not exists   ( select name from syscolumns where id IN 
                ( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Documents_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
                ) AND name = 'UId' )
    BEGIN
        -- add unique ROWGUIDCOL column
        ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
        ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );
        EXEC( 'ALTER TABLE AMGR_Documents_Tbl ADD Data1 varbinary( max ) FILESTREAM NULL' );
        -- will need to re-create FTS index after
        IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Documents_Tbl' ), 'AMGR_Documents_Record_Id', 'IsFulltextKey' ) BEGIN
            EXEC sp_fulltext_table AMGR_Documents_Tbl, 'drop'
        END
        EXEC( 'UPDATE AMGR_Documents_Tbl SET Data1 = Data;' );
        ALTER TABLE AMGR_Documents_Tbl DROP COLUMN Data;
        EXEC sp_rename 'AMGR_Documents_Tbl.Data1', 'Data', 'COLUMN';
        EXEC ('DBCC CLEANTABLE (0,''AMGR_Documents_Tbl'',1000) WITH NO_INFOMSGS;');
    END
END 
GO

Best Answer

To completely remove FILESTREAM features from a database, you need to perform the following steps.

  • Delete all FILESTREAM columns from all tables
  • Disassociate tables from FILESTREAM filegroups
  • Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)
  • Remove all FILESTREAM filegroup (there may be more than one of them)

Walkthrough Click Here and follow his steps

Another Note: if you have Data in your FileStream file you will not able able to delete it. sp_filestream_force_garbage_collection unfortunately this only works >= SQL Server 2012
For that fix you can See the Whole Thing Here

If that doesn't work. try to make a backup for it. That is how I cleared my FileStream file.

When it is Cleared then you can Delete it FileStream File and FILESTREAM filegroup.

If your database in heavily indexed, you should check you Functions and View, some might go missing. (Have a Back up of that)