Comments
Sort by recent activity
Given that your retention period is 30 days, I'm going to assume this is on a network share. The SQL Backup engine has to inspect each of the file on that network share to determine if it should be deleted, and would take a while if there are a lot of files on that folder. One way to reduce the amount of files is to split the backups by database name and backup type if you have not already done so. So instead of storing all backups in the same folder e.g. TO DISK = [\\share\backups\...] or COPYTO = [\\share\backups\], use TO DISK = [\\share\backups\<instance>_<database>\<type>\...] or COPYTO = [\\share\backups\<instance>_<database>\<type>\]. Another option, if space is not at a premium, would be to delete the old backups in a separate job, separate from the backup e.g. EXEC master..sqlbackup '-sql "ERASE FULL_BACKUPS FOR mydb FROM DISK = [\\share\backups\*.sqb] KEEP = 30"'See this page for the ERASE command details. / comments
Given that your retention period is 30 days, I'm going to assume this is on a network share. The SQL Backup engine has to inspect each of the file on that network share to determine if it should b...
<div>DECLARE @value nvarchar(128)
</div><div>EXEC master..sqbutility 1014, 'LOGFOLDER', @value OUTPUT
</div><div>SELECT @value</div>You can use this to retrieve most of the other settings stored in the SQL Backup global settings registry node for the connected instance: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name> Replace LOGFOLDER in the sample script with the other setting name you want to retrieve the values for. / comments
<div>DECLARE @value nvarchar(128)
</div><div>EXEC master..sqbutility 1014, 'LOGFOLDER', @value OUTPUT
</div><div>SELECT @value</div>You can use this to retrieve most of the other settings stored ...
I would suggest removing the ERASEFILES = 7, ERASEFILES_REMOTE = 8, for a couple of runs, and see if that is the cause of the delay. / comments
I would suggest removing the ERASEFILES = 7, ERASEFILES_REMOTE = 8, for a couple of runs, and see if that is the cause of the delay.
It isn't possible to encrypt a backup using other means other than supplying a textual password. One supported variation is to supply a file containing the password. Users would set the backup such that only the SQL Backup Agent service startup user has access to that file. Syntax is: EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH PASSWORD = [file:\\netshare\path\file.txt]"' SQL Backup will use the first non-empty line of the file as the password. / comments
It isn't possible to encrypt a backup using other means other than supplying a textual password. One supported variation is to supply a file containing the password. Users would set the backup su...
Try either of these: EXEC master..sqbsetlogin 0, '<user name>', '<password>' or EXEC master..sqbutility 9999, '<user name>', '<password>' Will get the documentation corrected for the sqbsetlogin command. / comments
Try either of these:EXEC master..sqbsetlogin 0, '<user name>', '<password>'or EXEC master..sqbutility 9999, '<user name>', '<password>'Will get the documentation corrected for the sqbsetlogin command.
What's the error message when you try to run a test backup like this on that node EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DSIK = [<AUTO>]"' / comments
What's the error message when you try to run a test backup like this on that nodeEXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DSIK = [<AUTO>]"'
As the SetEOF option doesn't seem to be in version 9.1, could you please download the SQL Backup Test Utility (https://download.red-gate.com/support/sql_backup/Testing_Utilities/SBaTU.zip) to run the disk writes test? Unzip the contents to a folder on the instance. Start the test application, select the 'Tools > Run extended disk writes test' item. Select the network path as the backup folder, set a file size of 512 MB, a 'medium' block size. Don't select the 'Run once only' option. Start the tests and send me the results (peter.yeoh@red-gate.com). Thanks. / comments
As the SetEOF option doesn't seem to be in version 9.1, could you please download the SQL Backup Test Utility (https://download.red-gate.com/support/sql_backup/Testing_Utilities/SBaTU.zip) to run t...
Could you please try running the backup of the smaller database to the 'slower' network path using FILEOPTIONS of 8 and 16 and see if there is any difference in the backup rate? E.g. EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH FILEOPTIONS = 8"' EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH FILEOPTIONS = 16"' If there is no improvement, there is a setting named SetEOF that changes the way files are allocated, but the setting needs to be made in the registry (also, I'm not sure if this setting exists in SQL Backup 9.1). You would create a DWORD-type setting in SQL Backup's registry node (HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>), and set its value to 1. Then try running a test backup with FILEOPTIONS values of 8 and 16 as per above. / comments
Could you please try running the backup of the smaller database to the 'slower' network path using FILEOPTIONS of 8 and 16 and see if there is any difference in the backup rate? E.g.EXEC master..s...
So the difference in backup rates isn't because one is ran as a scheduled backup, and the other as an ad-hoc backup, but because the backup files are being stored to different network paths? How long does it take to copy a 1 - 2 GB file from the SQL Server instance box to \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\ and \\cg1redgtp01\testbackup\CIS\FranStore2004\ respectively, using Windows Explorer (if you can get a constant speed)? / comments
So the difference in backup rates isn't because one is ran as a scheduled backup, and the other as an ad-hoc backup, but because the backup files are being stored to different network paths?How lon...
Thanks for posting the logs. The backup to \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\ was completed at the rate of 164.984 MB/sec, while the backup to \\cg1redgtp01\testbackup\CIS\FranStore2004\ was completed at 55.336 MB/sec. If you took a backup of a smaller database (5 GB or less), do you see the rate backup rates to the two network paths above? / comments
Thanks for posting the logs.The backup to \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\ was completed at the rate of 164.984 MB/sec, while the backup to \\cg1redgtp01\testbackup\CIS\FranStore2004\ ...