Upgrade varbinary(max) to varbinary(max) FILESTREAM

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