How to add FILESTREAM attribute to an existing column?

Article
11/24/2009

Question

_{Tuesday, November 24, 2009 11:56 PM | 1 vote}

I have a table that uses the FILESTREAM attribute for SQL Server 2008. If I edit it in the 2008 SSMS table designer, the FILESTREAM attribute is lost. Most folks say to use ALTER to put it back. Problem is that every example I see shows creating a brand new table or brand new column. I can drop and re-add the column but then I lose my data. What happens if I have a table in production with existing filestream data and need to put the FILESTREAM attribute back on the column without losing the data in the column?

I tried and get this:

Cannot alter column 'Data' in table 'accnt_attachment' to add or remove the FILESTREAM column attribute.

Obviously I do not have the correct syntax. An example would be greatly appreciated.

Thanks!
Don

All replies (15)

_{Sunday, October 10, 2010 8:27 PM ✅Answered | 1 vote}

Here is what I suggest.

1. Rename the current table to zzzYourTable

2. Create YourTable with the FILESTREAM attribute on designated column(s)

3. Move data over with INSERT YourTable(....) SELECT .... FROM zzzYourTable

4. Apply indexes/constraints to YourTable

Demo follows:

USE AdventureWorks2008
GO

CREATE TABLE Document(
    DocumentNode hierarchyid NOT NULL PRIMARY KEY,
    DocumentLevel AS (DocumentNode.GetLevel()),
    Title nvarchar(50) NOT NULL,
    [Owner] int NOT NULL,
    FolderFlag bit NOT NULL,
    [FileName] nvarchar(400) NOT NULL,
    FileExtension nvarchar(8) NOT NULL,
    Revision nchar(5) NOT NULL,
    ChangeNumber int NOT NULL,
    [Status] tinyint NOT NULL,
    DocumentSummary nvarchar(max) NULL,
    Document varbinary(max) FILESTREAM NULL,
    rowguid uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE,
    ModifiedDate datetime default(getdate())) 
ON [PRIMARY] FILESTREAM_ON DocumentFileStreamGroup
GO

INSERT INTO [AdventureWorks2008].[dbo].[Document]
   ([DocumentNode]
   ,[Title]
   ,[Owner]
   ,[FolderFlag]
   ,[FileName]
   ,[FileExtension]
   ,[Revision]
   ,[ChangeNumber]
   ,[Status]
   ,[DocumentSummary]
   ,[Document]
   ,[rowguid]
   ,[ModifiedDate])
SELECT [DocumentNode]
   ,[Title]
   ,[Owner]
   ,[FolderFlag]
   ,[FileName]
   ,[FileExtension]
   ,[Revision]
   ,[ChangeNumber]
   ,[Status]
   ,[DocumentSummary]
   ,[Document]
   ,[rowguid]
   ,[ModifiedDate]
 FROM Production.Document
GO
-- (13 row(s) affected)
SELECT * FROM Document
GO
/* Partial results

DocumentNode    DocumentLevel   Title
0x  0   Documents
0x58    1   Overview
0x5AC0  2   Introduction 1
0x5B40  2   Repair and Service Guidelines
0x68    1   Maintenance
0x6AC0  2   Crank Arm and Tire Maintenance
*/

DROP TABLE AdventureWorks2008.dbo.Document
GO

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

_{Monday, October 11, 2010 9:53 PM ✅Answered}

> The Table Designer's lack of support for FILESTREAM storage is certainly an issue. Nonetheless, the Table Designer is an OK GUI tool. Certainly appreciated by non-experts to perform table design or modification easily.

Just because something looks simple, does not mean that it is. And just because you don't pay attention to the bugs, it does not mean that they aren't there.

Keep in mind that SSMS is a tool that ships to manage enterprise databases. You don't modify tables in an enterprise database with point-and-clicking. Or, rather, if you do: you should have a solid tool that guides you through the process. Changing the schema in a 24/7 database, is something which can require careful planning, and it is definitely not a simple task. A good tool that could help people to plan such changes could certainly be welcome.

Here is a list of flaws with the Table Designer. The list is not likely to be exhaustive:

1) It many cases where ALTER TABLE would be the natural option, the Table Designer renames the existing table, creates the new schema and copy data over.

2) In the above process, the transaction scope for the changes is incorrect.

3) If you create a script from SSMS, rather than running it directly, there is no error handling, so if a statement fails and the transaction is rolled back, the rest of the script is executed nevertheless.

4) Constraints are restored with NOCHECK, which means that the optimizer cannot trust them. This can affect performance.

5) Do this: open a table, make a change to it. Generate a change script, but do not run it. Close the table without saving (you changed your mind). Open another table, make a change. Generate a change script and review it. You will find that the change from the first table is there!

It is obvious that the people who originally implemented the Table Designer had very little understand of what it takes to make schema changes. And, no, it is by no means a simple task. The rename-create-copy-drop dance is sometimes necessary, but it can have severe consequences in a production database. The tool should tell you that this will happen. And it should also offer you batching possibilities. And, certainly, having a tool that generates a template for this operation is good, as doing all by hand is tedious with all foreign keys.

