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

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance, and then click Properties.
  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
  6. Select the Enable FILESTREAM for Transact-SQL access check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
  9. Click Apply.

Enable filestream access and add the filestream filegroup to the database

-- 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

2014-02-20 08_01_30-DEV1SQL01.CORP.PF.COM - Remote Desktop Connection Manager v2.2

Phew. Writing that with an example took me a little longer than 10 minutes to write, but I think it was worth it.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s