[SQL Server][FILESTREAM] -- How to Backup and Restore a SQL Server FILESTREAM En

zyk198500

　　From: https://www.mssqltips.com/sqlservertip/1854/how-to-backup-and-restore-a-sql-server-filestream-enabled-database/
Problem
　　Most SQL Server DBAs have questions about backing up and restoring FILESTREAM enabled databases. In this tip, we will take a look at the steps Database Administrators need to follow in order to perform a backup and restore of a FILESTREAM database.  This tip includes a general explanation of the FILESTREAM technology introduced with SQL Server 2008.  This is followed by examples and scripts to setup the backup and recovery process in your environment.
Solution
　　In SQL Server 2008 one can store BLOBs (e.g. Images, Video, Word, Excel, PDF, MP3, etc files) in the NT file system rather than in a database file. This can be achieved by using the new FILESTREAM feature which was introduced in SQL Server 2008.  However, the big question in the mind of many DBA is how FILESTREAM enabled databases can be backed up and restored.   Are there any differences from a typical database?  In this tip, we will go through an example of how to backup and restore a FILESTREAM enabled database.
　　Creating a FILESTREAM Enabled Database
　　Let us start by creating a FILESTREAM enabled database namely FileStreamDB by executing the TSQL code below.
Use Master　　GO
　　IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
　　DROP DATABASE FileStreamDB
　　GO
　　USE master
　　GO
　　CREATE DATABASE [FileStreamDB] ON PRIMARY
　　( NAME = N'FileStreamDB', FILENAME = N'D:\FileStreamDB\FileStreamDB.mdf',
　　SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
　　LOG ON
　　( NAME = N'FileStreamDB_log', FILENAME = N'D:\FileStreamDB\FileStreamDB_log.ldf' ,
　　SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
　　GO
　　ALTER DATABASE [FileStreamDB]
　　ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
　　GO
　　ALTER DATABASE [FileStreamDB]
　　ADD FILE (NAME = N'FileStreamDB_FSData', FILENAME = N'D:\FileStreamDB\FileStreamData')
　　TO FILEGROUP FileStreamGroup
GO　　Creating a table with FILESTREAM columns
　　Let us now create the FileStreamDataStorage table by executing the TSQL code below.  This table will be used to store FILESTREAM data:
Use FileStreamDB　　GO
　　IF EXISTS (SELECT name FROM sys.all_objects WHERE name = N'FileStreamDataStorage')
　　DROP TABLE FileStreamDataStorage
　　GO
　　CREATE TABLE [FileStreamDataStorage]
　　(

　　[ID] [INT]>　　[FileStreamData] VARBINARY(MAX) FILESTREAM NULL,
　　[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
　　[DateTime] DATETIME DEFAULT GETDATE()
　　)
　　ON [PRIMARY]
　　FILESTREAM_ON FileStreamGroup
　　GO
　　To store a BLOB using FILESTREAM feature, you must have a column of datatype VARBINARY (MAX) along with the FILESTREAM attribute. In addition to this the table must also have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute.
　　Inserting FILESTREAM Data
　　Let us now add a row to FileStreamDataStorage table by execute the below mentioned TSQL code.
Use FileStreamDB　　GO
　　INSERT INTO [FileStreamDataStorage] (FileStreamData)
　　SELECT * FROM
　　OPENROWSET(BULK N'C:\SampleFiles\Image1.JPG' ,SINGLE_BLOB) AS Document
　　GO
　　-- Execute the below mentioned TSQL code to retrieve the data from
　　-- FileStreamDataStorage table.
　　USE FileStreamDB
　　GO
　　SELECT>　　, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
　　, FileStreamDataGUID
　　, [DateTime]
　　FROM [FileStreamDataStorage]
　　GO

　　For more information about inserting, updating or deleting FILESTREAM data, check out - Creating a SQL Server 2008 FILESTREAM Enabled Database and Using INSERT, UPDATE and DELETE statements to manage FILESTREAM Data.
　　Backup FILESTREAM Enabled Database
　　A DBA can perform a full backup of a FileStreamDB database by executing the T-SQL Code below.  In this tip, all of the backups are using the database backup compression feature which was introduced in SQL Server 2008.
/* Perform a Full Backup of FileStreamDB */　　Use master
　　GO
　　BACKUP DATABASE FileStreamDB
　　TO DISK =N'C:\DBBackup\FileStreamDB.BAK'
　　WITH COMPRESSION
　　GO
　　/* Perform a Tail Log Backup of FileStreamDB */
　　BACKUP LOG FileStreamDB
　　TO DISK =N'C:\DBBackup\FileStreamDB.TRN'
　　WITH COMPRESSION, NORECOVERY
　　GO

　　Once the database backups have successfully completed, the next step will be to go ahead and restore the FileStreamDB database.
　　Restore FILESTREAM Enabled Database
　　Let us now go ahead and restore the Full backup of the FileStreamDB database with the NORECOVERY option.
/*>Use master
　　GO
　　RESTORE FILELISTONLY
　　FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
　　GO
　　/* Restore the Full Backup with NORECOVERY option */
　　RESTORE DATABASE FileStreamDB
　　FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
　　WITH NORECOVERY, FILE =1
　　GO
　　Next, you need to restore the transaction log backup of the FileStreamDB database with the RECOVERY option to complete the restoration process.
/*Restore Tail Log Backup with RECOVERY option */　　RESTORE DATABASE FileStreamDB
　　FROM DISK = N'C:\DBBackup\FileStreamDB.TRN'
　　WITH RECOVERY, FILE =1
　　GO

　　Once the database restoration has successfully completed you can execute the T-SQL code below to check whether the earlier inserted record is still available.
USE FileStreamDB　　GO

　　SELECT>　　, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
　　, FileStreamDataGUID
　　, [DateTime]
　　FROM [FileStreamDataStorage]
　　GO

　　You will are able retrieve the same record values from FileStreamDataStorage table as the database was restored successfully.
　　Restore FILESTREAM Enabled Database Using MOVE Files Option

　　A DBA can execute the T-SQL code below to>SELECT name AS [File Name]　　, physical_name AS [Physical Name]
　　, type_desc AS [File Type]
　　, state_desc AS [Database Status]
　　FROM FileStreamDB.sys.database_files
　　GO

　　Note: Prior to executing the T-SQL code below, delete the earlier backup files and perform a new Full and Transaction Log backup of the FileStreamDB database.
　　The T-SQL code below will restore the FileStreamDB database using the MOVE option.
Use master　　GO
　　RESTORE FILELISTONLY
　　FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
　　WITH FILE =1
　　GO
　　/* Restore Full Backup with MOVE & NORECOVERY */
　　RESTORE DATABASE FileStreamDB
　　FROM DISK = N'C:\DBBackup\FileStreamDB.BAK'
　　WITH
　　MOVE 'FileStreamDB' to 'E:\FileStreamDB\FileStreamDB.mdf',
　　MOVE 'FileStreamDB_log' to 'E:\FileStreamDB\FileStreamDB_log.ldf',
　　MOVE 'FileStreamDB_FSData' to 'E:\FileStreamDB\FileStreamData',
　　NORECOVERY, FILE =1
　　GO
　　/* Restore Tail Log Backup with RECOVERY */
　　RESTORE DATABASE FileStreamDB
　　FROM DISK = N'C:\DBBackup\FileStreamDB.TRN'
　　WITH RECOVERY, FILE =1
　　GO

　　Once the FileStreamDB database is restored successfully, a DBA can execute the T-SQL code below to>SELECT name AS [File Name]　　, physical_name AS [Physical Name]
　　, type_desc AS [File Type]
　　, state_desc AS [Database Status]
　　FROM FileStreamDB.sys.database_files
　　GO

　　You could see in the above snippet that the all the database files are now available in E:\FileStreamDB location after the successful restoration.