full backup works, differential fails

clinfordclinford Posts: 8
edited February 13, 2012 11:42AM in SQL Backup Previous Versions
I am using Redgate Backup version 5.3.0.178

When I do a full backup, it completes successfully (in about 10 seconds). See the log file here:
SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [Viewpoint] TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\<AUTO>.sqb' WITH COMPRESSION = 1 "
PROCESSES COMPLETED SUCCESSFULLY

9/17/2008 8:58:21 AM: Backing up Viewpoint (full database) to:
9/17/2008 8:58:21 AM: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\FULL_(local)_Viewpoint_20080917_085821.sqb

9/17/2008 8:58:21 AM: BACKUP DATABASE [Viewpoint] TO VIRTUAL_DEVICE = 'SQLBACKUP_A403A8D7-2606-4DAD-8BC8-EE101EF47E86' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (Viewpoint), 9/17/2008 8:58:21 AM', DESCRIPTION = N'Backup on 9/17/2008 8:58:21 AM Server: IRONHIDE Database: Viewpoint'

9/17/2008 8:58:31 AM: Database size : 1.426 GB
9/17/2008 8:58:31 AM: Compressed data size: 689.879 MB
9/17/2008 8:58:31 AM: Compression rate : 52.77%

Processed 123920 pages for database 'Viewpoint', file 'VPProd' on file 1.
Processed 4 pages for database 'Viewpoint', file 'VPProd_log' on file 1.
BACKUP DATABASE successfully processed 123924 pages in 8.875 seconds (114.386 MB/sec).
9/17/2008 8:58:31 AM: SQL Backup process ended.

Here is the log for a differential backup that fails(after about 10 minutes of trying):
SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [Viewpoint] TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\<AUTO>.sqb' WITH COMPRESSION = 1, DIFFERENTIAL "
ERRORS AND WARNINGS


9/17/2008 10:07:57 AM: Backing up Viewpoint (differential database) to:
9/17/2008 10:07:57 AM: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DIFF_(local)_Viewpoint_20080917_100757.sqb

9/17/2008 10:07:58 AM: BACKUP DATABASE [Viewpoint] TO VIRTUAL_DEVICE = 'SQLBACKUP_E9D0125E-622E-46C5-8D3E-D6AF7F3B7397' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (Viewpoint), 9/17/2008 10:07:57 AM', DESCRIPTION = N'Backup on 9/17/2008 10:07:57 AM Server: IRONHIDE Database: Viewpoint', DIFFERENTIAL

9/17/2008 10:17:34 AM: 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 and that you have the SQL Server System Administrator server role; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName]
9/17/2008 10:17:34 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
9/17/2008 10:17:34 AM: SQL error 3035: Cannot perform a differential backup for database "Viewpoint", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.


I did create/increase the VDITimeout entry to 240 (4 minutes), per some of the other entries in this forum.

Any ideas/solutions for this? What do I need to do to get the differential backup to work? Why does the differential take 10+ minutes to fail, when the full backup completes successfully in 10 seconds??

Thanks!
· Share on Twitter

