July 9, 2013 at 6:27 am
I have a dos commande to delete backup files older than 2 days:
PushD "\\myuncloc\BackupDumps\" &&(
forfiles -s -m *.bak -d -2 -c "cmd /c del /q @path"
) & PopD
The code in a cmd prompt workt fine. How can i make this work in management studio?
I tried the below code. I'm not getting an error, only output with value NULL. But when i check the backup location (\\myuncloc\BackupDumps\), the files are not deleted.
declare @CMDSQL as varchar(5000)
select @CMDSQL = 'PushD "\\myuncloc\BackupDumps\" &&(
forfiles -s -m *.bak -d -2 -c "cmd /c del /q @path"
) & PopD'
print @CMDSQL
EXEC master..xp_CMDShell @CMDSQL
July 9, 2013 at 6:33 am
Look at xp_delete_file or create a maintenance plan to remove the backup files which I am pretty sure uses xp_delete_file under the covers anyway.
July 9, 2013 at 6:47 am
Yes, thnx for the tip, this works.
DECLARE @DeleteDate DATETIME = DATEADD(dd,-2,GETDATE());
DECLARE @path VARCHAR(256);
SET @path = '\\myuncloc\BackupDumps\';
print @DeleteDate
EXEC master.sys.xp_delete_file 0,@path,'BAK',@DeleteDate,0;
But anyone any idea why the other code doesn't work?
July 9, 2013 at 8:21 am
Two things come to mind.
The first is that you have embedded returns in your DOS command, which means that you are saying to execute these 3 individual commands at the command prompt. Using the PRINT command, you can see that the lines are on 3 separate lines. This, however, produces a single-line DOS command.
declare @CMDSQL as varchar(5000)
select @CMDSQL = 'PushD "\\myuncloc\BackupDumps\" &&(forfiles -s -m *.bak -d -2 -c "cmd /c del /q @path") & PopD'
print @CMDSQL
Second, the @path in the command looks like a SQL variable, but it's being passed as a string literal to the DOS command. I don't think this is what you had intended, but I could be missing something.
HTH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply