Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 T-Sql wild card

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.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
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
Go to Top of Page

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'
Go to Top of Page

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-28 : 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/

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-28 : 13:54:10
y'all seem to think that oppie is selecting from a table...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-28 : 15:29:22
We need more info than just that. Where's the code in between the set and the restore?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-28 : 15:30:04
This might help you: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-28 : 16:10:52
EXEC('restore database CAD from disk = ' + @cadpath+ ' with replace, recovery')

Seems kinda a danger mouse to me



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-28 : 22:34:27
I don't understand what you are trying to do with the wildcard. You don't pass a wildcard for the filename for the restore command. You have to pass an EXACT path and filename there. If you need to do a search, then you need to do that via dir command in xp_cmdshell.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 10:25:44
Have a look at this

http://weblogs.sqlteam.com/brettk/archive/2005/06/28/6895.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 14:58:28
table

INSERT INTO #t(Col)
EXEC master..xp_cmdshell @cmd



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
    Next Page

- Advertisement -