Last active
December 30, 2017 11:30
-
-
Save ststeiger/d4da026991ab1a9764f2 to your computer and use it in GitHub Desktop.
CREATE or ALTER table - FileStream-Enabled
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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' | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.