Snapshots on Databases with Filestream
Today I am going to try to write a blog in 10 minutes, based on an issue that occured yesterday.
We use SQL Server Enterprise which gives us access to taking snapshots of databases which we use during releases. Recently we added a filestream filegroup to one of our databases. We did not know this at the time, but you cannot take a snapshot of a database that has a filestream filegroup… at least that is what we thought. Turns out all you need to do is not reference the file when you are taking a snapshot. An example for you to try is below.
Create a database on an instance of developer or enterprise:
CREATE DATABASE [FileStreamExample]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'FileStreamExample1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample1.mdf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample2.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample3.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample4.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample5.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample6.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample7.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB ),
( NAME = N'FileStreamExample8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample8.ndf' , SIZE = 8096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileStreamExample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExample_log.ldf' , SIZE = 4096KB , FILEGROWTH = 10%)
GO
Enable the FileStream feature using the configuration manager. Below is verbatim TechNet article Enable and Configure FILESTREAM
-- now that you have enabled the filestream feature on the using the configuration manager, then run the sp_configure
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
-- set up the file group
ALTER DATABASE FileStreamExample
ADD FILEGROUP FSFileStreamGroup Contains FILESTREAM;
GO
Create a file in the filegroup and create a snapshot:
-- add a file to the file group, the last part of the directory must not exist
-- the rest MUST exist
-- this should be a shared drive on a failover cluster
ALTER DATABASE FileStreamExample
ADD FILE
(NAME = 'FileStream', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FSE')
TO FILEGROUP FSFileStreamGroup
GO
USE FileStreamExample
GO
CREATE DATABASE [FileStreamExampleSnapshot]
ON PRIMARY
( NAME = N'FileStreamExample1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_1.ndf'),
(NAME = N'FileStreamExample2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev.mdf' ),
(NAME = N'FileStreamExample3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_7.ndf' ) ,
(NAME = N'FileStreamExample4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_6.ndf' ),
(NAME = N'FileStreamExample5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_5.ndf' ),
(NAME = N'FileStreamExample6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_4.ndf' ),
(NAME = N'FileStreamExample7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_3.ndf' ),
(NAME = N'FileStreamExample8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FS\FileStreamExampleDev_2.ndf' )
as snapshot of FileStreamExample
Using the sys.database_files shows that the FileStream file is OFFLINE
use FileStreamExampleSnapshot
GO
select * from sys.database_files
Phew. Writing that with an example took me a little longer than 10 minutes to write, but I think it was worth it.