none
Issue restoring FULL and DIFF backups for db with FILESTREAM FileTables

    Question

  • I am seeking assistance from a dba or a developer who has experience with SQL2012 FILESTEAM FileTables in a production environment. Specifically with BACKUP/RESTORE.

    SOME BACKGROUND

    In the past month I have been experimenting with FILESTREAM and FileTables in SQL 2012 on our dev server. I have encountered an issue when attempting to restore a copy of a database from a FULL and DIFF backup to the same SQL server instance when the database contains FILESTREAM FileTables.  I have Googled extensively and have not found a similar example or resolution to the problem.  Specifically, the NORECOVERY and FILESTREAM options for the RESTORE DATABASE command are incompatible.  If they are used together (example will follow), an error is raised:

    Msg 3031, Level 16, State 1, Line 2

    Option 'norecovery' conflicts with option(s) 'filestream'. Remove the conflicting option and reissue the statement.

     

    The dilemma: the NORECOVERY option is necessary when restoring a FULL backup so that the DIFF backup can be applied.  The FILESTREAM DIRECTORY_NAME option is required so that a new directory can be created in the SQL FILESTREAM share.  If a FILESTREAM DIRECTORY_NAME is not specified in the RESTORE DATABASE command, the following error is raised:

    FILESTREAM DIRECTORY_NAME 'MyTestDB' attempting to be set on database 'MyTestDBCopy' is not unique in this SQL Server instance. Provide a unique value for the database option FILESTREAM DIRECTORY_NAME to enable non-transacted access.

     

    This warning isn’t unexpected since the SQL FILESTREAM share and database directory (\\SQL1\FILESTREAM\MyTestDB) for non-transacted access already exists.

    MY QUESTION:

    I need a work-around for the incompatibility between the two required options when attempting to restore a database from a FULL and a DIFF backup when the database contains FILESTREAM FileTables.  Having to perform a FULL backup every day, rather than FULL+DIFFs, is not an acceptable work-around.

    DETAILS: EXAMPLE T-SQL

    In my example scenario, the following directories/files exist prior to the restore:

    D:\SQLBKUP\MyTestDB_Full.bak             (FULL backup of MyTestDB)

    D:\SQLBKUP\MyTestDB_Diff.bak             (DIFF backup of MyTestDB)

    D:\SQLDATA\MyTestDB.mdf                  (physical location of the database)

    D:\SQLLOGS\MyTestDB.ldf                  (physical location of the log)

    D:\SQLFILESTREAM\MyTestDB_fs             (physical location of FILESTREAM data)

    \\SQL1\FILESTREAM                        (FILESTREAM share for the SQL instance)

    \\SQL1\FILESTREAM\MyTestDB               (FILESTREAM directory for the MyTestDB database)

    \\SQL1\FILESTREAM\MyTestDB\Attachments   (directory for the “Attachments” FileTable)

     

    Following is an example script for restoring the FULL and DIFF backups.  The FULL fails because NORECOVERY cannot be used with FILESTREAM.

    -- Restore the full backup. As noted above, this FAILS.

    -- It works fine with RECOVERY, but then the DIFF cannot be applied)

    RESTORE DATABASE [MyTestDBCopy]

    FROM DISK = 'D:\SQLBKUP\MyTestDB_Full.bak'

    WITH NORECOVERY,

    MOVE 'MyTestDB_dat' TO 'D:\SQLDATA\MyTestDBCopy.mdf',

    MOVE 'MyTestDB_log' TO 'D:\SQLLOGS\MyTestDBCopy.ldf',

    MOVE 'MyTestDB_fs' TO 'D:\SQLFILESTREAM\MyTestDBCopy_fs',

    FILESTREAM (DIRECTORY_NAME = 'MyTestDBCopy');

     

    -- Restore the diff backup

    RESTORE DATABASE [MyTestDBCopy]

    FROM DISK = 'D:\SQLBKUP\MyTestDB_Diff.bak'

    WITH RECOVERY;

     

    Subsequent to restoring a copy of the database, the following directories/files exist:

     

    D:\SQLDATA\MyTestDBCopy.mdf              (data)

    D:\SQLLOGS\MyTestDBCopy.ldf              (log)

    D:\SQLFILESTREAM\MyTestDBCopy_fs         (physical location of FILESTREAM data)

    \\SQL1\SQL_FS\MyTestDBCopy               (FILESTREAM directory for the MyTestDBCopy database)

    \\SQL1\SQL_FS\MyTestDBCopy\Attachments   (directory for the “Attachments” FileTable)

    Thanks for your assistance!


    Friday, September 23, 2016 8:40 PM

Answers

All replies

  • Good day,

    GREAT QUESTION!
    New blog post in the way...

    I will post a blog in several minuts with step by step from creating new database to duplicate it in new place. In the mean time here is the short answer, which is very simple :-)

    First create restore with move the files to new location
    * without the: FILESTREAM (DIRECTORY_NAME = 'MyTestDBCopy');

    Next alter the database and change the DIRECTORY_NAME to new name

    use master
    GO
    ALTER DATABASE AriDemoFiletableDBNew  
    	SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'AriDemoFileTableDirNew' )
    GO


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Friday, September 23, 2016 11:35 PM
    Moderator
    • Marked as answer by Tom H-man Monday, September 26, 2016 11:02 PM
    Saturday, September 24, 2016 12:50 AM
    Moderator
  • Thank you for the incredibly detailed response.  Very much appreciated. I don't have time at the moment to dig into your example, but I will in the next day or two.

    I presume the example handles the issue of files added/deleted to the FileTable directory in the FILESTREAM share between the time the FULL backup was taken and the DIFF backup?

    I will get back asap when I have had a chance to look at your example.

    Thanks again.

    TRH

    Monday, September 26, 2016 5:08 PM
  • Hi,

    >> Thank you for the incredibly detailed response.  Very much appreciated. I don't have time at the moment to dig into your example, but I will in the next day or two.

    Thanks, you are most welcome :-)
    Don;t forget to close the thread by marking answer(s), and voting helpful message(s)

    >> I presume the example handles the issue of files added/deleted to the FileTable directory in the FILESTREAM share between the time the FULL backup was taken and the DIFF backup?

    Yep, I didn't demonstrate using  DIFFERENTIAL BACKUP explicitly and in the blog I am using simple FULL BACKUP, but I even add files after the full backup, and then do a Tail Log Backup (which mean I backup the new files as well).

    The issue is not related to the type of backup but to restore procedure, which should be done in 2 steps(restore + SET FILESTREAM).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, September 26, 2016 5:57 PM
    Moderator
  • Thanks again.  Once I have a chance to review your solution and ask any additional clarifying questions, I will mark the answer.  From my testing and understanding of FILESTREAM FileTables, I figured the solution was going to require a bit of messing around (multi-step process).  Restoring a FULL backup with FileTables is not a problem, its only when DIFF restores are desired that it gets complicated/messy (i.e. applying changes to the file system subsequent to the FULL backup that are captured in the DIFF)

    I'll get back to you.

    Monday, September 26, 2016 6:30 PM
  • I was able to successfully verify that it is a two step process as shown in your scripts:

    1) create the database copy by restoring the FULL/NORECOVERY and DIFF/RECOVERY.  When restoring the DIFF, the following warning is raised:

    FILESTREAM DIRECTORY_NAME 'FileStreamTest' attempting to be set on database 'FileStreamTestCopy' is not unique in this SQL Server instance.

    2) change the properties of the database copy: provide a unique name for the virtual FILESTREAM Directory Name; set non-transacted access to FULL.  The virtual FILESTREAM directory will be created for the database, exposing the files in the physical FILESTREAM directory.

    I appreciate all your help.

    Monday, September 26, 2016 11:02 PM
  • You are most welcome!

    I am glad to hear that I could help a bit :-)

     

    I'm always glad to see when people remember to close the thread, with a thanks for the help they have received and a thanks to the people that tried to help them. For great starting point, here are your first 5 points :-)
    +5


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, September 27, 2016 6:28 AM
    Moderator