Click here to monitor SSC
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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37925
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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37925
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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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 :-D
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37925
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3252 Visits: 11771
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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37925
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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2976 Visits: 9071
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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4460 Visits: 9818
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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
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