Using 'master..sqlbackup' to do virtual restore

Comments

• Manfred.Castro Posts: 209

  February 7, 2013 1:50PM

  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

  0 · Share on Twitter
• sraitken Posts: 7 Bronze 1

  February 7, 2013 2:03PM

  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?

  0 · Share on Twitter
• RajK Posts: 59

  February 7, 2013 8:53PM

  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.

  0 · Share on Twitter
• sraitken Posts: 7 Bronze 1

  February 8, 2013 7:41AM

  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.

  0 · Share on Twitter
• sraitken Posts: 7 Bronze 1

  February 12, 2013 6:42PM

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

  0 · Share on Twitter
• RajK Posts: 59

  February 14, 2013 12:32AM

  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.

  0 · Share on Twitter