HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Restore database with a FileTable

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
databasewithrestorefiletable

Problem

We have a SQL Server 2014 (build 12.0.24457.0) database with two tables in, both are FileStream tables, the biggest has 2979466 rows, SUM(cached_file_size)= 40564905472.

We have tried to restore this database on to our test server, but it takes quite a long time. Our DBA gave up after 14 days.

Has anyone else tried to restore a database with FileStream tables, with more that a few files in?

I'm running this script to monitor the progress:

BEGIN TRY
    DROP TABLE #h 
END TRY
BEGIN CATCH
END CATCH

DECLARE @r INT=1, @percent_complete NUMERIC(9,2), @Extimated_Completion_Datetime DATETIME2(0)
CREATE TABLE #h ( start_time DATETIME2(0), percent_complete NUMERIC(9,2), Estimated_completion_time NUMERIC(9,2), HoursSinceStart NUMERIC(9,2), Extimated_Completion_Datetime DATETIME2(0), session_id INT, created_date datetime2(0))
DECLARE @d VARCHAR(19) , @e VARCHAR(max)

WHILE @r > 0 BEGIN 
    INSERT INTO #h
    SELECT start_time, percent_complete, CAST(ROUND(estimated_completion_time/3600000.0,1) AS DECIMAL(9,1)) AS Estimated_completion_time
    , CAST(ROUND(total_elapsed_time/3600000.0,1) AS DECIMAL(9,1)) AS HoursSinceStart
    , DATEADD(HOUR, CAST(ROUND(estimated_completion_time/3600000.0,1) AS DECIMAL(9,1)), GETDATE()) AS Extimated_Completion_Datetime
    , session_id
    , GETDATE()
    FROM
    sys.dm_exec_requests AS r
    WHERE
    r.session_id <> @@SPID
    AND r.session_id > 50
    AND command LIKE 'restore database'
    SELECT @r = @@ROWCOUNT

    select top 1 @percent_complete=percent_complete, @Extimated_Completion_Datetime=Extimated_Completion_Datetime from  #h ORDER BY created_date DESC
    SET @d = CONVERT(VARCHAR(19), @Extimated_Completion_Datetime, 121) 
    SET @e = CONVERT(VARCHAR(19), GETDATE(), 121) + ' we are ' + LTRIM(@percent_complete) + '% complete. We estimate to finish at: ' + @d
    RAISERROR('At %s ', 10, 1, @e) WITH NOWAIT

    --WAITFOR DELAY '00:00:10'
    WAITFOR DELAY '00:01:00'
END


So far I've this outcome:

At 2016-06-08 10:35:

Solution

EDIT:

I've finally managed to get the database restored.

It took only 124 minutes, when we changed some settings on the server.

I found the settings here: https://support.microsoft.com/da-dk/kb/2160002

and here https://technet.microsoft.com/en-us/library/cc778996.aspx

In case the links stops working:
in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
NtfsDisableLastAccessUpdate and NtfsDisable8dot3NameCreation should be set to 1

Context

StackExchange Database Administrators Q#140703, answer score: 2

Revisions (0)

No revisions yet.