Issue with XP_CMDShell Purge Old Backups script.

Hi EE,

I have the following script running in SQL server 2016:

-- Beware of server level collation and the way date formats are treated.
-- If date format needs to be forced use SET DATEFORMAT DMY

DECLARE @mydate nvarchar(50)
DECLARE @DeleteDateTime datetime
DECLARE @bakpath nvarchar(255)
DECLARE @BackupLocation nvarchar(255)
DECLARE @cmd nvarchar(4000)

EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory',@bakpath output
-- SET @bakpath = '<DRIVE>:\<PATH>'

-- Edit the number to subtract the number of hours required for 
-- the retention period i.e. -24 to delete files > 1 day
-- SET @DeleteDateTime = DateAdd(hh, -24, GetDate())

SET @DeleteDateTime = DateAdd(hh, -24, GetDate())
SET @mydate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

-- Consider retention period if there is a need to split into separate jobs
-- copy and remove the type of backup files to suit target environment.

-- xp_delete_file will remove from 1 level of subdirectory only

-- Full backups
SET @BackupLocation = @bakpath
--SET @bakpath = @bakpath + '\FULL'-- customise path if required
set @cmd = 'EXECUTE master.dbo.xp_delete_file 0,N''' + @BackupLocation +''' ,N''bak'',N''' + @mydate + ''',1'
exec sp_executesql @cmd

Open in new window

The script isn't working, I have checked the registry the backup location is set correctly how would I go about debugging this, I suspect it's a path issue?

Additionally, I enabled the xp_cmdshell advanced config option on the SQL Server and that the SQL agent account has complete permissions to the backup directory folder so permissions should be ok.

Here is the SQLCMD used command to backup; sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'B:\MSSQL13.SQL2016\MSSQL\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 48, @Compress = 'Y', @ChangeBackupType = 'Y', @CheckSum = 'Y'" -b

For some reason, the command doesn't insert the .bak files into the root directory but creates separate folders for each of the databases.

Any assistance would be appreciated.

Thank you.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:

Two things: I suggest that you set the age to delete to -25 hours instead of -24 in case there are some differences in operation of the job and you have a lot of databases. It will delete the backups that are two days old just as well as the -24/ If you really want to delete the previous run's backups (ie yesterdays) I'd set the datetime to -23 hours for the same reason.

You've asked about this at - see my comments there.

BTW it does appear that you are backing up to the same drive as your database data and transaction log files. Ideally, it should be a separate drive. Doing that might give you a bit more of what you're looking for. The depth of the backup directory tree allows for multiple instances on of SQL on the one server, backing up to the same location - which in turn can be taken off to tape or storage.

ZackGeneral IT Goto GuyAuthor Commented:
Hi David,

No, the backups aren't going the same directory as the transaction logs thankfully.

I have three days of backups now so I don't think it's a matter of setting the date.

Your comments about the directory tree has led me to think I should change the backup location:

SET @BackupLocation = @bakpath
SET @bakpath = @bakpath + '\Test$SQL2016APPS'

Open in new window

Your thoughts I would test immediately but i have lost access to the server network issue :(.

Thank you.
David ToddSenior Database AdministratorCommented:
Hi Zack,

To be as widely useful as possible, Ola has assumed vanilla setup and settings. He uses the undocumented proc xp_delete_file that the maintenance plans themselves use. It has a major flaw in that it can fail silently. It is more prone to do this when disk usage is quite high (and disk performance is quite low.)

In setting your backup path above, is the final path 'right' as in trailing backslash, not double backslashes - all that kind of thing.

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

ZackGeneral IT Goto GuyAuthor Commented:
Hi David,

Yes, please refer to the screenshot I took before I lost access:

Thank you.
ZackGeneral IT Goto GuyAuthor Commented:
Hi David

Tried it didn't work

SET @BackupLocation = @bakpath
SET @bakpath = @bakpath + '\Test$SQL2016APPS'

Any other suggestions?

Thank you.
David ToddSenior Database AdministratorCommented:
Hi Zack

In that screenshot, are the backups in those folders, or infurther folders - full or log etc. If so you're going to need to iterate through that tree with the database names.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZackGeneral IT Goto GuyAuthor Commented:
HI David,

Cheers, that got it thank you for your assistance.

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2016

From novice to tech pro — start learning today.