IBM Support

Unable to use Instant Restore for MS SQL Database

Troubleshooting


Problem

MS SQL Databases VSS backups appear to work normally, but when trying to do an instant restore, FlashCopy Manager falls back to a file level restore.

Cause

Part of the files that represent the database are located on another disk.

Diagnosing The Problem

A MS SQL database is represented by one or more of the following files : data, log, none or many stream files.

An Instant Restore is possible only when ALL the files are located on disks that can be flash copied by the VSS hardware provider of the used storage back-end as documented in the manual:


in the VSS INSTANT RESTORE chapter :
http://pic.dhe.ibm.com/infocenter/tsminfo/v6r4/topic/com.ibm.itsm.db.sql.doc/c_dpfcm_res_vssinstant_sql.html
"A VSS Instant Restore is only possible when all of the data from the storage group or database that is specified for restore resides on storage subsystems that are supported by the VSS Instant Restore. If part of the data being restored, including the log files and full-text index files, resides on a local disk, a VSS Fast Restore is performed."

One possible cause of having some files located on another disk than the database or log objects, is the FILESTREAM feature:

"FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. "

See the following tech doc on this feature:


http://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx

By default this locates files on the system disk (e.g. C:).

Resolving The Problem

Check that, if this feature is used, the path is set to the same path as the database files or that the disk were these files are located is part of the same FlashCopy set.

[{"Product":{"code":"SSERFV","label":"IBM Spectrum Protect Snapshot"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"FlashCopy Manager for Microsoft SQL Server","Platform":[{"code":"PF033","label":"Windows"}],"Version":"4.1.3;4.1.4;4.1.6;8.1.0","Edition":"All Editions","Line of Business":{"code":"LOB26","label":"Storage"}}]

Document Information

Modified date:
01 February 2022

UID

swg21625899