Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


This differential backup cannot be restored because the database has not been restored to the...


This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Author
Message
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
I restore a Database from a Complete Backups:


RESTORE DATABASE BASSISControlCYP
FROM DISK = 'H:\Backups\Complete\BASSISControlCYP_backup_201208170809.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCYP.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCYP.ldf'



Processed 640 pages for database 'BASSISControlCYP', file 'BASSISControl' on file 1.
Processed 3 pages for database 'BASSISControlCYP', file 'BASSISControl_log' on file 1.
RESTORE DATABASE successfully processed 643 pages in 0.165 seconds (30.409 MB/sec)


Then I apply a differential backup without any issues:


RESTORE DATABASE BASSISControlCYP
FROM DISK = 'H:\Backups\Differential\BASSISControlCYP_backup_201208200745.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCYP.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCYP.ldf'


Processed 104 pages for database 'BASSISControlCYP', file 'BASSISControl' on file 1.
Processed 3 pages for database 'BASSISControlCYP', file 'BASSISControl_log' on file 1.
RESTORE DATABASE successfully processed 107 pages in 0.080 seconds (10.375 MB/sec).

Then I restore an almost Identiacal Database from a Complete Backup:


RESTORE DATABASE BASSISControlCTL
FROM DISK = 'H:\Backups\Complete\BASSISControlCTL_backup_201208170808.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCTL.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCTL.ldf'



Processed 760 pages for database 'BASSISControlCTL', file 'BASSISControl' on file 1.
Processed 3 pages for database 'BASSISControlCTL', file 'BASSISControl_log' on file 1.
RESTORE DATABASE successfully processed 763 pages in 0.124 seconds (48.024 MB/sec).

The I try and apply the differential but I get an error:


RESTORE DATABASE BASSISControlCTL
FROM DISK = 'H:\Backups\Differential\BASSISControlCTL_backup_201208200959.bak'
WITH REPLACE,
NORECOVERY,
MOVE 'BASSISControl' TO 'F:\MSSQL\Data\BASSISControlCTL.mdf',
MOVE 'BASSISControl_log' TO 'F:\MSSQL\Log\BASSISControlCTL.ldf'



Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


I checked the backup history to see if the chain was broken and that is not the case.

I used the following to do so:


SELECT
TOP 100
s.database_name,
CASE s.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO



I don't get it. I have done this before and no issues?

Any help would be greatly appreciated.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
I just tried restoing two more database from differntial backups, the first worked the second did not.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
The diff backups in question are not "copy only" backups are they?

Is_copy_only from msdb.dbo.backupset



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
anthony.green (8/20/2012)
The diff backups in question are not "copy only" backups are they?

Is_copy_only from msdb.dbo.backupset


They are not copy only they are full and differential backups.

What do you mean by Is_copy_only from msdb.dbo.backupset?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
The column Is_Copy_Only is backupset.

But forget that comment, Diff backups cannot be made in a copy_only state

http://msdn.microsoft.com/en-us/library/ms191495.aspx

Your full backups though could be created as copy_only and therefore will not allow the diff to be restored.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7466 Visits: 15142
There are only two reasons I can think of for this:
(1) Another full backup was taken between the first full backup and the differential
(2) The differential backup is actually a backup of a different database.

What were the results from the query you ran?

John
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869

BASSISControlCTL Differential 0 Seconds 2012-08-20 09:59:03.000
BASSISControlCTL Differential 0 Seconds 2012-08-20 07:46:09.000
BASSISControlCTL Full 0 Seconds 2012-08-17 08:08:55.000



I tried it with the first differential backup. Then I performed another differential backup and tried applying the second differential.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17599 Visits: 32267
Maybe I misunderstood, you're trying to apply the same differential that is matched to the first backup to a second, different backup? You can't do that. Differentials are tied directly to the last, full, backup. You can't restore a differential to any other full backup other than the last full one prior to the differential being run.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
Grant Fritchey (8/20/2012)
Maybe I misunderstood, you're trying to apply the same differential that is matched to the first backup to a second, different backup? You can't do that. Differentials are tied directly to the last, full, backup. You can't restore a differential to any other full backup other than the last full one prior to the differential being run.


Grant,

There was only one full backup for each of the Databases.

I restored the complete backup then I attempted to restore the first differential and it failed. I performed a second differential and it failed as well. Did you notice the backup history that you asked for?

I did this to a total of six Databases, 3 failed 3 sucessfull.

I only performed the 2nd differential on the 1st Database that failed.

I have 1 full backup then I have one differential for each of the other 6 databases.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5164 Visits: 4869
I'm backing up from SQL Server 2005 32bit and I'm restoring to SQL Server 2008 R2 64 bit.

I have not attempted to restore all 24 database but of the 6 I restored it fails every other Database same scripting methodology.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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