Home / server / Questions / 194432
Accepted
user193655
Asked: 2010-10-26 01:50:15 +0800 CST

Migrating from filestream to "classic" blob

  • 772

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?

sql-server filestream

2 Answers

  1. 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).
    • 1
  2. Best Answer

    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

    • 1