Restore database with wildcard in name

SQL Virtual Restore: Rapidly mount live, fully functional databases direct from backups

Moderators: eddie davis, Colin Millerchip, fionag

Restore database with wildcard in name

Postby lculley » Thu Jun 02, 2011 3:25 pm

I am creating a dynamic script to restore a virtual database and I would like to use wildcard syntax, but I get a "cannot open backup device. Operating system error 123 ( the filename, directory name or volume lable syntax is incorrect)". Can I use wildcard values?

e.g.,
RESTORE DATABASE [test] FROM
DISK=N'C:\\testfolder\\test_20110602*.sqb'
WITH MOVE N'testdata' TO N'E:\\test_virtual1.vmdf',
MOVE N'testlog' TO N'E:\\test_log_virtual1.vldf',
NORECOVERY, STATS=1, REPACE
GO
RESTORE DATABASE [test] with RECOVERY
GO
lculley
 
Posts: 21
Joined: Thu Nov 29, 2007 5:51 pm

spell chekc

Postby epetro » Fri Jun 03, 2011 7:53 pm

If you copy/pasted your code, you misspelled REPLACE as REPACE.

I don't know if you can use wildcard, but I plan to write a similar script that will look for the last backup file and use dynamic sql. Consider finding an exact file. What would you expect if 2 files existed that matched?

Keep in mind you will use the "DISK=..." once per thread used in the backup. We have standardized (7) threads used. It is tough to track down, but is available via red-gate stored proc "Master..sqbdata".

If your going to recover right after the restore, consider 'recovery' in the first script.
epetro
 
Posts: 55
Joined: Tue May 31, 2011 7:55 pm
Location: Zotec Partners

Postby Andrew Hodge » Thu Jul 21, 2011 11:17 am

Trying to do this as well.

Looks to me like you cant put the wildcards into virtual restore like you can with native sql backup.

Can we have this as a feature request?
Andrew Hodge
 
Posts: 90
Joined: Thu Jul 19, 2007 8:34 am
Location: Kent, UK

Postby Colin Millerchip » Wed Aug 17, 2011 3:01 pm

Sorry for the loooong delay in replying, this slipped through due to holiday breaks. The short version is that SQL Virtual Restore uses native T-SQL syntax, and as such the approach proposed by epetro is the way to do this. That said, we're working on making scheduling easier to do, but I can't provide details on this yet.
Colin Millerchip
 
Posts: 66
Joined: Wed Oct 31, 2007 5:31 pm
Location: Cambridge, UK


Return to SQL Virtual Restore 2

Who is online

Users browsing this forum: No registered users and 1 guest