Zack
asked on
Troubleshooting part of SQL backup script.
Hi EE,
I have the following script to take backup and label them by date:
The backups work fine however, the section to remove old DB backups isn't working
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.
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
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"'
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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, 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 [..]
Then you should consider using a maintenance plan (scheduled) for this.
ASKER
Hi Raja,
Thank you for the script that worked.
Cheers.
Thank you for the script that worked.
Cheers.
Welcome, 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?
Open in new window
Or this just to see the file list of files that are getting queued for deletion:
Open in new window
- Paul N.