We help IT Professionals succeed at work.

Troubleshooting part of SQL backup script.

100 Views
Last Modified: 2018-10-04
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

Paul NeralichIT Specialist
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
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
ste5anSenior Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome, Zack..

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions