SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 T-Sql wild card
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

fralo
Posting Yak Master

153 Posts

Posted - 02/28/2012 :  10:28:23  Show Profile  Reply with Quote
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
1782 Posts

Posted - 02/28/2012 :  11:02:27  Show Profile  Reply with Quote
% 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

153 Posts

Posted - 02/28/2012 :  11:08:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1782 Posts

Posted - 02/28/2012 :  13:36:56  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/28/2012 :  13:52:03  Show Profile  Reply with Quote
@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 - 02/28/2012 :  13:54:10  Show Profile  Reply with Quote
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

153 Posts

Posted - 02/28/2012 :  15:06:11  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/28/2012 :  15:15:25  Show Profile  Reply with Quote
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

153 Posts

Posted - 02/28/2012 :  15:27:35  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 02/28/2012 :  15:29:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37466 Posts

Posted - 02/28/2012 :  15:30:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

India
52325 Posts

Posted - 02/28/2012 :  15:39:35  Show Profile  Reply with Quote
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

153 Posts

Posted - 02/28/2012 :  15:59:26  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/28/2012 :  16:10:52  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 02/28/2012 :  22:34:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

153 Posts

Posted - 02/29/2012 :  09:21:48  Show Profile  Reply with Quote
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 - 02/29/2012 :  10:25:44  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 02/29/2012 :  12:27:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

153 Posts

Posted - 02/29/2012 :  14:45:31  Show Profile  Reply with Quote
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 - 02/29/2012 :  14:58:28  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 02/29/2012 :  16:31:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000