Using 'master..sqlbackup' to do virtual restore

[sraitken]

I have SQL Backup with Virtual Restore licensed. I would like to use the SQL Backup extended stored proc to perform a virtual restore but the script shown below always does a full restore.
Is this possible and if so, can you point-out why the script is bad? It is my impression that .vmdf and .vldf extensions signal a virtual restore.

DECLARE @ec int
DECLARE @sec int

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MyDB_Restored] FROM DISK = ''D:\SQLBackup\MyDB\*.sqb'' SOURCE = ''MyDB''
LATEST_ALL WITH RECOVERY, DISCONNECT_EXISTING, MOVE ''MyDB_Data'' TO ''F:\SQLDataVirtual\MyDB.vmdf'',
MOVE ''MyDB_Log'' TO ''F:\SQLDataVirtual\MyDB_Log.vldf'', REPLACE, CHECKDB = ''NO_INFOMSGS, ALL_ERRORMSGS'', DROPDB"', @ec OUT, @sec OUT

[Manfred.Castro]

There should be no need to call the SQL Backup extended stored procedure because the Hyperbac service should be able to handle .sqb extension of SQL Backup.

The only difference would be that multi-threaded SQL Backup backups are handled by repeating the DISK parameter in the RESTORE command, once for each thread used during the backup process.

Here is an example for a .sqb file with a thread count of 3

RESTORE DATABASE [WidgetProduction_Virtual] FROM
DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FULL_SQL2008R2_WidgetProduction_20120430_133003.sqb',
DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FULL_SQL2008R2_WidgetProduction_20120430_133003.sqb',
DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Backup\FULL_SQL2008R2_WidgetProduction_20120430_133003.sqb'
WITH MOVE N'WidgetProduction' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\WidgetProduction_WidgetProduction_Virtual.vmdf',
MOVE N'WidgetProduction_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Data\WidgetProduction_log_WidgetProduction_Virtual.vldf',
NORECOVERY, STATS=1
GO
RESTORE DATABASE [WidgetProduction_Virtual] WITH RECOVERY
GO
_________________
Manfred Castro
Product Support
Red Gate Software

[sraitken]

Thanks for the response.

I reason I opted for the extended sp is because it accepts "...\*.sqb" and then figures out which files (full or full+diff) and the number of threads used, which is not a simple thing!

So are you saying that there is no way to do a virtual restore via the extended sp?

[RajK]

Thanks for the forum post.

The virtual restore extensions can only be used with a native TSQL Restore syntax. You are currently using the SQL Backup restore syntax. That's why the script is not work for SVR. The extensions are being ignored by hyperbac when you try any other syntax. Within current syntax SQL Backup is just doing a normal restore.

[sraitken]

I am looking to automate doing virtual restores on a number of databases after each nightly backup. The extended sp does this nicely, but it doesnt take advantage of the virtual restore feature -- too bad.

Using native sql then for each database that I want to check, I need something like this:

-- Script generated by Red Gate SQL Virtual Restore v2.4.0.164
-- Multi-threaded SQL Backup backups are handled by repeating the DISK parameter in the RESTORE command, once for each thread used during the backup process.
RESTORE DATABASE [MyDB_Virtual] FROM
DISK=N'D:\SQLBackup\MyDB\MyDB_FULL_20130205201700.sqb',
DISK=N'D:\SQLBackup\MyDB\MyDB_FULL_20130205201700.sqb',
DISK=N'D:\SQLBackup\MyDB\MyDB_FULL_20130205201700.sqb'
WITH MOVE N'MyDB_Data' TO N'F:\SQLBackup\MyDB\MyDB_MyDB_Virtual.vmdf',
MOVE N'MyDB_Log' TO N'F:\SQLBackup\MyDB\MyDB_MyDB_Virtual.vldf',
NORECOVERY, STATS=1
GO
RESTORE DATABASE [MyDB_Virtual] FROM
DISK=N'D:\SQLBackup\MyDB\MyDB_DIFF_20130207220005.sqb',
DISK=N'D:\SQLBackup\MyDB\MyDB_DIFF_20130207220005.sqb',
DISK=N'D:\SQLBackup\MyDB\MyDB_DIFF_20130207220005.sqb'
WITH NORECOVERY, STATS=1
GO
RESTORE DATABASE [MyDB_Virtual] WITH RECOVERY, RESTRICTED_USER
GO
DBCC CHECKDB ([MyDB_Virtual])
GO
DROP DATABASE [MyDB_Virtual]
GO

Obviously, this script changes each night in terms of file names and, if for some reason, the number of threads used changes.

So, is there something available that will generate this sort of sql given a database name and the folder that holds the full+diff backups?

Thanks.

[sraitken]

Does this question have to now be raised in a different forum?

[RajK]

Thanks for your forum post. This does not need to be raised in a new post.

The TSQL script that you have generated using the wizard is the correct one and should work fine for that specific backup. To dynamically generate the TSQL depending on the backup will require using variables to substitute the relevant values. Unfortunately we haven't written any TSQL that can do this at the moment.

Please note this will need to be customised in your environment and depending on the number of threads, database structure the 'move syntax' will change as well.

Thanks for your patience and feedback in this matter.