Issue with XP_CMDShell Purge Old Backups script.

Zack
Zack used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
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
ZackGeneral IT Goto Guy

Author

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 Administrator

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ZackGeneral IT Goto Guy

Author

Commented:
Hi David,

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

Thank you.
Pic1.PNG
ZackGeneral IT Goto Guy

Author

Commented:
Hi David

Tried it didn't work

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

Any other suggestions?

Thank you.
Senior Database Administrator
Commented:
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.

Regards
  David
ZackGeneral IT Goto Guy

Author

Commented:
HI David,

Cheers, that got it thank you for your assistance.

Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial