Zack
asked on
Issue with XP_CMDShell Purge Old Backups script.
Hi EE,
I have the following script running in SQL server 2016:
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.
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
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.
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:
Your thoughts I would test immediately but i have lost access to the server network issue :(.
Thank you.
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'
Your thoughts I would test immediately but i have lost access to the server network issue :(.
Thank you.
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
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
ASKER
ASKER
Hi David
Tried it didn't work
SET @BackupLocation = @bakpath
SET @bakpath = @bakpath + '\Test$SQL2016APPS'
Any other suggestions?
Thank you.
Tried it didn't work
SET @BackupLocation = @bakpath
SET @bakpath = @bakpath + '\Test$SQL2016APPS'
Any other suggestions?
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI David,
Cheers, that got it thank you for your assistance.
Thank you.
Cheers, that got it thank you for your assistance.
Thank you.
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