Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Redgate Differential Backup and Restore Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:32 PM
Points: 3, 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?

Post #1449745
Posted Monday, May 6, 2013 9:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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)
Post #1449754
Posted Monday, May 6, 2013 9:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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)
Post #1449756
Posted Monday, May 6, 2013 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:32 PM
Points: 3, 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

Post #1449765
Posted Monday, May 6, 2013 9:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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)
Post #1449767
Posted Monday, May 6, 2013 9:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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.





Post #1449769
Posted Monday, May 6, 2013 9:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
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.



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)
Post #1449770
Posted Wednesday, May 8, 2013 8:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 2,832, Visits: 8,509
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




Post #1450596
Posted Wednesday, May 8, 2013 12:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1450739
Posted Wednesday, May 8, 2013 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:32 PM
Points: 3, 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.
Post #1450742
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse