Skip to content

create-a-filestream-enabled-database.md

Latest commit

 

History

History
47 lines (33 loc) · 3.04 KB

create-a-filestream-enabled-database.md

File metadata and controls

47 lines (33 loc) · 3.04 KB
title Create a FILESTREAM-Enabled Database
description Configure a database to support FILESTREAM by using the CONTAINS FILESTREAM clause when you create or alter the database.
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice filestream
ms.topic how-to
helpviewer_keywords
FILESTREAM [SQL Server], FILESTREAM-enabled databases

Create a FILESTREAM-Enabled Database

[!INCLUDE SQL Server] This topic shows how to create a database that supports FILESTREAM. Because FILESTREAM uses a special type of filegroup, when you create the database, you must specify the CONTAINS FILESTREAM clause for at least one filegroup.

A FILESTREAM filegroup can contain more than one file. For a code example that demonstrates how to create a FILESTREAM filegroup that contains multiple files, see CREATE DATABASE (SQL Server Transact-SQL).

To create a FILESTREAM-enabled database

  1. In [!INCLUDEssManStudioFull], click New Query to display the Query Editor.

  2. Copy the [!INCLUDEtsql] code from the following example into the Query Editor. This [!INCLUDEtsql] code creates a FILESTREAM-enabled database called Archive.

    [!NOTE]
    For this script, the directory C:\Data must exist.

  3. To build the database, click Execute.

Example

The following code example creates a database that is named Archive. The database contains three filegroups: PRIMARY, Arch1, and FileStreamGroup1. PRIMARY and Arch1 are regular filegroups that cannot contain FILESTREAM data. FileStreamGroup1 is the FILESTREAM filegroup.

:::code language="sql" source="codesnippet/tsql/create-a-filestream-enab_1.sql":::

For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist. In this example, c:\data must exist. However, the filestream1 subfolder cannot exist when you execute the CREATE DATABASE statement. For more information about the syntax, see CREATE DATABASE (SQL Server Transact-SQL).

After you run the previous example, a filestream.hdr file and an $FSLOG folder appears in the c:\Data\filestream1 folder. The filestream.hdr file is a header file for the FILESTREAM container.

Important

The filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.

For existing databases, you can use the ALTER DATABASE statement to add a FILESTREAM filegroup.

See Also

CREATE DATABASE (SQL Server Transact-SQL)
ALTER DATABASE (Transact-SQL)