Author |
Topic |
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-28 : 10:28:23
|
Hi all,I'm trying to retrieve a backup file. The name of the file, though, contains a randomly generated number, such as...cad_02943523.bakHow can I locate this file? I tried asterisk but it doesn't work.set @cadpath = 'c:\cad_' + '*.bak'Thanks for your help. |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-02-28 : 11:02:27
|
% is the SQL equivalent of *_ is the SQL equivalent of ?=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-28 : 11:08:01
|
Thanks. But it seems that no matter what I put, * or %, it is being treated as a hardcoded character and not a wildcard.The error returned is "cannot open backup devie 'c:\cad_%.bak' |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-02-28 : 13:36:56
|
You need to use the LIKE operator instead of the Equality oprator (=) (e.g., WHERE MyPath LIKE 'c:\cad_%.bak')=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-28 : 13:52:03
|
@fralothat will match any files which has cad_ as start and of type.bakis that exactly what you're looking at?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
X002548
Not Just a Number
15586 Posts |
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-28 : 15:06:11
|
Yes, I am not doing a query as in "select...where path like 'cad_%.bak'"Rather, it's a SET statement. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-28 : 15:15:25
|
thats fine. but what are you finally using this value for?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-28 : 15:27:35
|
It's used for determining the correct backup file in preparation for restoring a database within a SQL agent job that runs every day.set @cadpath = 'c:\cad_' + '*.bak'restore database CAD from disk = @cadpath with replace, recovery |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-28 : 15:39:35
|
quote: Originally posted by fralo It's used for determining the correct backup file in preparation for restoring a database within a SQL agent job that runs every day.set @cadpath = 'c:\cad_' + '*.bak'restore database CAD from disk = @cadpath with replace, recovery
are you trying to do restore from a backup set?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-28 : 15:59:26
|
Yes I am.Here's a little more code. In my original posting, I cleaned it up a little bit to make it easier to read, but here is the actual code.I'm doing this restore for every database on the server. The name of the backup file contains the databasename, the DATE, followed by random key, like:"cad_backup_20120228_2348920.bak"The code below is basically repeated for every database.declare @datestring varchar(12);declare @cadpath varchar(80);--Restore will use the backup taken in the morning set @dateString = CONVERT(varchar(8), getdate(),112); --Need to get backup with random number after DATE set @cadpath = 'g:\backups\cad_backup_' + @datestring + '*.bak' restore database CAD from disk = @cadpath with replace, recovery |
|
|
X002548
Not Just a Number
15586 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-29 : 09:21:48
|
Hi Tara,At run-time I don't know the fullname of the backup file, only a portion of it. I know that it begins with "cad_backup_" followed by the DATE. But then there is a random sequence of numbers generated which complete the name of the file. The wildcard is the only way I know how to reference the filename. |
|
|
X002548
Not Just a Number
15586 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-02-29 : 12:27:42
|
quote: Originally posted by fralo Hi Tara,At run-time I don't know the fullname of the backup file, only a portion of it. I know that it begins with "cad_backup_" followed by the DATE. But then there is a random sequence of numbers generated which complete the name of the file. The wildcard is the only way I know how to reference the filename.
You can't use a wildcard for the restore. You have to know the EXACT name of it. So you will need to search for its name via the dir command. EXEC xp_cmdshell 'dir c:\cad_*.bak'So based upon the output of that, how will YOU know which one to grab?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2012-02-29 : 14:45:31
|
Thanks Tara. Together with the DATE (i.e. "cad_backup_20120229") it will be unique.How can I store the result of the xp_cmdshell commmand into a variable to use when I run the restore statement? |
|
|
X002548
Not Just a Number
15586 Posts |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-02-29 : 16:31:23
|
quote: Originally posted by fralo Thanks Tara. Together with the DATE (i.e. "cad_backup_20120229") it will be unique.How can I store the result of the xp_cmdshell commmand into a variable to use when I run the restore statement?
Use the CONVERT command for GETDATE(), with the appropriate style and save that to a varchar variable. You may need to do some extra formatting there with REPLACE (sorry don't have BOL in front of me to check CONVERT and its styles). Then concatenate that together with your existing variable and finally pass that to the restore command. Voila.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
Next Page
|