Red Gate forums :: View topic - Using 'master..sqlbackup' to do virtual restore
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Using 'master..sqlbackup' to do virtual restore

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
sraitken



Joined: 07 Sep 2012
Posts: 5

PostPosted: Wed Feb 06, 2013 4:20 pm    Post subject: Using 'master..sqlbackup' to do virtual restore Reply with quote

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
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Thu Feb 07, 2013 7:50 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
sraitken



Joined: 07 Sep 2012
Posts: 5

PostPosted: Thu Feb 07, 2013 8:03 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
RajK



Joined: 02 Feb 2012
Posts: 58

PostPosted: Fri Feb 08, 2013 2:53 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
sraitken



Joined: 07 Sep 2012
Posts: 5

PostPosted: Fri Feb 08, 2013 1:41 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
sraitken



Joined: 07 Sep 2012
Posts: 5

PostPosted: Wed Feb 13, 2013 12:42 am    Post subject: Reply with quote

Does this question have to now be raised in a different forum?
Back to top
View user's profile Send private message
RajK



Joined: 02 Feb 2012
Posts: 58

PostPosted: Thu Feb 14, 2013 6:32 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group