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 12»»

This differential backup cannot be restored because the database has not been restored to the correct earlier state. Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 8:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 4,263, Visits: 4,311
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/

Post #1347206
Posted Monday, August 20, 2012 8:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 4,263, Visits: 4,311
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/

Post #1347214
Posted Monday, August 20, 2012 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1347222
Posted Monday, August 20, 2012 8:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 4,263, Visits: 4,311
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/

Post #1347225
Posted Monday, August 20, 2012 8:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1347229
Posted Monday, August 20, 2012 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:32 AM
Points: 5,490, Visits: 10,354
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
Post #1347232
Posted Monday, August 20, 2012 8:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 4,263, Visits: 4,311
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/

Post #1347249
Posted Monday, August 20, 2012 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:01 AM
Points: 14,208, Visits: 28,538
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
Post #1347326
Posted Monday, August 20, 2012 10:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 4,263, Visits: 4,311
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/

Post #1347333
Posted Monday, August 20, 2012 10:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 4,263, Visits: 4,311
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/

Post #1347341
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse