none
Upgrade varbinary(max) to varbinary(max) FILESTREAM RRS feed

  • Question

  • Is there any guidance/issues relating to upgrade from SQL 2005 varbinary(max) to SQL 2008 varbinary(max) FILESTREAM.

    Thursday, August 7, 2008 10:01 AM
    Moderator

Answers

  • OK, I ended up doing this:

     

    Code Snippet

    -- Initial schema:

    CREATE TABLE [dbo].[Pictureimage](

               [PictureId] [uniqueidentifier] NOT NULL,

               [Image] [varbinary](max) NOT NULL,

               [OriginalImage] [varbinary](max) NOT NULL,

               [Version] [timestamp] NOT NULL,

     CONSTRAINT [PK_Pictureimage] PRIMARY KEY CLUSTERED

    (

               [PictureId] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

     

    -- Attach 90 db…

     

    -- Set Comp level

    EXEC sp_dbcmptlevel Amigo, 100;

    GO

     

    -- Create FILESTREAM filegroup

    ALTER database Amigo

    ADD FILEGROUP fsfg_Amigo

    CONTAINS FILESTREAM

    GO

     

    --Add a file for storing database photos to FILEGROUP

    ALTER database Amigo

    ADD FILE

    (

        NAME= 'fs_Amigo',

        FILENAME = 'C:\fs_Amigo'

    )

    TO FILEGROUP fsfg_Amigo

    GO

     

    -- Migration to FILESTREAM

     

    ALTER TABLE dbo.PictureImage

    SET ( FILESTREAM_ON = fsfg_Amigo )

    GO

     

    ALTER TABLE dbo.PictureImage

    ALTER COLUMN PictureId ADD ROWGUIDCOL

    GO

     

    ALTER TABLE dbo.PictureImage

    ADD OriginalImageFS varbinary(MAX) FILESTREAM NULL;

    GO

     

    UPDATE dbo.PictureImage SET OriginalImageFS = [OriginalImage];

    GO

     

    ALTER TABLE dbo.PictureImage

    DROP COLUMN OriginalImage;

    GO

     

    EXEC sp_rename 'AmigoProd.dbo.PictureImage.OriginalImageFS', 'OriginalImage', 'COLUMN';

    GO

     

     

     

     

    Wednesday, August 20, 2008 9:34 AM
    Moderator

All replies

  • Here is the whitepaper describing how to manage unstructured data, especially FileStream:
    http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-manage-unstructured.aspx

     

    Here is a blog post:
    http://blogs.msdn.com/rdoherty/archive/2007/10/12/getting-traction-with-sql-server-2008-filestream.aspx

     

    I dont think there is any document specific to upgrade. If you have any particular questions, please let me know.

    Tuesday, August 19, 2008 7:35 PM
  • OK, I ended up doing this:

     

    Code Snippet

    -- Initial schema:

    CREATE TABLE [dbo].[Pictureimage](

               [PictureId] [uniqueidentifier] NOT NULL,

               [Image] [varbinary](max) NOT NULL,

               [OriginalImage] [varbinary](max) NOT NULL,

               [Version] [timestamp] NOT NULL,

     CONSTRAINT [PK_Pictureimage] PRIMARY KEY CLUSTERED

    (

               [PictureId] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

     

    -- Attach 90 db…

     

    -- Set Comp level

    EXEC sp_dbcmptlevel Amigo, 100;

    GO

     

    -- Create FILESTREAM filegroup

    ALTER database Amigo

    ADD FILEGROUP fsfg_Amigo

    CONTAINS FILESTREAM

    GO

     

    --Add a file for storing database photos to FILEGROUP

    ALTER database Amigo

    ADD FILE

    (

        NAME= 'fs_Amigo',

        FILENAME = 'C:\fs_Amigo'

    )

    TO FILEGROUP fsfg_Amigo

    GO

     

    -- Migration to FILESTREAM

     

    ALTER TABLE dbo.PictureImage

    SET ( FILESTREAM_ON = fsfg_Amigo )

    GO

     

    ALTER TABLE dbo.PictureImage

    ALTER COLUMN PictureId ADD ROWGUIDCOL

    GO

     

    ALTER TABLE dbo.PictureImage

    ADD OriginalImageFS varbinary(MAX) FILESTREAM NULL;

    GO

     

    UPDATE dbo.PictureImage SET OriginalImageFS = [OriginalImage];

    GO

     

    ALTER TABLE dbo.PictureImage

    DROP COLUMN OriginalImage;

    GO

     

    EXEC sp_rename 'AmigoProd.dbo.PictureImage.OriginalImageFS', 'OriginalImage', 'COLUMN';

    GO

     

     

     

     

    Wednesday, August 20, 2008 9:34 AM
    Moderator
  • OK, I ended up doing this:

     

     

    Code Snippet

    - - Initial schema:

    CREATE TABLE [dbo]. [Pictureimage](

               [PictureId] [uniqueidentifier] NOT NULL,

               [Image] [varbinary]( max ) NOT NULL,

               [OriginalImage] [varbinary]( max ) NOT NULL,

               [Version] [timestamp] NOT NULL,

     CONSTRAINT [PK_Pictureimage] PRIMARY KEY CLUSTERED

    (

               [PictureId] ASC

    ) WITH ( PAD_INDEX   = OFF , STATISTICS_NORECOMPUTE   = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS   = ON , ALLOW_PAGE_LOCKS   = ON ) ON [PRIMARY]) ON [PRIMARY]

     

    -- Attach 90 db…

     

    -- Set Comp level

    EXEC sp_dbcmptlevel Amigo, 100;

    GO

     

    -- Create FILESTREAM filegroup

    ALTER database Amigo

    ADD FILEGROUP fsfg_Amigo

    CONTAINS FILESTREAM

    GO

     

    --Add a file for storing database photos to FILEGROUP

    ALTER database Amigo

    ADD FILE

    (

        NAME= 'fs_Amigo',

        FILENAME = 'C:\fs_Amigo'

    )

    TO FILEGROUP fsfg_Amigo

    GO

     

    -- Migration to FILESTREAM

     

    ALTER TABLE dbo. PictureImage

    SET ( FILESTREAM_ON = fsfg_Amigo )

    GO

     

    ALTER TABLE dbo. PictureImage

    ALTER COLUMN PictureId ADD ROWGUIDCOL

    GO

     

    ALTER TABLE dbo. PictureImage

    ADD OriginalImageFS varbinary ( MAX ) FILESTREAM NULL;

    GO

     

    UPDATE dbo. PictureImage SET OriginalImageFS = [OriginalImage];

    GO

     

    ALTER TABLE dbo. PictureImage

    DROP COLUMN OriginalImage;

    GO

     

    EXEC sp_rename 'AmigoProd.dbo.PictureImage.OriginalImageFS' , 'OriginalImage' , 'COLUMN' ;

    GO

     

     

     

     

     

     

    Hi, how is doing...

    Excuse me can you explain me what is this?

    EXEC sp_rename 'AmigoProd.dbo.PictureImage.OriginalImageFS' , 'OriginalImage' , 'COLUMN' ;

    How does this work?

    Thanks so much...

     

    Saturday, February 5, 2011 12:31 PM
  • EXEC sp_rename 'AmigoProd.dbo.PictureImage.OriginalImageFS' , 'OriginalImage' , 'COLUMN' ;

     

    This statement just renames the column and sp_rename is a built in stored procedure.

    Monday, July 4, 2011 6:12 PM
  • Hi,

    I've exactly executed  the script above on my dbo.tbFiles, after that I've noticed that the table size is duplicated. I've tryied to execute log backup followed by full backup (with INIT clause), but it doens't change anything: the dbo.tbFiles size is still duplicated!

    Why?

    Thanks.

    Regards.

    Saturday, December 30, 2017 11:14 AM
  • It is usually better to start a new thread than to piggyback on an old thread.

    For this particular case try DBCC CLEANTABLE ('yourdb', 'dbo.tbFiles'). This should reclaim the space of the dropped column.

    If this does not help, please start a new thread describing your problem from start to end.

    Saturday, December 30, 2017 1:03 PM