For a pure developer environment like Visual Studio, the Table Designer may be OK, but in most production environments it is dangerous.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and SQL 2000.
(Just click the link you need.)

_{Wednesday, September 21, 2011 12:37 AM ✅Answered | 3 votes}

Here's what I've used.

/* rename the varbinary(max) column
eg. FileData to xxFileData */
sp_RENAME '<TableName>.<ColumnName>', 'xx<ColumnName>' , 'COLUMN'
GO

/* create a new varbinary(max) FILESTREAM column */
ALTER TABLE <TableName>
ADD <ColumnName> varbinary(max) FILESTREAM NULL
GO

/* move the contents of varbinary(max) column to varbinary(max) FILESTREAM column */
UPDATE <TableName>
SET <ColumnName> = xx<ColumnName>
GO

/* drop the xx<ColumnName> column */
ALTER TABLE <TableName>
DROP COLUMN xx<ColumnName>
GO

_{Wednesday, November 25, 2009 1:15 AM}

Don,

Is there data currently in that column? What is the data type?Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

_{Wednesday, November 25, 2009 2:41 PM}

It is varbinary(max) and yes it already had data. That is why I need to understand how to add the FILESTREAM attribute back to the column without destroying existing data.

Imagine you have a production table with thousands and uploaded files in varbinary(max) column but stored in the file system using the FILESTREAM capability in 2008. A new or existing developer forgets and makes a simple table modification using SSMS. Suddenly the uploads begin to fail. The DBA finally determines that the FILESTREAM attribute has been removed from the varbinary column on this table. How to you place the FILESTREAM attribute back on the varbinary(max) column without destroying the existing data?

Thanks so much!

_{Wednesday, November 25, 2009 8:24 PM}

Don,

Action items:

You can file bug report at: https://connect.microsoft.com/SQLServer?wa=wsignin1.0

2. If you can just restore the db from a good backup, the simplest solution

If 2 is not a possibility, I suggest you contact Microsoft support
I also suggest you keep developers away from production. Pretty typical environments in IT shops: development, QA, staging, production & standby.

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

_{Wednesday, November 25, 2009 10:36 PM}

Looking at the ALTER TABLE, it doesn't seem possible to do this (and I wouldn't have expected it, really, this would be a hard thing to implement.) I think the error message pretty much agrees with that too.

You will just need to add a nullable varchar(max) column and do an update:

update accnt_attachment
set dataNew = data

then drop the old column and rename the existing column. A bit painful, but frankly I wouldn't expect that sort of thing with a new feature like this. I think if you use a tool like SSMS's table editor, or even Red-Gate's comparison tool, it would do most of the heavy lifting for you.
Louis

_{Thursday, December 3, 2009 4:06 PM}

Don,

Any progress?Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

_{Sunday, October 10, 2010 7:37 PM}

Was there any solution to this scenario?

_{Monday, October 11, 2010 1:15 PM}

Thanks, at least theres an option should someone modify the table.

Are there any plans to support filestream in the next SSMS?

_{Monday, October 11, 2010 1:19 PM}

> Are there any plans to support filestream in the next SSMS?

If you are thinking of the Table Designer, I don't know what plans there are. But the Table Designer is rotten from the core, my advice would to Microsoft that they just drop it. In fact, they should have done so long ago.

For you as a user, the only advice I can give is to stay away from it.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and SQL 2000.
(Just click the link you need.)

_{Monday, October 11, 2010 2:51 PM}

Erland,

The Table Designer's lack of support for FILESTREAM storage is certainly an issue. Nonetheless, the Table Designer is an OK GUI tool. Certainly appreciated by non-experts to perform table design or modification easily.

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

_{Tuesday, October 12, 2010 12:13 AM}

Thanks Erland. Notes:

I could not reproduce 5.

>The rename-create-copy-drop dance is sometimes necessary, but it can have severe consequences in a production database. The tool should tell you that this will happen. And it should also offer you batching possibilities.

Agreed. A senior DBA knows to use scripts instead, however, a novice DBA, or reluctant/involuntary/casual developer-turned-into-DBA can get really burned.

>For a pure developer environment like Visual Studio, the Table Designer may be OK, but in most production environments it is dangerous.

Agreed. It is a good tool in development environment.

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

_{Tuesday, March 8, 2011 9:52 PM}

I had #3 hit me just today. Ended up dropping the old table after it could not create the new one. Requiring me to contact the "real" DBAs to do a restore.

As a dev tool for those of us that aren't primarily SQL, table designer is a valuable tool to just get the basic table there and make minor changes.

Paul

_{Monday, April 25, 2011 7:38 PM}

If the table editor is that unreliable and they don't want to fix it then it should be removed from the product. This bug effectively makes the filestream attribute unusable. Thank God I found this before putting my database into production. At some point in the future some unsuspecting soul would use the table designer and lose years worth of stored files.

Share via

How to add FILESTREAM attribute to an existing column?

Question

All replies (15)

Additional resources