Restore database with wildcard in name

lculley

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

epetro

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.

Andrew Hodge

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?

Colin Millerchip

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.