Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am considering moving all my customers' DBs to SQL Azure. I am using a single FILESTREAM field to store files in blobs (not for performance on large blobs), but simply because I am using the Express Edition and so I use Filestream as a way not to hit the Express DB size limit).

I have this table, the DOCUMENT field contains the filestream data and the GUID field is needed for filestream implementation:

CREATE TABLE [dbo].[DOC_FILES](
    [ID_DOC_FILE] [int] NOT NULL,
    [DOCUMENT] [varbinary](max) FILESTREAM  NULL,
    [GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL
 CONSTRAINT [PK_DOC_FILES] PRIMARY KEY CLUSTERED 
(
    [ID_DOC_FILE] ASC
) ON [PRIMARY] FILESTREAM_ON [MyFileStreamFileGroup],
UNIQUE NONCLUSTERED 
(
    [GUID] ASC
) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [MyFileStreamFileGroup]

How to remove the filestream in the best way?

One technique I have in mind is simply:

1) renaming the DOCUMENT field to DOCUMENT_TEMP

2) creating a new DOCUMENT (not FILESTREAM) varbinary(max) field

3) copy and delete from DOCUMENT_TEMP to DOCUMENT one by one

4) altering the table to remove filestream [THIS IS NOT CLEAR TO ME]

Can you please suggest?

share|improve this question

2 Answers 2

When you create the tables in Azure simply create them as varbinary(max) and use SSIS or the import/export wizard to move the data.

If you wanted to convert an existing database to using the native varbinary you would need to follow the basic concept you have above.

  1. Add new column Document_temp
  2. Update table copying data from Document to Document_temp
  3. Drop the document column
  4. Rename the document_temp column to document.
  5. Remove the Guid if you don't need it (there is no harm in leaving it).
share|improve this answer
    
ok thanks, anyway I will post my personal answer since I found the solution. –  user193655 Oct 27 '10 at 12:02
up vote 0 down vote accepted

As explained [HERE][1] the procedure for totally removing filestream after having deleted all the filestream colmns is:

1) Disassociating tables from FILESTREAM filegroups

2) Removing FILESTREAM filegroup files

3) Removing FIELSTREAM filegroups

(see article for details)

[1]: http://beyondrelational.com/blogs/jacob/archive/2010/03/11/completely-removing-filestream- features-from-a-sql-server-2008-database.aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.