Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Can't restore differential backups in SQL Sever 2012
Posted on 2016-08-22
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?
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
10 Comments
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?
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?
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
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
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?
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.
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.
Just wanted to see the backup chain.
You can always query the msdb database:
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
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
I can see you have 2 Full Backups (type='D') one made at midnight and another one at 02:31AM. Why's that?
And between those 2 Full you have 2 transaction log backups (type='L') and none differential.
First differential (type='I') from yesterday has ran at 03:00AM but it respects the schedule that you said of every 3 hours.
If you know when the problematic backups were made then add the period to the query and post here the results. Example to bring all backups made at 21st of August:
And between those 2 Full you have 2 transaction log backups (type='L') and none differential.
First differential (type='I') from yesterday has ran at 03:00AM but it respects the schedule that you said of every 3 hours.
If you know when the problematic backups were made then add the period to the query and post here the results. Example to bring all backups made at 21st of August:
SELECT database_name, backup_start_date, backup_finish_date, type, backup_size
FROM backupset
WHERE database_name='McCoy' AND
backup_start_date >= '2016-08-21' AND backup_start_date < '2016-08-22'
ORDER BY backup_start_date
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!
Featured Post
This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 7 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.