Comments

  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi clinford,

    SQL Server is complaining that a current full backup of your database Viewpoint doesn't exist.

    This message can often be caused if the recovery model of your database has been changed to simple and back to full again, often during re-indexing or something similar.

    If you take a full backup and immediately take a differential, is it successful?

    If it is successful you might want to check for a job that changes the recovery model and modify it to change to Bulk-Logged rather than simple.

    I hope this helps.
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • clinfordclinford Posts: 8
    Matthew,

    Thank you for the reply. However, I don't think that is the case--as it still fails if I do a full backup, immediately followed by a differential. . . Though I would be happy to do some more digging on that if you can give me some clues on where to look--I have virtually no experience with database admin :-(

    Here are the latest logs:

    First the full backup:
    SQL Backup log file 5.3.0.178

    -SQL "BACKUP DATABASE [Viewpoint] TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\<AUTO>.sqb' WITH COMPRESSION = 1 "
    PROCESSES COMPLETED SUCCESSFULLY

    9/17/2008 1:12:37 PM: Backing up Viewpoint (full database) to:
    9/17/2008 1:12:37 PM: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\FULL_(local)_Viewpoint_20080917_131237.sqb

    9/17/2008 1:12:38 PM: BACKUP DATABASE [Viewpoint] TO VIRTUAL_DEVICE = 'SQLBACKUP_F88DE86F-725E-48B4-B11F-44FD3DC2F27B' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (Viewpoint), 9/17/2008 1:12:37 PM', DESCRIPTION = N'Backup on 9/17/2008 1:12:37 PM Server: IRONHIDE Database: Viewpoint'

    9/17/2008 1:12:47 PM: Database size : 1.427 GB
    9/17/2008 1:12:47 PM: Compressed data size: 689.259 MB
    9/17/2008 1:12:47 PM: Compression rate : 52.84%

    Processed 124024 pages for database 'Viewpoint', file 'VPProd' on file 1.
    Processed 1 pages for database 'Viewpoint', file 'VPProd_log' on file 1.
    BACKUP DATABASE successfully processed 124025 pages in 8.861 seconds (114.661 MB/sec).
    9/17/2008 1:12:47 PM: SQL Backup process ended.


    Here is the failing differential log:

    SQL Backup log file 5.3.0.178

    -SQL "BACKUP DATABASE [Viewpoint] TO DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\<AUTO>.sqb' WITH COMPRESSION = 1, DIFFERENTIAL "
    ERRORS AND WARNINGS


    9/17/2008 1:13:13 PM: Backing up Viewpoint (differential database) to:
    9/17/2008 1:13:13 PM: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DIFF_(local)_Viewpoint_20080917_131313.sqb

    9/17/2008 1:13:13 PM: BACKUP DATABASE [Viewpoint] TO VIRTUAL_DEVICE = 'SQLBACKUP_2BB50CC0-BBB8-47DF-8F57-CFB5BFA5B6EB' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (Viewpoint), 9/17/2008 1:13:13 PM', DESCRIPTION = N'Backup on 9/17/2008 1:13:13 PM Server: IRONHIDE Database: Viewpoint', DIFFERENTIAL

    9/17/2008 1:22:49 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 and that you have the SQL Server System Administrator server role; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\[InstanceName]
    9/17/2008 1:22:49 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
    9/17/2008 1:22:49 PM: SQL error 3035: Cannot perform a differential backup for database "Viewpoint", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

    Any other ideas on what I can look at/for?

    Thanks!
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi clinford,

    Could you try performing a Native Full and Differential backup and let me know the results?

    Also, is it only this database which has the problem?

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • clinfordclinford Posts: 8
    Matthew,

    Thanks again for the reply. I have tried what I *think* is a Native Full/Differential backup (using Microsoft SQL Server Management Studio)--and get the same error about the database not existing.

    Any ideas on what I need to do to resolve that? (The backup location is the same for both the full and differential backups that I do. . .)

    Here is the error I get from there:
    TITLE: Microsoft SQL Server Management Studio

    Backup failed for Server 'IRONHIDE'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName ... nkId=20476
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: Cannot perform a differential backup for database "Viewpoint", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName ... nkId=20476
    BUTTONS:

    OK

    Thanks again for your help.
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi clinford,

    The only other information I can find regarding this error is the effect of snapshot backups taken using ntbackup or something similar as per this Mircosoft kb article :

    http://support.microsoft.com/kb/903643

    Does this apply here?

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • clinfordclinford Posts: 8
    Matthew,

    I don't think so. I searched through the logs, and didn't find any occurrences of the message "IO is frozen for snapshot"

    For now, I may just need to do daily full backups, rather than differential backups. . . :(
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi clinford,

    Unfortunately yes, I am out of ideas at this point!

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • mark_sterlingmark_sterling Posts: 1
    Setting the databases from the simple recovery model to the full recovery model and then back again to the simple recovery model seems to clear the issue. A new full backup must be taken at that point but differential backups will work fine from that point on.

    I'm still attempting to determine root cause and if I find what it is I'll post it but the workaround allows me to keep doing backups using differentials.
    · Share on Twitter
  • jacw2000jacw2000 Posts: 4
    I had the same problem. I retook the Full backup, and then the diff backup worked fine. Not sure why it lost track of the full backup though.
    Regards
    Jack
    · Share on Twitter
Sign In or Register to comment.