Troubleshooting part of SQL backup script.

Zack
Zack used Ask the Experts™
on
Hi EE,

I have the following script to take backup and label them by date:

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'F:\Temp\'  
 
EXEC xp_cmdshell 'FORFILES /p @path /s /m *.BAK /d -1 /c "CMD /C del /Q /F @FILE"'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb','test')  -- exclude these databases
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName 
   WITH COPY_ONLY

   FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window


The backups work fine however, the section to remove old DB backups isn't working
EXEC xp_cmdshell 'FORFILES /p @path /s /m *.BAK /d -1 /c "CMD /C del /Q /F @FILE"'

Open in new window


I have enabled XP_Cmdshell in the server config and checked to make sure the directory is correct.

How else would I troubleshoot this issue?

Any assistance is welcome.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi Zack,

Which version of SQL Server are you running?

Is it possible to execute this command interactively on the server to see how it behaves?
FORFILES /p F:\Temp\ /s /m *.BAK /d -1 /c "CMD /C del /Q /F @FILE"

Open in new window


Or this just to see the file list of files that are getting queued for deletion:
FORFILES /p F:\Temp\ /s /m *.BAK /d -1 /c "CMD /C echo @FILE"

Open in new window



- Paul N.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Is there by any chance that you are using Share folder instead of "F:\Temp", if so, then deleting files on share path might be due to some permission issues..
And there is no need to use \ after "F:\Temp" and hence slightly modified your code, try it once and let us know the feedback..
FORFILES /p "F:\Temp" /s /m *.BAK /d -1 /c "cmd /c del @FILE"

Open in new window

ZackGeneral IT Goto Guy

Author

Commented:
Hi Paul,

I am using SQL Server 2017, I will attempt to run the ECHO command and let you know the outcome.

Hi Raja,

I will try your suggestion and see if this helps.

Thank you
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
I am using SQL Server 2017 [..]

Open in new window

Then you should consider using a maintenance plan (scheduled) for this.
ZackGeneral IT Goto Guy

Author

Commented:
Hi Raja,

Thank you for the script that worked.

Cheers.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, Zack..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial