Moving FileStream data to new location

Article
06/03/2010

SQL Server 2008 introduces File Stream data type that lets you store BLOB data in an efficient manner. One of the issues with this data type is there is no easy way to do space management. What happens if you run out of space on the drive that you designated for storing the BLOBs using FileStream? There are a few different ways mentioned below to move this to a new location:

Option 1: Backup and restore file group to a new location – This option is the simplest and will back up the existing filestream file group and will restore on to the new location/drive that has more disk space.

Option 2: Detach and Attach database – This option will detach the database and then you physically move the files to the new location, then attach the database

Option 3: Create a new file group in the new location – This option lets you create a new file group in the new location and then you drop and recreate the clustered index in the new location – this is similar to option 2 except instead of taking the whole database offline, you only affect the particular table

Option 4: Partitioning – In this option, you use table partitioning to create separate file groups for the filestream data based on a partition key. As the need to move the data to a new drive arises, you can simple create a new partition in that location. This option will require you to keep both the old and new locations intact as the data is spread amongst them. In the previous 3 options, you can get rid of the old location since the entire data is moved tot the new location. I have included an example of partitioning in the attached script, this white paper also has a section in the end that talks about the same and a technique to do load balance filestream data onto multiple volumes: https://msdn.microsoft.com/en-us/library/cc949109.aspx

Here is the SQL script that has a sample of the above 4 options, you need to change the database file locations before you can run it on your SQL Server:

-- Create the FileStream database with a default primary filegroup and another one for the filestream data

Use Master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO

USE master
GO
CREATE DATABASE [FileStreamDB] ON PRIMARY
( NAME = N'FileStreamDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamDB.mdf',
SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
LOG ON
( NAME = N'FileStreamDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamDB_log.ldf' ,
SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO
ALTER DATABASE [FileStreamDB]
ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
GO
ALTER DATABASE [FileStreamDB]
ADD FILE (NAME = N'FileStreamDB_FSData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamData')
TO FILEGROUP FileStreamGroup
GO

Use FileStreamDB
Go

-- Create the table with filestream data

create table dbo.Files
(FileId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE,
[FileContents] varbinary (max) FILESTREAM DEFAULT NULL,
[FileNum] int identity (1,1) PRIMARY KEY)

-- Use T-SQL to upload a row of data to the table

declare @img as varbinary(max)
select @img = CAST(bulkcolumn AS Varbinary(max)) from openrowset (BULK 'C:\SQLTSTechnical\SQL08Demos\FileStream\tulips.jpg', single_blob) as x
Insert into dbo.Files(Fileid, FileContents) select newid(),@img

-- Make sure the row is there

select * from dbo.Files

------ Moving FileStreamData

------- Option 1: Backup filegroup and restore to new location

-- Do a full backup first

backup database FileStreamDB TO DISK = 'c:\temp\DataBack.bak'

-- You have to backup the log to do partial restore later

backup log filestreamdb to disk = 'c:\temp\DataBack.trn'

Use master
Go

-- Restore the primary filegroup first

restore database filestreamdb filegroup = 'Primary' FROM DISK = 'c:\temp\DataBack.bak'

-- Restore the filestream filegroup and move it to the new location

restore database filestreamdb filegroup = 'FileStreamGroup' FROM DISK = 'c:\temp\DataBack.bak' WITH MOVE 'FileStreamDB_FSData' TO 'd:\temp', REPLACE

-- Check to see all the file groups are online

Use FileStreamDB
Go

SELECT
file_id AS FileID,
file_guid AS FileGUID,
type_desc AS FileType,
name AS Name,
physical_name AS PhysicalName,
state_desc AS State
FROM FileStreamDB.sys.database_files
GO

-- Insert a row again

-- Make sure the row is inserted right and then check to see if this is inserted into the new location

select * from dbo.Files

------ Option 2: Detach Database and Re-attach database specifying file group in new location after
------- physically copying files to new location

--- Detach the database first

USE [master]
GO
ALTER DATABASE [FileStreamDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'FileStreamDB'
GO

-- Physically move the filestream files to the new location - in this example, moving it back to the C drive from the D drive
-- Re-attach the database specifying the new location for the file stream file group

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamDB_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamData' )
FOR ATTACH
GO

-- Option 3: Drop and recreate cluster index after adding a new file group
-- This option works only if you have a clustered index on a column other than the rowguid column

-- Add a new file group first

USE [master]
GO

ALTER DATABASE [FileStreamDB] ADD FILEGROUP [FileStreamGroup2] CONTAINS FILESTREAM

-- The below is the key step, you have to make the new filegroup the default

ALTER DATABASE [FileStreamDB] MODIFY FILEGROUP [FileStreamGroup2] DEFAULT

-- Add a file to the newly created filegroup

ALTER database [FileStreamDB]
ADD FILE
(
NAME= 'FileStreamDB_FSData2',
FILENAME = 'd:\temp'
)
TO FILEGROUP FileStreamGroup2
GO

Use FileStreamDB
Go

-- Look at sys.indexes to find the name of the clustered index to drop

select * from sys.indexes

-- Drop the index

ALTER TABLE dbo.files DROP CONSTRAINT PK__Files__0BBA21387F60ED59

-- Add the clustered index back, since filestreamgroup2 is the new default filegroup for filestream, the image data will be located here and all new images will also go here

ALTER TABLE dbo.files ADD CONSTRAINT PKCLUS PRIMARY KEY CLUSTERED (FileNum)
Go

-- Insert a new row of data, this should insert into the new location for filestream

select * from dbo.Files

-- Option 4: Partition the table and create new partitions in a different location to change the location of the file stream data

Use Master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO

-- Creating the FileStreamDB again -

-- Add a file group for filestream data

ALTER DATABASE [FileStreamDB]
ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
GO
ALTER DATABASE [FileStreamDB]
ADD FILE (NAME = N'FileStreamDB_FSData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\FileStreamData')
TO FILEGROUP FileStreamGroup
GO

-- Create a simple partition function - All values upto 100 will be in the first partition and 100 & above will be in the second

CREATE PARTITION FUNCTION Mypartfunc1 (int) AS
RANGE LEFT FOR VALUES (100)
GO

-- Add 2 new filegroups and 2 secondary files for the structured data

ALTER DATABASE [FileStreamDB] ADD FILEGROUP [Primary1]
ALTER DATABASE [FileStreamDB] ADD FILEGROUP [Primary2]

ALTER DATABASE [FileStreamDB] ADD FILE (
NAME = Primary1File,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\Primary1.ndf')
TO FILEGROUP Primary1;
GO

ALTER DATABASE [FileStreamDB] ADD FILE (
NAME = Primary2File,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.TWOK8\MSSQL\DATA\Primary2.ndf')
TO FILEGROUP Primary2;
GO

-- Create a partition scheme for the structured data

CREATE PARTITION SCHEME MyPartitionScheme1 AS
PARTITION MyPartfunc1 TO([PRIMARY1],[PRIMARY2])
GO

-- Create a partition scheme for the file stream data - note everything will go to the same filegroup for now

CREATE PARTITION SCHEME MyPartitionScheme2 AS
PARTITION MyPartfunc1 TO([FileStreamGroup],[FileStreamGroup])
GO

-- Create a table that uses the 2 partition schemes above

create table dbo.Files
([FileId] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE ON PRIMARY1,
[FileContents] varbinary (max) FILESTREAM DEFAULT NULL,
[FileNum] int identity (1,1) PRIMARY KEY)
ON MyPartitionScheme1(FileNum)
FILESTREAM_ON MyPartitionScheme2

-- select @@IDENTITY

-- Insert new data into the table

-- Make sure the data is inserted right and check the physical location of the same
select * from dbo.Files

-- Add a new filegroup and a file in a new location

ALTER DATABASE [FileStreamDB] ADD FILEGROUP [FileStreamGroup2] CONTAINS FILESTREAM

ALTER database [FileStreamDB]
ADD FILE
(
NAME= 'FileStreamDB_FSData2',
FILENAME = 'd:\temp'
)
TO FILEGROUP FileStreamGroup2
GO

-- Change the partition scheme to include the new filegroup

ALTER PARTITION SCHEME MyPartitionScheme1
NEXT USED Primary1

ALTER PARTITION SCHEME MyPartitionScheme2
NEXT USED FileStreamGroup2

-- Create a new partition for values 51 and higher

ALTER PARTITION FUNCTION MyPartFunc1()
SPLIT RANGE (50);

-- Manually reseed the identity value to 55 to test addition of new files going to the new location

DBCC CHECKIDENT(files, reseed, 55)

-- Insert new data into the table

-- Make sure the data is inserted right and make sure this is in the new location

select * from dbo.Files

Moving FileStream data to new location

Additional resources