SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing a parameter as a filename


Passing a parameter as a filename

Author
Message
arrjay
arrjay
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 600
Hi there,

I'm trying to pass a parameter as a filename in a restore database statement. In my example code I have set the parameter with the correct filename however I am unsure how to code the last statement to use my parameter as the file I want to restore.


create procedure GetbackupFilename

as

SET NOCOUNT ON

truncate TABLE DIRLIST

INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\DB_backups'

declare @Filename varchar (200)

select @filename = SUBSTRING(line,37,100) FROM dirList where line like '%.bak'

restore database chillistore2
from disk = 'C:\DB_backups'@filename

The @filename is not recognised as a txt string and therefore fails to execute and so it is this part of the query I need a hand fixing....

I appreciate any help on this. Regards, Russell.

--------------------------------------------

Laughing in the face of contention...
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147159 Visits: 19434
You have a few problems. First if there is more than one .bak file, you have no guarantee of which one your script will find.

Second, you can't include a variable with a string without any operator. What you probably want is something more like this, assuming you fix the first item.


select @filename = 'c:\DB_Backups\' + SUBSTRING(line,37,100) FROM dirList where line like '%.bak'

restore database chillistore2
from disk = @filename



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
arnipetursson
arnipetursson
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1381 Visits: 1019
Why are you not getting the file name and path from msdb?

Are you comfortable with the security issues related with using xp_cmdshell?
arnipetursson
arnipetursson
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1381 Visits: 1019
Sample code:

declare
@max_backup_set_id int,
@db_name varchar(50)

select
@db_name = '<db_name>'

select
@max_backup_set_id = max(backup_set_id)
from
msdb..backupset

select
b.physical_device_name
from
msdb..backupset a
join msdb..backupmediafamily b
on a.media_set_id = b.media_set_id
where
backup_set_id > (@max_backup_set_id - 10000) and
type = 'D' and
database_name = @db_name and
b.physical_device_name not like 'VDI%' and
( b.physical_device_name like '%Full.LSbak' OR
b.physical_device_name like '%Full%bak' ) and
backup_finish_date > dateadd(hh,-(7*24),getdate())
order by
backup_finish_date DESC
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216206 Visits: 41986
arnipetursson (12/18/2012)
Are you comfortable with the security issues related with using xp_cmdshell?


There are no security issues related with using xp_CmdShell if your system has the proper security of no one (no login or user or group) having direct access to it and have no privs higher than DBO except for DBAs. Even turning it off won't help if any login, user or group has SA privs. Even if you delete the related dll, a hacker getting in as SA can still use a trick with OPENROWSET to get to the command line. If your system isn't properly locked down, you might as well turn on xp_CmdShell because that's what a hacker is going to do for you anyway.

xp_CmdShell is not a security problem. Having bad security is a security problem. ;-)

That, notwithstanding, I do agree that, in this particular case, the filenames should come from MSDB but not for the reason most people would think. You cannot rely on the dates embedded in the filenames especially if you have Point-in-Time log backups running. Instead, you must align the LSN's with the most recent full backup in order to select the correct log files to restore.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
arrjay
arrjay
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 600
There will always only be one .bak file as a previous process overwrites the previous days .bak file.

The solution works perfectly! Thanks.

--------------------------------------------

Laughing in the face of contention...
arrjay
arrjay
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 600
Thanks for all your responses.

I will use the solution Steve posted as this works.

Steve - there will always only be one .bak file in this folder at one time but thanks for making me aware.

Thanks again all.

--------------------------------------------

Laughing in the face of contention...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search