Proving the Restore, Part 12: The Files That Take No Extension
The restore script generator from part 11 worked on most databases and failed on one. The full backup restored fine everywhere else; on this database the restore refused to start.
The database had two FILESTREAM filegroups. The generated MOVE clauses had given them .mdf extensions, along with everything else that wasn’t a log file.
Not every database file is a file
RESTORE FILELISTONLY returns a Type column of char(1), and two of its four values describe things that aren’t files at all.[1]
| Value | Meaning | On disk |
|---|---|---|
D |
SQL Server data file | a file, conventionally .mdf or .ndf |
L |
SQL Server log file | a file, conventionally .ldf |
F |
Full Text Catalog | a directory |
S |
FileStream, FileTable, or In-Memory OLTP container | a directory |
FILESTREAM stores its data as files in the filesystem, managed by SQL Server, inside a container directory.[2] The PhysicalName for a FILESTREAM filegroup is that directory, and the same applies to a full-text catalog under F.
MOVE maps a logical name to a physical location, and for D and L that location is a filename. For S and F it’s a directory path, so appending .mdf produces a path SQL Server can’t use as a container.
What it looks like when it goes wrong
The restore fails at the point it tries to create the container. Messages in this area name the operating system error rather than the concept, so the text points at the path rather than at the extension:
|
1 2 3 |
Msg 3634, Level 16, State 1, Line 1 The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLData\Sales_test_FileStreamData.mdf'. |
RestoreContainer::ValidateTargetForCreation is the useful part. The word “container” says the failure is about a FILESTREAM or full-text location rather than about an ordinary data file.
The specific operating system error varies with what’s already at the path and what permissions apply, so treat the message above as representative rather than as the exact text you’ll see. The pattern to recognise is a restore failing on a container path that has a file extension on the end.
Generating the clause correctly
The fix is to branch on Type and emit no extension for S and F:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT [move_clause] = N' , MOVE N''' + [f].[LogicalName] + N''' TO N''' + CASE [f].[Type] WHEN N'L' THEN @log_path ELSE @data_path END + @restore_as + N'_' + [f].[LogicalName] + CASE [f].[Type] WHEN N'D' THEN N'.mdf' WHEN N'L' THEN N'.ldf' ELSE N'' /* F and S are directories */ END + N'''' , [f].[Type] , [f].[LogicalName] , [f].[PhysicalName] FROM #filelist_results AS [f] ORDER BY [f].[FileID]; |
Writing the CASE with an explicit WHEN for D and L and an ELSE that adds nothing is safer than testing for S and F by name. If a future version introduces another container type, the ELSE treats it as a directory, which is the more likely correct answer and the less damaging guess.
Naming the container directory after the restore target matters here as much as for files. Two databases sharing a FILESTREAM container directory is not a situation to create by accident.
The other constraints on containers
Three further requirements apply to S and F that don’t apply to ordinary files, and they’re worth knowing before a test restore rather than during one.
The container can’t go on a UNC path. FILESTREAM integrates the engine with an NTFS or ReFS file system, and only NTFS or ReFS authentication works with FILESTREAM containers.[2] A restore that targets a container on a network share fails, with a message about the path not being usable for FILESTREAM files followed by the Msg 3013 that names the restore.
This one bites automated test restores in particular. A generator that defaults the data path to wherever the backups live works for every ordinary database and fails for the FILESTREAM ones, because the backup share is a UNC path. Passing an explicit local path for the restore target is the fix, and it’s worth failing fast with a clear message when the file list contains S rows and the target path begins with two backslashes.
The target directory must not already exist. SQL Server creates the container as part of the restore and won’t restore into an existing directory. A repeated test restore therefore needs the previous container removed, and a script that drops the test database doesn’t necessarily remove it. Leftover container directories are a common reason a second test restore fails after the first succeeded.
FILESTREAM has to be enabled on the instance. Restoring a database containing FILESTREAM data onto an instance where FILESTREAM is disabled won’t work. That’s a two-part setting: enabled at the Windows service level through SQL Server Configuration Manager, and set at the instance level through the filestream access level configuration option.[3]
The second is worth checking before a restore that’s supposed to be routine:
|
1 2 3 4 5 6 7 |
SELECT [name] , [value_in_use] FROM [sys].[configurations] WHERE [name] = N'filestream access level'; |
0 means disabled. Changing it needs sp_configure and RECONFIGURE, and enabling the service-level setting is a separate step outside T-SQL.
Detecting the case in advance
A validation routine can report on this before the restore rather than after:
|
1 2 3 4 5 6 |
SELECT [containers] = SUM(CASE WHEN [f].[Type] IN (N'S', N'F') THEN 1 ELSE 0 END) , [data_files] = SUM(CASE WHEN [f].[Type] = N'D' THEN 1 ELSE 0 END) , [log_files] = SUM(CASE WHEN [f].[Type] = N'L' THEN 1 ELSE 0 END) FROM #filelist_results AS [f]; |
A non-zero container count is a signal to check the instance configuration and the target directories before generating anything. It’s also a useful thing to print in the script header, since it tells whoever runs it that this restore has prerequisites the others didn’t.
Next
Two posts remain. Part 13 reconciles the two vocabularies this series has been switching between, since msdb and the backup headers describe the same backups with different column names and different type codes. Part 14 covers getting a long generated script out of a procedure, which PRINT will not do without truncating it.
Have you been caught by a leftover FILESTREAM container blocking a repeat test restore? Bluesky or LinkedIn.
References
- RESTORE FILELISTONLY (Transact-SQL) – Microsoft Learn. Documents
Typeas char(1) with D for data file, L for log file, F for Full Text Catalog, and S for FileStream, FileTable, or In-Memory OLTP container. ↩ - FILESTREAM (SQL Server) – Microsoft Learn. States that FILESTREAM integrates the Database Engine with an NTFS or ReFS file system by storing varbinary(max) data as files, and that only NTFS or ReFS authentication works with FILESTREAM containers. ↩
- filestream access level – Microsoft Learn. The instance-level configuration option, separate from the Windows service-level setting configured through SQL Server Configuration Manager. ↩