PushD commands from MS

• SQLServerForum.nl

  SSChasing Mays

  Points: 660

  More actions

  July 9, 2013 at 6:27 am

  #280113

  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

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  July 9, 2013 at 6:33 am

  #1630766

  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.

• SQLServerForum.nl

  SSChasing Mays

  Points: 660

  More actions

  July 9, 2013 at 6:47 am

  #1630771

  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?

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  July 9, 2013 at 8:21 am

  #1630821

  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

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions