full backup works, differential fails

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

full backup works, differential fails

Postby clinford » Wed Sep 17, 2008 5:22 pm

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!
clinford
 
Posts: 6
Joined: Wed Sep 17, 2008 4:24 pm

Postby Matthew Flatt » Wed Sep 17, 2008 7:33 pm

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
Test Engineer, HyperBac
Matthew Flatt
 
Posts: 253
Joined: Tue Jan 15, 2008 4:45 pm

full backup works, differential fails

Postby clinford » Wed Sep 17, 2008 8:36 pm

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!
clinford
 
Posts: 6
Joined: Wed Sep 17, 2008 4:24 pm

Postby Matthew Flatt » Thu Sep 18, 2008 10:23 am

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
Test Engineer, HyperBac
Matthew Flatt
 
Posts: 253
Joined: Tue Jan 15, 2008 4:45 pm

full backup works, differential fails

Postby clinford » Thu Sep 18, 2008 3:42 pm

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.
clinford
 
Posts: 6
Joined: Wed Sep 17, 2008 4:24 pm

Postby Matthew Flatt » Thu Sep 18, 2008 4:34 pm

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
Test Engineer, HyperBac
Matthew Flatt
 
Posts: 253
Joined: Tue Jan 15, 2008 4:45 pm

Postby clinford » Thu Sep 18, 2008 4:42 pm

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. . . :(
clinford
 
Posts: 6
Joined: Wed Sep 17, 2008 4:24 pm

Postby Matthew Flatt » Fri Sep 19, 2008 10:23 am

Hi clinford,

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

Thanks,
Matthew Flatt
Test Engineer, HyperBac
Matthew Flatt
 
Posts: 253
Joined: Tue Jan 15, 2008 4:45 pm

I had/have the same issue with the same version of Redgate

Postby mark_sterling » Wed Feb 25, 2009 3:48 pm

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.
mark_sterling
 
Posts: 1
Joined: Wed Feb 25, 2009 3:39 pm

Postby jacw2000 » Mon Feb 13, 2012 5:42 pm

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
jacw2000
 
Posts: 3
Joined: Wed Feb 01, 2012 11:28 am


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests