Using new FULLIFREQUIRED option with SQL 2005

DonFergusonDonFerguson Posts: 202 Silver 5
edited July 6, 2012 10:16AM in SQL Backup Previous Versions
I realize that there are a number of places that state this option is not supported for SQL 2005, but I did find an interesting statement in the 7.1 documentation that I would like clarification on.

"Note that this option is not available if SQL Server does not return SQL error 3035 or 4214 when a full backup is required. This is the case when performing transaction log backups on most SQL Server 2000 or 2005 service packs, and when performing differential backups on most SQL Server 2005 service packs. When using this option on SQL Server 2000 or 2005, warning 483 is returned to alert you that a full backup may not have been taken."

So this seems to imply that with some service packs of 2005 this may work. Is this documentation in error? Or are the some conditions were I could use this option for SQL Server 2005? Please clarify what builds of SQL Server 2005 will allow this option.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    The FULLIFREQUIRED option works by catching SQL errors 3035 or 4214. When SQL Server reports that a differential or transaction log backup cannot be performed because no full database backup exists to be used as a base for the differential/log backup, SQL Backup then performs a full database backup, if the FULLIFREQUIRED option is used.

    Unfortunately, SQL Server 2005 isn't very consistent when it comes to raising the above errors. For e.g. on my SQL Server 2005 SP1 and SP3 instances, I can take a differential backup for a database even if I had never taken a full database backup of that database. On my colleagues' SQL 2005 SP1 and SP3 instances, the above errors are raised. We have not been able to identify any differences in the SQL 2005 instances that may be causing this discrepancy. Note that we are using SQL native backups to obtain the above results, and using SQL Backup produces the same discrepancy.

    Thus:
    Is this documentation in error?
    No, it's correct.
    Or are there some conditions where I could use this option for SQL Server 2005?
    You could try running the following test to see how your SQL Server responds:
    CREATE DATABASE sqbdifftest3035
BACKUP DATABASE sqbdifftest3035 TO DISK = 'sqbdifftest3035.bak' WITH DIFFERENTIAL
DROP DATABASE sqbdifftest3035
    If your SQL Server instance raises an error, then it should be safe to rely on the FULLIFREQUIRED option. However, I don't know if it's possible for SQL Server to suddenly not report errors when no full backup exists.
    Please clarify what builds of SQL Server 2005 will allow this option.
    As mentioned above, we have been getting different results even from the same versions of SQL Server. For the moment, we can't tell you with 100% certainty which version of SQL Server you can use the FULLIFREQURIED with confidence.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • DonFergusonDonFerguson Posts: 202 Silver 5
    Thanks for the information Petey.

    I got really excited to see this new feature added to 7.1, but was disappointed that it doesn't "officially" work on SQL Server 2005.

    I tested this on as SQL Server 2005 with SP4/CU3 installed (Build 9.0.5266) and it worked with a transaction log backup where the database had been replaced but a full hadn't been performed. That's good. 8)

    However now that this option is enabled in the backup jobs, I now get a Warning 483: message every time a log backup is performed. Not so good. :roll:

    I realize this is benign, but it would be nice not to get the warning when it works.

    Sample output:
    ERRORS AND WARNINGS


    7/5/2012 2:51:40 PM: Backing up MYDB1 (transaction log) on MYINSTANCE instance to:
    7/5/2012 2:51:40 PM: F:\MyDestinationPath\MYINSTANCE\MYDB1\LOG_MYINSTANCE_MYDB1_20120705_145140.sqb

    7/5/2012 2:51:40 PM: BACKUP LOG [MYDB1] TO VIRTUAL_DEVICE = 'SQLBACKUP_2C3E09AA-9699-48D7-85D5-C101BC0C6E45' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (MYDB1), 7/5/2012 2:51:40 PM', DESCRIPTION = N'Backup on
    7/5/2012 2:51:40 PM Server: MyServer\MYINSTANCE Database: MYDB1', FORMAT

    7/5/2012 2:51:40 PM: Backup data size : 512.000 KB
    7/5/2012 2:51:40 PM: Compressed data size: 4.000 KB
    7/5/2012 2:51:40 PM: Compression rate : 99.22%

    7/5/2012 2:51:40 PM: Processed 0 pages for database 'MYDB1', file 'MYDB1_log' on file 1.
    7/5/2012 2:51:40 PM: BACKUP LOG successfully processed 0 pages in 0.014 seconds (0.000 MB/sec).
    7/5/2012 2:51:40 PM: Warning 483: The FULLIFREQUIRED option is not available when taking transaction log backups on SQL Server 2005. SQL Backup was unable to create any full backups that may be required to restore transaction log backups.

    7/5/2012 2:51:41 PM: Backing up MYDB2 (transaction log) on MYINSTANCE instance to:
    7/5/2012 2:51:41 PM: F:\MyDestinationPath\MYINSTANCE\MYDB2\LOG_MYINSTANCE_MYDB2_20120705_145141.sqb

    7/5/2012 2:51:41 PM: BACKUP LOG [MYDB2] TO VIRTUAL_DEVICE = 'SQLBACKUP_98D3B127-42B9-4174-B166-65A8314A258E' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (MYDB2), 7/5/2012 2:51:41 PM', DESCRIPTION = N'Backup on
    7/5/2012 2:51:41 PM Server: MyServer\MYINSTANCE Database: MYDB2', FORMAT

    7/5/2012 2:53:41 PM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running, that you have the SQL Server System Administrator server role; and that no other
    processes are blocking the backup or restore process; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName]
    7/5/2012 2:53:41 PM:
    ~Process List Snipped~
    7/5/2012 2:53:41 PM:
    7/5/2012 2:53:41 PM: SQL error 3013: BACKUP LOG is terminating abnormally.
    7/5/2012 2:53:41 PM: SQL error 4214: BACKUP LOG cannot be performed because there is no current database backup.
    7/5/2012 2:53:41 PM:
    7/5/2012 2:53:42 PM: Memory profile
    7/5/2012 2:53:42 PM: Type Maximum Minimum Average Blk count Total
    7/5/2012 2:53:42 PM:
    7/5/2012 2:53:42 PM: Commit 11511267328 4096 8056374 1692 13631385600
    7/5/2012 2:53:42 PM: Reserve 64206340096 4096 228934287 281 64330534912
    7/5/2012 2:53:42 PM: Free 6803192938496 4096 28397820964 307 8718131036160
    7/5/2012 2:53:42 PM: Private 64206340096 4096 54553960 1427 77848502272
    7/5/2012 2:53:42 PM: Mapped 1585152 4096 137340 66 9064448
    7/5/2012 2:53:42 PM: Image 33374208 4096 217403 480 104353792
    7/5/2012 2:53:42 PM:
    7/5/2012 2:53:44 PM: Backing up MYDB2 (transaction log) on MYINSTANCE instance to:
    7/5/2012 2:53:44 PM: F:\MyDestinationPath\MYINSTANCE\MYDB2\LOG_MYINSTANCE_MYDB2_20120705_145344.sqb

    7/5/2012 2:53:44 PM: BACKUP LOG [MYDB2] TO VIRTUAL_DEVICE = 'SQLBACKUP_463A9C26-9A7B-4118-95F4-2EF2ABB404EF' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (MYDB2), 7/5/2012 2:51:41 PM', DESCRIPTION = N'Backup on
    7/5/2012 2:51:41 PM Server: MyServer\MYINSTANCE Database: MYDB2', FORMAT

    7/5/2012 2:53:45 PM: Backup data size : 640.000 KB
    7/5/2012 2:53:45 PM: Compressed data size: 6.000 KB
    7/5/2012 2:53:45 PM: Compression rate : 99.06%

    7/5/2012 2:53:45 PM: Processed 3 pages for database 'MYDB2', file 'MYDB2_Log' on file 1.
    7/5/2012 2:53:45 PM: BACKUP LOG successfully processed 3 pages in 0.025 seconds (0.696 MB/sec).
    7/5/2012 2:53:45 PM: Warning 474: A full database backup was performed for the database (MYDB2).

    7/5/2012 2:53:45 PM: Backing up MYDB3 (transaction log) on MYINSTANCE instance to:
    7/5/2012 2:53:45 PM: F:\MyDestinationPath\MYINSTANCE\MYDB3\LOG_MYINSTANCE_MYDB3_20120705_145345.sqb

    7/5/2012 2:53:45 PM: BACKUP LOG [MYDB3] TO VIRTUAL_DEVICE = 'SQLBACKUP_77036C52-87B6-40C5-9B4F-D6F8010FC790' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (MYDB3), 7/5/2012 2:53:45 PM', DESCRIPTION = N'Backup
    on 7/5/2012 2:53:45 PM Server: MyServer\MYINSTANCE Database: MYDB3', FORMAT

    7/5/2012 2:53:46 PM: Backup data size : 512.000 KB
    7/5/2012 2:53:46 PM: Compressed data size: 4.000 KB
    7/5/2012 2:53:46 PM: Compression rate : 99.22%

    7/5/2012 2:53:46 PM: Processed 0 pages for database 'MYDB3', file 'MYDB3_Log' on file 1.
    7/5/2012 2:53:46 PM: BACKUP LOG successfully processed 0 pages in 0.015 seconds (0.000 MB/sec).
    7/5/2012 2:53:46 PM: Warning 483: The FULLIFREQUIRED option is not available when taking transaction log backups on SQL Server 2005. SQL Backup was unable to create any full backups that may be required to restore transaction log backups.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    I realize this is benign, but it would be nice not to get the warning when it works.
    Trust me, we would like nothing more either. Unfortunately, when a trx log backup succeeds, we can't tell whether:

    a) there is a full backup taken earlier that can serve as a base for this trx log backup

    OR

    b) there is no valid full backup which can be used to restore this trx log backup but SQL Server isn't raising any errors

    So we decided that it's best to raise the warning in all cases when FULLIFREQUIRED is used on SQL Server 2005. We could store a flag to indicate when FULLIFREQUIRED actually triggered a full backup, hence indicating that SQL Server is behaving correctly, and not raise the above warning. However, we can't tell with certainty if SQL Server would still works correctly when new service packs/updates are installed.

    Basically, it's just prudent for us to raise the warning due to all the uncertainty as to how SQL Server 2005 works.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SQL_ME_RICHSQL_ME_RICH Posts: 112
    I've been following this thread with interest, and am curious to know if you are planning on doing something in SQL Backup that will address this when Microsoft stops writing any further service packs and updates for SQL Server 2005?

    Microsoft's product lifecycle typically for SQL Server has been 10 years of support (Mainstream, Extended, ect...). That puts SQL Server 2005 really close to the end of it's lifecycle, so I am just curious if at that point, could there potentially be a fix for this particular issue that Red Gate could handle on it's own?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    That would depend on how many of our users will still be running SQL Server 2005 by then.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.