May 6, 2013 at 8:49 am
I've recently taken over database duties and am trying to learn as I go. Our company uses Redgate 6.5 for backing up one of our larger databases. Here is our backup schedule...
- Full once a month on the first
- Deferential every day
- Log every hour
This all seems to work fine. This past week we had a need to restore the database to a test database. The restore point we needed was April 18th.
- I restore the FULL backup no problem (April 1st)
- I restore the April 1st DIFF backup no problem
- I attempt to restore the April 2nd DIFF backup and get the following error...
SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
I have found that if I restore all the Log files inbetween the DIFFs I can then restore the DIFF. So...
- Restore FULL
- Restore April 1st DIFF
- Restore All LOG backups between April 1st DIFF and April 2nd DIFF
- Restore April 2nd DIFF
- Restore All LOG backups between April 2nd DIFF and April 3rd DIFF
- Restore April 3rd DIFF
- etc...
This doesn't seem logical to me or from everything I've read. What am I missing or doing wrong?
May 6, 2013 at 9:22 am
Scott Costello-401147 (5/6/2013)
I've recently taken over database duties and am trying to learn as I go. Our company uses Redgate 6.5 for backing up one of our larger databases. Here is our backup schedule...- Full once a month on the first
- Deferential every day
- Log every hour
This all seems to work fine. This past week we had a need to restore the database to a test database. The restore point we needed was April 18th.
- I restore the FULL backup no problem (April 1st)
- I restore the April 1st DIFF backup no problem
- I attempt to restore the April 2nd DIFF backup and get the following error...
SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
I have found that if I restore all the Log files inbetween the DIFFs I can then restore the DIFF. So...
- Restore FULL
- Restore April 1st DIFF
- Restore All LOG backups between April 1st DIFF and April 2nd DIFF
- Restore April 2nd DIFF
- Restore All LOG backups between April 2nd DIFF and April 3rd DIFF
- Restore April 3rd DIFF
- etc...
This doesn't seem logical to me or from everything I've read. What am I missing or doing wrong?
Restore the Full backup (with norecovery or Redgate equivalent), restore most current diff (as done with full backup), restore log files after the most current diff upto the point in time you need.
May 6, 2013 at 9:24 am
If the Redgate Differentials are the same as the native differentials, each differential has all changes since the last full backup. This means you only have to restore the most current differential not all of them.
May 6, 2013 at 9:44 am
Lynn,
Thanks so much for the reply. If I try and restore the FULL and then the DIFF for April 18th (skipping all the differentials between) I get the message...
This operation failed with errors.
Restoring PARS_RESTORE (database) from:
T:\DatabaseBackups\PARS_2013\April 18th Restore\PARS_2013_DIFF_20130419_010100.sqb
SQL Server error
SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3136: SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
SQL Backup exit code: 1100
SQL error code: 3136
If I take a look at all the differential files, they are all random sizes. What i mean is they don't get larger and larger, which I suspect they should if they were all based off the Last FULL backup.
Is it possible that another backup that is running each day is messing things up? Don't judge me as I'm still learning 😀
May 6, 2013 at 9:52 am
Scott Costello-401147 (5/6/2013)
Lynn,Thanks so much for the reply. If I try and restore the FULL and then the DIFF for April 18th (skipping all the differentials between) I get the message...
This operation failed with errors.
Restoring PARS_RESTORE (database) from:
T:\DatabaseBackups\PARS_2013\April 18th Restore\PARS_2013_DIFF_20130419_010100.sqb
SQL Server error
SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3136: SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
SQL Backup exit code: 1100
SQL error code: 3136
If I take a look at all the differential files, they are all random sizes. What i mean is they don't get larger and larger, which I suspect they should if they were all based off the Last FULL backup.
Is it possible that another backup that is running each day is messing things up? Don't judge me as I'm still learning 😀
Sounds like there has been another full backup taken. Have you looked at the logs to check if this has occurred? I'm not sure about Redgate and where it logs info or if it also logs to the msdb database like the native processes do.
May 6, 2013 at 9:53 am
Scott Costello-401147 (5/6/2013)
Lynn,Thanks so much for the reply. If I try and restore the FULL and then the DIFF for April 18th (skipping all the differentials between) I get the message...
This operation failed with errors.
Restoring PARS_RESTORE (database) from:
T:\DatabaseBackups\PARS_2013\April 18th Restore\PARS_2013_DIFF_20130419_010100.sqb
SQL Server error
SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3136: SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.
SQL Backup exit code: 1100
SQL error code: 3136
If I take a look at all the differential files, they are all random sizes. What i mean is they don't get larger and larger, which I suspect they should if they were all based off the Last FULL backup.
Is it possible that another backup that is running each day is messing things up? Don't judge me as I'm still learning 😀
That is the danger of having such a long string of differential backups. A full backup run anytime after the last full will break the string of differentials. Do you know if another full backup is being run?
After a week or so, a differential will probably be close to the size of a full backup anyway, so you would probably be better off running more frequent full backups.
I like to have a full backup run daily, along with transaction log backups every 15 minutes. I rarely use differential backups.
May 6, 2013 at 9:54 am
It may take longer, but if needed you can restore the Full backup from the 1st and then use all the t-logs up to the point in time you require.
May 8, 2013 at 8:17 am
Run something like this to see your backup history and see if there are full backups running at other times. You can add DIFF and LOG to get more results:
SELECT a.server_name as 'Server',
a.database_name as 'Database',
convert(varchar(25),a.backup_start_date,100) AS 'Start Date',
convert(varchar(25),a.backup_finish_date,100) AS 'Finish Date',
DATENAME(weekday, a.backup_finish_date) AS 'Day' ,
datediff(minute, a.backup_start_date, a.backup_finish_date) as 'Mins' ,
cast(cast(datediff(minute, a.backup_start_date, a.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours' ,
case
when datediff(minute, a.backup_start_date, a.backup_finish_date) > 0
then cast(ceiling(a.backup_size /1048576) / datediff(minute, a.backup_start_date, a.backup_finish_date) as decimal (8,1))
else 0
end as 'Meg/Min',
ceiling(a.backup_size /1048576) as 'Size Meg' ,--cast((a.backup_size /1048576) as decimal (9,2)) as 'Size Meg' ,
cast((a.backup_size /1073741824) as decimal (9,2)) as 'Gig', -- div by 1073741824 to get gig
a.user_name,a.backup_size as 'Raw Size'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' /*D=Full*/ AND a.backup_start_date > getdate() -40 -- Last X days
group by a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
order by a.server_name, a.database_name, a.backup_start_date desc
May 8, 2013 at 12:25 pm
Michael Valentine Jones (5/6/2013)
That is the danger of having such a long string of differential backups. A full backup run anytime after the last full will break the string of differentials. Do you know if another full backup is being run?After a week or so, a differential will probably be close to the size of a full backup anyway, so you would probably be better off running more frequent full backups.
I like to have a full backup run daily, along with transaction log backups every 15 minutes. I rarely use differential backups.
I agree - I rarely use differentials and will only consider it when (and if) I get to a point where I cannot backup the full database every night.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 8, 2013 at 12:31 pm
Thanks for everyone's help. I've located a Job that was running backups on a nightly basis which I suspect is the cause of my Differential problems. I've stopped that job and am testing things out and as of now everything looks good.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply