Skip to content

move-a-filestream-enabled-database.md

Latest commit

 

History

History
68 lines (53 loc) · 3.04 KB

move-a-filestream-enabled-database.md

File metadata and controls

68 lines (53 loc) · 3.04 KB
title Move a FILESTREAM-enabled database
description Discover how to move a FILESTREAM-enabled database. See which Transact-SQL scripts to use in the Query Editor of SQL Server Management Studio.
author MikeRayMSFT
ms.author mikeray
ms.date 01/31/2022
ms.service sql
ms.subservice filestream
ms.topic how-to
helpviewer_keywords
FILESTREAM [SQL Server], moving a FILESTREAM-enabled database

Move a FILESTREAM-enabled database

[!INCLUDE SQL Server] This article 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 [!INCLUDEssManStudioFull], select New Query to open the Query Editor.

  2. Copy the following [!INCLUDEtsql] script into the Query Editor, and then select 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 [!INCLUDEtsql] script into the Query Editor, and then select 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 [!INCLUDEtsql] script into the Query Editor, and then select 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

Next steps