Skip to content

Instantly share code, notes, and snippets.

@ststeiger
Last active December 30, 2017 11:30
Show Gist options
  • Save ststeiger/d4da026991ab1a9764f2 to your computer and use it in GitHub Desktop.
Save ststeiger/d4da026991ab1a9764f2 to your computer and use it in GitHub Desktop.
CREATE or ALTER table - FileStream-Enabled
-- http://msdn.microsoft.com/en-us/library/cc645583.aspx
CREATE TABLE dbo.MyDataFile
(
-- MyUID uniqueidentifier NOT NULL
-- A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.
MyUID uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE
,MyFile VARBINARY(MAX) FILESTREAM NULL
);
-- Existing table
-- ALTER TABLE dbo.MyTable ALTER COLUMN MyUID ADD ROWGUIDCOL
-- http://www.ceservices.com/adding-filestream-existing-table-database-sql-2008-r2
-- There are three steps needed enable FILESTREAM on the database.
-- You need to turn on the FILESTREAM feature on the instance,
-- turn on the feature on the database,
-- ALTER DATABASE DBNAME ADD FILEGROUP FILESTREAMGroupName CONTAINS FILESTREAM
-- ALTER DATABASE DBNAME ADD FILE (NAME='FILESTREAMGroupName', FILENAME='Drive:\FolderName') TO FILEGROUP FILESTREAMGroupName
-- and then let the database know where to store the data on the file system.
-- e.g.
-- ALTER DATABASE COR_Basic ADD FILEGROUP COR_Basic_FileStreamGroup1 CONTAINS FILESTREAM
-- ALTER DATABASE COR_Basic ADD FILE (NAME='COR_Basic_FileStreamGroup1', FILENAME='C:\data\COR_Basic_FileStreamGroup1') TO FILEGROUP COR_Basic_FileStreamGroup1
-- a) Create a ROWGUID column
-- ALTER Table tablename ADD columname uniqueidentifier not null ROWGUIDCOL unique default newid()
-- b) Turn on FILESTREAM
-- ALTER Table tablename SET (filestream_on=FILESTREAMGroupName)
-- c) Add FILESTREAM column to the table
-- ALTER Table tablename Add columname varbinary(max) FILESTREAM null
-- d) Move data into the new column
-- UPDATE tablename SET new_columnname=old_columnname
-- e) Drop the old column
-- ALTER Table tablename DROP column old_columnname
-- f) Rename the new FILESTREAM column to the old column name
-- EXEC sp_rename 'tablename.newcolumnname', 'oldcolmnname', 'COLUMN'
@aldo-t
Copy link

aldo-t commented Dec 30, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment