en-UShe-IL
You are here:  Blog

Awared MVP 

Microsoft® Community Contributor 


Microsoft® Community Contributor


Blog Archive:

הגדל
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

הגדל
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

הגדל
* Can be used in order to search for blogs by keywords

TNWikiSummit


הגדל מה בעמוד?

ספט24

Written by: ronen ariely
24/09/2016 02:30 RssIcon

* This post base on a question asked at the MSDN forums in this link

In order to restore the database in the same instance with the original database, we need to (1) move the restored database files to new location, since two files cannot have the same path in windows operating system. (2) we must change the DIRECTORY_NAME for the Filetable data, since it is instance level element and it must be unique in this SQL Server instance.

We can configure the new path for the restored files using simple command "restore... with move <original path> to <new path>" for each file, but there is no built-in option to configure the DIRECTORY_NAME at the same time during restore. Well... In this post I will show step by step a simple procedure to restore the database as a new database on the same instance as the original database.

* I am still working on the post to add comment and explanation, but in order to post the solution fast for the sake of the forum question, I will post the procedure as simple one query file.. all you need to do is to follow the code...

USE master
GO
 
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
EXEC xp_cmdshell 'IF NOT EXIST C:\AriDemoFiletableFolder MKDIR C:\AriDemoFiletableFolder';
EXEC xp_cmdshell 'IF NOT EXIST C:\AriDemoFiletableFolder\Backup MKDIR C:\AriDemoFiletableFolder\Backup';
EXEC xp_cmdshell 'IF NOT EXIST C:\AriDemoFiletableFolder\NewDB MKDIR C:\AriDemoFiletableFolder\NewDB';
GO
 
DROP DATABASE IF EXISTS AriDemoFiletableDB
GO
CREATE DATABASE AriDemoFiletableDB WITH FILESTREAM (
    NON_TRANSACTED_ACCESS = FULL,
    DIRECTORY_NAME = N'AriDemoFileTableDir'
);
GO
 
/* Add a FileGroup that can be used for FILESTREAM */
ALTER DATABASE AriDemoFiletableDB
ADD FILEGROUP AriDemoFiletable_FG CONTAINS FILESTREAM;
GO
 
/* Add the folder that needs to be used for the FILESTREAM filegroup. */
ALTER DATABASE AriDemoFiletableDB ADD FILE(
    NAME= 'AriDemoFiletable_Name',
    FILENAME = 'C:\AriDemoFiletableFolder\AriDemoFiletable_FileName'
) TO FILEGROUP AriDemoFiletable_FG;
GO
 
 
USE AriDemoFiletableDB;
GO
 
/* Create a FileTable */
CREATE TABLE AriDemoFiletable_Tbl AS FILETABLE WITH(
    FILETABLE_DIRECTORY = 'AriDemoFiletable_Dir',
    FILETABLE_COLLATE_FILENAME = database_default
);
GO
 
SELECT * FROM AriDemoFiletable_Tbl;
GO
 
-- Go to the files virtual path
-- \\<server name or address>\<instance name>\<DIRECTORY_NAME>\<FILETABLE_DIRECTORY>
-- get server name + instance name, for the virtual path:
PRINT '\\' + @@SERVERNAME + '\AriDemoFileTableDir\AriDemoFiletable_Dir'
GO
 
SELECT * FROM AriDemoFiletable_Tbl;
GO
 
 
-------------------------
/* Perform a Full Backup of FileStreamDB */
Use master
GO
BACKUP DATABASE AriDemoFiletableDB
TO DISK =N'C:\AriDemoFiletableFolder\Backup\AriDemoFiletableDB.BAK'
WITH COMPRESSION
GO
 
-- insert new file from the IO API gui
use AriDemoFiletableDB
GO
SELECT * FROM AriDemoFiletable_Tbl;
GO
 
Use master
GO
/* Perform a Tail Log Backup of FileStreamDB */
BACKUP LOG AriDemoFiletableDB
TO DISK =N'C:\AriDemoFiletableFolder\Backup\AriDemoFiletableDB.TRN'
WITH COMPRESSION, NORECOVERY
GO
 
 
-------------------------------
 
/* Identify the list of the database & log files contained within the backup set */
Use master
GO
RESTORE FILELISTONLY
FROM DISK = N'C:\AriDemoFiletableFolder\Backup\AriDemoFiletableDB.BAK'
GO
/*
AriDemoFiletableDB      C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\AriDemoFiletableDB.mdf
AriDemoFiletableDB_log  C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\AriDemoFiletableDB_log.ldf
AriDemoFiletable_Name   C:\AriDemoFiletableFolder\AriDemoFiletable_FileName
*/
 
 
-------------------------------restore to new DB
 
/* Restore Full Backup with MOVE & NORECOVERY */
RESTORE DATABASE AriDemoFiletableDBNew
FROM DISK = N'C:\AriDemoFiletableFolder\Backup\AriDemoFiletableDB.BAK'
WITH
MOVE 'AriDemoFiletableDB'       TO 'C:\AriDemoFiletableFolder\NewDB\AriDemoFiletableDB.mdf',
MOVE 'AriDemoFiletableDB_log'   TO 'C:\AriDemoFiletableFolder\NewDB\AriDemoFiletableDB_log.ldf',
MOVE 'AriDemoFiletable_Name'    TO 'C:\AriDemoFiletableFolder\NewDB\AriDemoFiletable_FileName',
NORECOVERY, FILE =1
GO
/* Restore Tail Log Backup with RECOVERY */
RESTORE DATABASE AriDemoFiletableDBNew
FROM DISK = N'C:\AriDemoFiletableFolder\Backup\AriDemoFiletableDB.TRN'
WITH RECOVERY, FILE =1
GO
 
--RESTORE DATABASE AriDemoFiletableDB
--FROM DISK = N'C:\AriDemoFiletableFolder\Backup\AriDemoFiletableDB.TRN'
--WITH RECOVERY, FILE =1
--GO
 
 
SELECT * FROM AriDemoFiletableDBNew.dbo.AriDemoFiletable_Tbl;
GO -- we can see the same files as in old database - as expected
 
-- insert new file from the IO API gui
 
SELECT * FROM AriDemoFiletableDB.dbo.AriDemoFiletable_Tbl;
SELECT * FROM AriDemoFiletableDBNew.dbo.AriDemoFiletable_Tbl;
GO -- we don't see the new file
 
/*
The Virtual path is still connect to the old database.
if I use the GUI IO API then the file added to the old databasde and not to the new
both databases use the same virtual path as this moment, which is problem
 
now we will fix it by moving the DIRECTORY_NAME of the new database
 
*/
 
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc 
FROM sys.database_filestream_options; 
GO
/*
Database Name           non_transacted_access  non_transacted_access_desc   directory_name
AriDemoFiletableDB      2                      FULL                         AriDemoFileTableDir
AriDemoFiletableDBNew   0                      OFF                          AriDemoFileTableDir
 
The new database has the same DIRECTORY_NAME,
    but non_transacted_access_desc is OFF and non_transacted_access ius 0
 
FileTables let Windows applications obtain a Windows file handle to FILESTREAM data
 without requiring a transaction. This mean that we can drop files in the virtual folder.
 In our new database it is not disable now
 The directory_name must be unique in this SQL Server instance.
 Therefore we cannot enable it for the new database with the same name!
 
*/
ALTER DATABASE AriDemoFiletableDBNew 
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'AriDemoFileTableDir' )
GO
-- FILESTREAM DIRECTORY_NAME 'AriDemoFileTableDir' attempting to be set on database 'AriDemoFiletableDBNew'
-- is not unique in this SQL Server instance. Provide a unique value for the database option FILESTREAM DIRECTORY_NAME to enable non-transacted access.
-- ALTER DATABASE statement failed.
 
---------------------------------- change DIRECTORY_NAME for the new dtabase
 
 
use master
GO
ALTER DATABASE AriDemoFiletableDBNew 
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'AriDemoFileTableDirNew' )
GO

Tags: SQL , T-SQL , sql server , 2016 , 2012 , 2014
Categories: SQL
Location: Blogs Parent Separator Public blog