Avatar of Zack
Zack
Flag 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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
Paul Neralich

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
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
ste5an

I am using SQL Server 2017 [..]

Open in new window

Then you should consider using a maintenance plan (scheduled) for this.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Zack

ASKER
Hi Raja,

Thank you for the script that worked.

Cheers.
Raja Jegan R

Welcome, Zack..