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

  • 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/

  • 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/

  • The diff backups in question are not "copy only" backups are they?

    Is_copy_only from msdb.dbo.backupset

  • 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/

  • 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.

  • 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

  • BASSISControlCTLDifferential0 Seconds2012-08-20 09:59:03.000

    BASSISControlCTLDifferential0 Seconds2012-08-20 07:46:09.000

    BASSISControlCTLFull0 Seconds2012-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/

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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/

  • 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/

  • The error you're getting, "This differential backup cannot be restored because the database has not been restored to the correct earlier state.", is usually indicative that the full backup and the differential are out of sync. It's a common error. I don't see precisely what's happening on your end, but I'm pretty sure, based on the error, that something is amiss with the alignment of full to differential. Is it possible the backups where taken without using INIT and the correct FULL or differential are stacked inside the file or something along those lines?

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • here is the code from the Maintenance Plan.

    I noticed that it is set to NOINIT and I do see an option where you can set that.

    I'm not sure about the Full Backup logic?

    BACKUP DATABASE [BASSISControlCYP] TO DISK = N'D:\BACKUP\BASSISControlCYP_backup_201208201341.bak' WITH NOFORMAT, NOINIT, NAME = N'BASSISControlCYP_backup_20120820134115', SKIP, REWIND, NOUNLOAD, STATS = 10

    I had created these jobs as well as the scripts back in January.

    At that time the Server was going to move to another Data Center and it was going from SQL Server 2005 to SQL Server 2005.

    I had all of the Databases restored and I was able to apply the differentials to all 24 Databases.

    At first I encontered the same error and I found out that a Vendor was using a 3rd Party Tool to perform backups even though I had told them to stop all backups.

    They stopped the backups, I ran the query that I sent you and I was good to go.

    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/

  • I just did a complete backup followed by a Differential Backup and restored both without issue.

    It is almost as if sometning got backed up by that outside vendor with a 3rd party tool since my last backup but it is not showing in my query.

    Back in January there were told to turn off the backups and they turned off one set but forgot to turn off another.

    They were told to turn them back on when the Server move was postponed.

    I may have used a different query last time.

    I suspect that there may be something wrong with my query?

    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/

  • Since it's backing up with NOINIT, it's likely putting one backup on top of another. You may not be seeing the right backup.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I found this Script. I had two backups at 8:08:22 and 8:08:55.

    The other script return one at 8:08 AM.

    I was backing up a lot of Databases developing SSIS Packages and Admin Support all at the same time and hopefully this was just a case of unintentional operator error.

    Thank you for the help.

    USE MSDB

    GO

    SELECT

    bs.server_name AS Server, -- Server name

    bs.database_name AS DatabaseName , -- Database name

    CASE bs.compatibility_level

    WHEN 80 THEN 'SQL Server 2000'

    WHEN 90 THEN 'SQL Server 2005 '

    WHEN 100 THEN 'SQL Server 2008'

    WHEN 110 THEN 'SQL Server 2011'

    END AS CompatibilityLevel , -- Return backup compatibility level

    recovery_model AS Recoverymodel , -- Database recovery model

    CASE bs.type

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    WHEN 'F' THEN 'File or filegroup'

    WHEN 'G' THEN 'Differential file'

    WHEN 'P' THEN 'P'

    WHEN 'Q' THEN 'Differential partial'

    END AS BackupType, -- Type of database baclup

    bs.backup_start_date AS BackupstartDate, -- Backup start date

    bs.backup_finish_date AS BackupFinishDate, -- Backup finish date

    bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion

    CASE device_type

    WHEN 2 THEN 'Disk - Temporary'

    WHEN 102 THEN 'Disk - Permanent'

    WHEN 5 THEN 'Tape - Temporary'

    WHEN 105 THEN 'Tape - Temporary'

    else 'Other Device'

    END AS DeviceType, -- Device type

    bs.backup_size AS [BackupSize(In bytes)]

    --, -- Normal backup size (In bytes)

    --compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)

    FROM msdb.dbo.backupset bs

    INNER JOIN msdb.dbo.backupmediafamily bmf

    ON (bs.media_set_id=bmf.media_set_id)

    WHERE bs.database_name = 'BASSISControlCTL'

    ORDER BY bs.backup_start_date DESC

    GO

    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/

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply