Delete File Without xp_cmdshell

Question

Post reply

Delete File Without xp_cmdshell

Buxton69-502449

SSCrazy

Points: 2026
More actions
September 1, 2008 at 7:47 am

#192983

Does anyone know how I can delete specific files without using xp_cmdshell?
I backup a log file with a date and time stamp in it's name and then I run this as the second step of the job to remove the prevous days backups:
DECLARE @cmd varchar(255)
SET @cmd = 'del "G:\BACKUP\DBADB_BackUp_' + LEFT(REPLACE(REPLACE(CONVERT(VARCHAR,DATEADD(d,-1,GETDATE()), 13), ' ', '_'), ':', ''), 12) + '*.trn"'
EXEC master..xp_cmdshell @cmd
To run this on the servers I would have to enable xp_cmdshell in the SAC tool, I don't have a problem with this as only sysadmins can run it anyway but the people in charge of changes have different ideas and I'm not able to do that and wondered if there was any simple way of deleting files without using this option.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736602 More actions · Answer 1

you could call a VBScript/Powershell file to do this, calculate the date and delete the file. That might be easier.

Or use an SSIS package to do this. Maintenance plans have a task that will do this as well.

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 2

I've got a deep mistrust of maintenance plans, especially when it comes to deleting files.

I never thought of SSIS, thanks.

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 3

There is a sneaky way of doing this described here:

xp_cmdshell alternative

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

Richard Fryar (9/1/2008)
There is a sneaky way of doing this described here:
xp_cmdshell alternative

Heh... I love "sneaky". That's awesome! Thanks for the tip! 🙂

EDIT - BEWARE !!!! THE LINK ABOVE NO LONGER TAKES YOU TO THE RIGHT PLACE! IT NOW TAKES YOU TO A GAMING SITE !!!!

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

A CLR procedure maybe?

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

Peso (9/2/2008)
A CLR procedure maybe?

Heh... better be limited to only the directory being worked on or you might as well open up xp_CmdShell.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)