| Author |
 Topic  |
|
fralo
Posting Yak Master
134 Posts |
 Posted - 02/28/2012 : 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.bak
How can I locate this file? I tried asterisk but it doesn't work.
set @cadpath = 'c:\cad_' + '*.bak'
Thanks for your help. |
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 02/28/2012 : 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
134 Posts |
Posted - 02/28/2012 : 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
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 02/28/2012 : 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
India
47069 Posts |
Posted - 02/28/2012 : 13:52:03
|
@fralo
that will match any files which has cad_ as start and of type.bak
is that exactly what you're looking at?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
fralo
Posting Yak Master
134 Posts |
Posted - 02/28/2012 : 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
India
47069 Posts |
Posted - 02/28/2012 : 15:15:25
|
thats fine. but what are you finally using this value for?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
fralo
Posting Yak Master
134 Posts |
Posted - 02/28/2012 : 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
USA
35007 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 02/28/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
fralo
Posting Yak Master
134 Posts |
Posted - 02/28/2012 : 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 |
Edited by - fralo on 02/28/2012 16:05:48 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
fralo
Posting Yak Master
134 Posts |
Posted - 02/29/2012 : 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
USA
35007 Posts |
Posted - 02/29/2012 : 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
|
|
|
fralo
Posting Yak Master
134 Posts |
Posted - 02/29/2012 : 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
USA
35007 Posts |
Posted - 02/29/2012 : 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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Subscribe to my blog |
|
|
|
Topic |
|
T-Sql wild card
