Problem with ERASEFILES Option
michaelfried
Posts: 25
I am using SQL Backup version 5.3.0.178.
I am having a problem with the ERASEFILES option of the BACKUP command. The Backups are running fine as well as the copyto portion, but the erasefiles and erasefiles_remote options are not running and no errors are reported in any of the logs.
My command is: -SQL "BACKUP LOG NewDB TO DISK = 'C:\DatabaseBackups\<DATABASE>\<DATABASE>_<TYPE>_<DATETIME yyyymmddhhnnss>_RedGate.sqb' WITH COMPRESSION = 3, ERASEFILES = 5, FILEOPTIONS = 7, INIT, KEYSIZE = 256, PASSWORD = 'XXXXXXXXXXXX', THREADCOUNT = 6, VERIFY, COPYTO = '\\networkshare\sqlbackups\<DATABASE>', ERASEFILES_REMOTE = 21 "
According to the Help Contents for ERASEFILES option:
How exactly are the ServerName values compared?
@SERVERNAME <> SERVERPROPERTY('ServerName'). From the log file, I can see the machine name (OriginalServerName) is used:
3/20/2008 12:00:08 PM: BACKUP LOG [NewDB] TO VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A01', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A02', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A03', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A04', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A05' WITH BUFFERCOUNT = 30, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (NewDB), 3/20/2008 12:00:08 PM', DESCRIPTION = N'Backup on 3/20/2008 12:00:08 PM Server: OriginalServerName Database: NewDB'
Is there a workaround for this? What have other people done when using the ERASEFILES option on a renamed SQL Server?
Mike
I am having a problem with the ERASEFILES option of the BACKUP command. The Backups are running fine as well as the copyto portion, but the erasefiles and erasefiles_remote options are not running and no errors are reported in any of the logs.
My command is: -SQL "BACKUP LOG NewDB TO DISK = 'C:\DatabaseBackups\<DATABASE>\<DATABASE>_<TYPE>_<DATETIME yyyymmddhhnnss>_RedGate.sqb' WITH COMPRESSION = 3, ERASEFILES = 5, FILEOPTIONS = 7, INIT, KEYSIZE = 256, PASSWORD = 'XXXXXXXXXXXX', THREADCOUNT = 6, VERIFY, COPYTO = '\\networkshare\sqlbackups\<DATABASE>', ERASEFILES_REMOTE = 21 "
According to the Help Contents for ERASEFILES option:
The files are deleted only if the name of the SQL Server, instance (if applicable), and database recorded in the file header match the details of the database that is being backed up
How exactly are the ServerName values compared?
@SERVERNAME <> SERVERPROPERTY('ServerName'). From the log file, I can see the machine name (OriginalServerName) is used:
3/20/2008 12:00:08 PM: BACKUP LOG [NewDB] TO VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A01', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A02', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A03', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A04', VIRTUAL_DEVICE = 'SQLBACKUP_F28866B2-1BAE-4152-8946-A47D5304496A05' WITH BUFFERCOUNT = 30, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (NewDB), 3/20/2008 12:00:08 PM', DESCRIPTION = N'Backup on 3/20/2008 12:00:08 PM Server: OriginalServerName Database: NewDB'
Is there a workaround for this? What have other people done when using the ERASEFILES option on a renamed SQL Server?
Mike
Comments
In each backup file, there is a SQL Backup header block that stores the server name. It is this value that is compared. You can see what is being stored by running this:
What is the value stored on new backup files?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
The value stored on the new backup files is the original server name -- which is what I guessed was there from looking at the RedGate SQL Backup logs.
For the current database that is being backed up, what is the method used to determine the "details of the database being backed up" with regards to the name of the SQL Server?
Mike
@SERVERNAME or SERVERPROPERTY('ServerName') values.
Or is it working? Because if it was still using the old server names, the old backup files would have been picked up for deletion.
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Should the File Deletion (or the attempt to do so) be in the Log file?
The log file displays that the process completed successfully, gives all the details about the backup, then shows the Verifying Files step, then the Copying step. Then the "SQL Backup Process Ended" message. Should there be a message about trying to Delete old files in the Backup folder, as well as trying to delete old files in the CopyTo folder?
How do I tell if it is even trying to Delete the old files?
Mike
It presently does not log anything at the start of the deletion process. You can get a log by turning on the SQL Backup Agent service's debug flag. To do that:
- stop the SQL Backup Agent service for the instance
- open the registry, look in HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services for the service's name e.g. SQLBackupAgent, or SQLBackupAgent_<instance name> for a named instance
- for the ImagePath value, add -sqbdebug to the data value e.g.
C:\Program Files\Red Gate\SQL Backup\(LOCAL)\SQBCoreService.exe -sqbdebug
- start the service
This will generate a log file named SQBCoreService_log.txt in the SQL Backup service's folder. In there, you should see entries that report how many files in each folder were picked up for deletion within the deletion process.
I will add this as a standard log entry in the next release.
Once you're done, I would suggest repeating the above process to remove the -sqbdebug flag, as that file can get large quickly if you run a lot of SQL Backup processes.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
I added the debug flag to the service and I can see the entry for "DeleteOldFiles.Collecting
Is there a way to tell why a specific file is not deleted when it meets the criteria of the ERASEFILES and FILEOPTIONS options?
I have setup the same process on a Test Server (without the SERVERNAME issues) and I am still seeing the same problems. The old files are not deleted.
Mike
- could you please run a test backup using the server name and database name tags e.g.
Then run a RESTORE SQBHEADERONLY command on that file e.g. Do the server name and database name values in the result match the names used in the backup tags (in the file nane)?
- you are using FILEOPTIONS = 7, which means that files will only be deleted if the archive bit is off. This is usually set when a tape backup software has archived the file. Is this what you wanted?
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
It turns out it was the FILEOPTIONS setting that was causing the problem. The files in question did have the Archive flag set. By setting the FILEOPTIONS = 5, then the old backup files are deleting correctly. When reading through the help file for the BACKUP command Settings, it was not clear that ERASEFILES could be used without FILEOPTIONS = 2 (or any mask value containing option 2).
Thanks for all your help.
Mike