SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Redgate Differential Backup and Restore


Redgate Differential Backup and Restore

Author
Message
Scott Costello-401147
Scott Costello-401147
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 10
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96179 Visits: 38981
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96179 Visits: 38981
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Scott Costello-401147
Scott Costello-401147
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 10
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 :-D
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96179 Visits: 38981
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 :-D



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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14705 Visits: 11848
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 :-D



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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96179 Visits: 38981
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
homebrew01
homebrew01
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12528 Visits: 9222
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





Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19471 Visits: 10042
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Scott Costello-401147
Scott Costello-401147
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 10
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search