Avatar of Zack
Zack
Flag for Australia asked on

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)

-- SELECT THE DEFAULT BACKUP FOLDER FOR THE SQL INSTANCE or set a manual value
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.
Windows Server 2016Shell ScriptingActive DirectoryMicrosoft SQL Server

Avatar of undefined
Last Comment
Zack

8/22/2022 - Mon
David Todd

Hi,

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 https://www.experts-exchange.com/questions/29136307/Query-about-SQLCMD.html - 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.

HTH
  David
Zack

ASKER
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 Todd

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.

HTH
  David
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 David,

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

Thank you.
Pic1.PNG
Zack

ASKER
Hi David

Tried it didn't work

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

Any other suggestions?

Thank you.
ASKER CERTIFIED SOLUTION
David Todd

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 David,

Cheers, that got it thank you for your assistance.

Thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.