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.
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 2012For 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)