How to set up FileStream on Origam Attachements?

support
1

I want to separate BLOB data to not consume MS SQL Express 10 GB data limit and to maintain the server better.

2

Use this script (update the filestream location and your database name):

USE [master]
GO
ALTER DATABASE [dbname] ADD FILEGROUP [attachments] CONTAINS FILESTREAM 
GO
ALTER DATABASE [dbname] ADD FILE (
	NAME = N'attachments', 
	FILENAME = N'c:\some_path\attachments' ) TO FILEGROUP [attachments]
GO
USE [dbname]
GO
-- add rowguidcol - it is mandatory for filestream tables
ALTER TABLE Attachment
ALTER COLUMN Id Add ROWGUIDCOL
GO
-- turn on FILESTREAM
ALTER TABLE Attachment
SET (filestream_on = attachments)
GO
-- add FILESTREAM column to the table
ALTER TABLE Attachment
ADD Data_fs varbinary(max) FILESTREAM null
GO
-- move data into the new column
UPDATE Attachment
SET Data_fs = Data
GO
-- drop old column
ALTER Table Attachment
DROP column Data
GO
-- rename the temporary column
EXEC sp_rename 'Attachment.Data_fs', 'Data', 'COLUMN'
GO
-- free up the space after the deleted column
dbcc cleantable ('dbname', 'Attachment')
1 Like
3

Hi Tomáš,

great script and help to enable filestream on the database.

But I want to ask if is there any reason why not to use Id column as ROWGUIDCOL column and use additional column instead? I would say that Id column should be sufficient for using this functionality and we wouldn’t need an additional column. But I might not see something so I rather ask :slight_smile:

Script for setting ROWGUIDCOL on Id column would look like this:

ALTER TABLE Attachment
ALTER COLUMN Id Add ROWGUIDCOL
1 Like
4

You are right, I don’t remember why this was the solution. I changed the script.

1 Like