There is not much change in restore process, the additional process is to backup and restore file stream file and data. Enable dbcc traceon(3004, 3605, -1) to write backup and restore background process in error log. RESTORE DATABASE [SQL2016DB] FROM DISK=’H:\DatabaseBackup_FileTable\SQL2016DB_FULL.BAK’ WITH REPLACE,RECOVERY,STATS=5 Backup Background Process 2015-10-21 00:33:42.940 spid54 Backup(SQL2016DB): BACKUP DATABASE started 2015-10-21 00:33:42.940 spid54 […]

Catalog Views select db_name(database_id) DatabaseName,non_transacted_access,non_transacted_access_desc,directory_name from sys.database_filestream_options select object_name(object_id),object_name(parent_object_id) from sys.filetable_system_defined_objects select * from sys.filetables select * from sys.tables where is_filetable=1 [Function] FileTableRootPath : Returns the root-level UNC path for a specific FileTable or for the current database. USE <Database Name> GO select FileTableRootPath(null,null) [Function] GetFileNamespacePath : Returns the UNC path for a file or […]

Enabled filestream from the SQL server Services and restart the SQL Server services for the changes to take effect. — Enable Filestream, by default its set to 0 (disabled) EXEC sp_configure filestream_access_level, 2 RECONFIGURE After configuration change take effect the “FILESTREAM Access Level” will change to “Full access enabled” Add FileStream FileGroup and file to […]

The FileTable feature builds on top of SQL Server FILESTREAM technology.Enabling the Prerequisites for FileTable To enable the prerequisites for creating and using FileTables, enable the following items. Enable FILESTREAM at the Instance Level Powershell CLS $instance=”MSSQLSERVER” $wmi = Get-WmiObject -Namespace “root\Microsoft\SqlServer\ComputerManagement11” -Class FileStreamSettings | where {$_.InstanceName -eq $instance} $wmi.EnableFilestream(3, $instance) Restart SQL Server Services […]

After extensive testing and troubleshooting we identified two bugs in SQL Server that can hit you if you run FileTables in conjunction with AlwaysOn Availability Groups. One concerns the way Checkpoints are done in FileStream, which could lead to very long failover times, the other one concerns a handle issue that could lead to the […]