What are the challenges you face when working across database platforms? Take the survey
Options

Problem with ERASEFILES Option

michaelfriedmichaelfried Posts: 25
edited March 26, 2008 2:05PM in SQL Backup Previous Versions
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:
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

  • Options
    peteypetey Posts: 2,358 New member
    >> How exactly are the ServerName values compared?
    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:
    EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = &#91;&lt;backup file name&gt;&#93;" '
    
    What is the value stored on new backup files?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Peter,

    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
  • Options
    peteypetey Posts: 2,358 New member
    The SQL Server server name is the computer name assigned to the server, unless it is a cluster, in which case it is the assigned SQL Server cluster server name.

    @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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Peter,

    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
  • Options
    peteypetey Posts: 2,358 New member
    The file deletion process is logged in the log file only when a file is deleted e.g. Deleting old backup file: <file name>

    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Peter,

    I added the debug flag to the service and I can see the entry for "DeleteOldFiles.Collecting :D:\DatabaseBakups\ - 0 entries" as well as one for the CopyTo folder location.

    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
  • Options
    peteypetey Posts: 2,358 New member
    Two issues I would like to clarify:

    - could you please run a test backup using the server name and database name tags e.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = &#91;c:\temp\&lt;SERVER&gt;_&lt;DATABASE&gt;_test.sqb&#93; " '
    
    Then run a RESTORE SQBHEADERONLY command on that file e.g.
    EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = &#91;&lt;file name&gt;&#93;"'
    
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Peter,

    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
Sign In or Register to comment.