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
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.