Link to home
Avatar of Zack
ZackFlag for Australia

asked on

Troubleshooting part of SQL backup script.

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.
Avatar of Paul Neralich
Paul Neralich
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Zack

ASKER

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
I am using SQL Server 2017 [..]

Open in new window

Then you should consider using a maintenance plan (scheduled) for this.
Avatar of Zack

ASKER

Hi Raja,

Thank you for the script that worked.

Cheers.
Welcome, Zack..