Avatar of McCoyIT
McCoyIT
 asked on

Can't restore differential backups in SQL Sever 2012

I have a SQL 2012 server with a maintenance plan that takes full backups daily, differential backups every 3 hours and transaction log backups every hour.

Up until last month, everything was working fine, I could restore any full and differential or transaction log backup to the test server with no issues.

Came back from vacation, and for some reason I can't restore a full + differential backup anymore. Restoring the full backup works fine, or full backup + transaction log back ups works. But if I go to restore, select device and choose both the full and differential backups, normally it would show the Full and Differential backups as two line items, but instead it only shows the Differential, and I can't restore it.

Any ideas on what could cause this?
Microsoft SQL Server

Avatar of undefined
Last Comment
McCoyIT

8/22/2022 - Mon
Vitor Montalvão

I'm not sure if I understood your issue. Can you add a screenshot of it?
Anyway, I don't think your backup plan is a good one. Why do you need to take a DIFF BAK every 3h?
If you're running a daily FULL BAK I even think that a single DIFF or no DIFF is even needed.

For a good backup strategy you need to know how much data can you loose. With your current strategy you can lost 1h of data. Is data ok for your client?
McCoyIT

ASKER
I'm not entirely sure, the plan was set up by somebody else. I'm guessing the differential backups were in case of a problem with the transaction log backups, or is that flawed logic?
Also differential backups are retained longer than log backups.

I've attached a screenshot of what I should see in the restore screen, and what I currently see.

In both cases I have selected the full backup and a differential backup from the same day.
Previous.JPG
Current.JPG
Vitor Montalvão

I'm guessing the differential backups were in case of a problem with the transaction log backups, or is that flawed logic?
I don't think so. What happens if the problem is with the differential backup instead of the transactional log backup?
Anyway a differential backup takes longer than a transactional log backup and can origin performance issues if you're running it when the database is being used actively.

Also differential backups are retained longer than log backups.
How are you retaining the backups? Do you copy the files to different tapes?

In both cases I have selected the full backup and a differential backup from the same day.
What is the device? Are you sure that you didn't have a failed full backup for the 2nd screen?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
McCoyIT

ASKER
Backups are being retained on a deduplication disk backup appliance.

We retain transaction log backups for the current week, differential backups for the current month, then just the full backups.

I have confirmed the full backups work fine, I can take a full backup and any combination of log backups from the same day and the restore works fine. It's only the differential restore that is not working, but the agent log shows all backups completing successfully.
Vitor Montalvão

Just wanted to see the backup chain.
You can always query the msdb database:
SELECT TOP 20 database_name, backup_start_date, backup_finish_date, type, backup_size
FROM backupset
WHERE database_name='DatabaseNameHere'
ORDER BY backup_start_date DESC

Open in new window

NOTES: Replace the database name with the correct one
McCoyIT

ASKER
database_name	backup_start_date	backup_finish_date	type	backup_size
McCoy	2016-08-23 10:00:02.000	2016-08-23 10:00:02.000	L	56517632
McCoy	2016-08-23 09:00:03.000	2016-08-23 09:00:05.000	L	256933888
McCoy	2016-08-23 09:00:03.000	2016-08-23 09:00:10.000	I	1680172032
McCoy	2016-08-23 08:00:02.000	2016-08-23 08:00:03.000	L	20602880
McCoy	2016-08-23 07:00:03.000	2016-08-23 07:00:03.000	L	24666112
McCoy	2016-08-23 06:00:02.000	2016-08-23 06:00:02.000	L	8413184
McCoy	2016-08-23 06:00:02.000	2016-08-23 06:00:05.000	I	524575744
McCoy	2016-08-23 05:00:03.000	2016-08-23 05:00:03.000	L	10836992
McCoy	2016-08-23 04:00:03.000	2016-08-23 04:00:03.000	L	5856256
McCoy	2016-08-23 03:00:02.000	2016-08-23 03:00:02.000	L	2120704
McCoy	2016-08-23 03:00:02.000	2016-08-23 03:00:03.000	I	125068288
McCoy	2016-08-23 02:41:13.000	2016-08-23 02:41:13.000	L	55666688
McCoy	2016-08-23 02:31:28.000	2016-08-23 02:31:41.000	D	25271066624
McCoy	2016-08-23 02:00:04.000	2016-08-23 02:00:04.000	L	120747008
McCoy	2016-08-23 01:00:02.000	2016-08-23 01:00:02.000	L	35741696
McCoy	2016-08-23 00:00:03.000	2016-08-23 00:00:54.000	D	25269986304
McCoy	2016-08-22 23:00:02.000	2016-08-22 23:00:03.000	L	109737984
McCoy	2016-08-22 22:00:02.000	2016-08-22 22:00:03.000	L	61171712
McCoy	2016-08-22 21:00:02.000	2016-08-22 21:00:03.000	L	59862016
McCoy	2016-08-22 21:00:02.000	2016-08-22 21:00:14.000	I	2611307520

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
McCoyIT

ASKER
Good catch, Veeam was recently updated so something must have changed with how it handles SQL server backups, that server backup ran at 2:31 so that would explain the extra full backup, just have to figure out what's going on there now. Thanks!
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
McCoyIT

ASKER
My coworker upgraded Veeam and it looks like "Enable application-aware processing" got enabled on the SQL server backup job, which was taking the extra full backup and breaking the chain.