Move a FILESTREAM-Enabled Database

This topic shows how to move a FILESTREAM-enabled database.

Note

The examples in this topic require the Archive database that is created in Create a FILESTREAM-Enabled Database.

To move a FILESTREAM-enabled database

1. In SQL Server Management Studio, click New Query to open the Query Editor.

2. Copy the following Transact-SQL script into the Query Editor, and then click Execute. This script displays the location of the physical database files that the FILESTREAM database uses.

   USE Archive
   GO
   SELECT type_desc, name, physical_name from sys.database_files
   

3. Copy the following Transact-SQL script into the Query Editor, and then click Execute. This code takes the Archive database offline.

   USE master
   EXEC sp_detach_db Archive
   GO
   

4. Create the folder C:\moved_location, and then move the files and folders that are listed in step 2 into it.

5. Copy the following Transact-SQL script into the Query Editor, and then click Execute. This script sets the Archive database online.

   CREATE DATABASE Archive ON
   PRIMARY ( NAME = Arch1,
       FILENAME = 'c:\moved_location\archdat1.mdf'),
   FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
       FILENAME = 'c:\moved_location\filestream1')
   LOG ON  ( NAME = Archlog1,
       FILENAME = 'c:\moved_location\archlog1.ldf')
   FOR ATTACH
   GO
   

See Also

Reference

sp_detach_db (Transact-SQL)