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.