Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

Completely Removing FILESTREAM features from a SQL Server 2008 database

Mar 11 2010 8:05PM by Jacob Sebastian   

Configuring a software feature is usually much harder than removing it. Most of the times, removing a feature could be just a matter of a mouse click or a simple command/statement.

So what do you think about this question? Enabling FILESTREAM storage on a database and removing it completely from a database – which one is harder?. Well the correct answer to this question may be ‘It depends!’.

Removing FILESTREAM features completely from a SQL Server 2008 may be a little bit trickier than you would expect. This post explains how to remove the FILESTREAM storage features from a database.

Why would someone ever want to remove the FILESTREAM storage features? Well, this is not something that you will frequently do. One scenario could be while moving the production database to a dev or testing environment where the FILESTREAM data is not relevant any more. In such a case, to reduce the database size, one might decide to remove FIELSTREAM storage completely from the database.

Removing FILESTREAM Storage from a database

At first look, it looks like removing FILESTREAM features from a database will be just a matter of the dropping the FIELSTREAM columns and removing the FILESTREAM filegroup from the database.

Unfortunately, it is a little bit more complicated than that. To completely remove FILESTREAM features from a database, you need to perform the following steps.

  1. Delete all FILESTREAM columns from all tables
  2. Disassociate tables from FILESTREAM filegroups (we will see this shortly)
  3. Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)
  4. Remove all FILESTREAM filegroup (there may be more than one of them)

Let us now see how to do this. Before we perform the above operations, we need to have a database with FILESTREAM features. Run the script below to create such a database.

-- Create a FILESTREAM Enabled Database
CREATE DATABASE NorthPole 
ON
PRIMARY ( 
    NAME = NorthPoleDB, 
    FILENAME = 'C:\Data\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS_FG CONTAINS FILESTREAM( 
    NAME = NorthPoleFS_FILE,
    FILENAME = 'C:\FS\NorthPoleFS')
LOG ON ( 
    NAME = NorthPoleLOG,
    FILENAME = 'C:\Data\NorthPoleLOG.ldf')
GO

-- CREATE a TABLE with FILESTREAM columns
USE NorthPole
GO
CREATE TABLE [dbo].[Items](
   [ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [ItemNumber] VARCHAR(20),
   [ItemDescription] VARCHAR(50),
   [ItemImage] VARBINARY(MAX) FILESTREAM NULL
)

Dropping FILESTREAM Columns

So lets get into the destructive mood. Our job is to remove the FILESTREAM storage completely from this database. Let us start with dropping the FILESTREAM column from the Item Table.

ALTER TABLE Items DROP column ItemImage

In a real-world scenario there may be more than one table with FILESTREAM columns and each table may have more than one FILESTREAM columns. You need to drop all of them.

Disassociating tables from FILESTREAM filegroups

This is a tricky step and often confusing. Each table that has one or more FILESTREAM columns is associated to a FILESTREAM filegroup in the database. This association is usually made when the table is created. If your database has more than one FILESTREAM filegroup, you can specify in which filegroup you wish to store the FILESTREAM data of that table.

This association exists even after you drop all the FIELSTREAM columns. To SQL Server, it will still look like the FILESTREAM filegroup file is in use. So before deleting the FILESTREAM filegroup file (FILESTREAM Data Container), you need to remove this association. The following TSQL code does this.

-- Disassociate table "items" from its FILESTREAM file group
ALTER TABLE Items SET (FILESTREAM_ON = "NULL")

Removing FILESTREAM filegroup files

The next step is to remove the FILESTREAM filegroup file. The following TSQL code shows how to do this. If the database has more than one FILESTREAM filegroup, you need to do it for the filegroup file of each of them.

-- Remove the File
ALTER DATABASE NorthPole Remove file NorthPoleFS_FILE;

Removing FIELSTREAM filegroups

And finally, you are ready to drop the FILESTREAM filegroup. The following TSQL code snippet demonstrates it. If the database has more than one FILESTREAM filegroup, the operation needs to be performed for each filegroup.

-- Remove the File Group
ALTER DATABASE NorthPole REMOVE FILEGROUP NorthPoleFS_FG;

If you have got a FILESTREAM question, feel free to post it to this forum.

Tags: FILESTREAM, TSQL, SQL SERVER, SQL, jacob sebastian,


Jacob Sebastian
1 · 100% · 32220
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]