We help IT Professionals succeed at work.

Issue with XP_CMDShell Purge Old Backups script.

114 Views
Last Modified: 2019-02-18
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

David ToddSenior Database Administrator
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
ZackGeneral IT Goto Guy

Author

Commented:
HI David,

Cheers, that got it thank you for your assistance.

Thank you.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.