-
Title
MSSQL Differential restore fails with "This differential backup cannot be restored because the database has not been restored to the correct earlier state" -
Description
When using a strategy where a Full backup runs once a week and differentials or incrementals run daily, if a one off full backup is run manually after the scheduled full this may affect later restore attempts.
If an attempt is made to restore a database by restoring the normal full first and then a differential taken after the one off full, the job will fail because a full backup has been created some time between the weekly scheduled full and the differential stored in NetVault.
The below error will be returned:
ODBC Error Number:42000, MsgState=1, Severity=16, Servername= ProcName=, LineNumber=1, Message:[Microsoft][ODBC SQL Server Driver][SQL Server]This differential backup cannot be restored because the database has not been restored to the correct earlier state.Differential Restore: Be sure you have restored the corresponding full database as non-operational or read only.Error restoring database -
Cause
If one-off full backups of the database/s are run in the middle of the scheduled full - incremental/differential sequence, this will cause problems when trying to restore one of the incremental/differential backups taken after the one-off backup.
The SQL db registers that a full backup of the database has been taken within it's own logs seeing this new full as having superseded the previous full and seeing subsequent incremental/differential backups as no longer being based on the last scheduled run full backup.
-
Resolution
If running occasional one-off full backups outside of the scheduled sequences is required, please use the 'Copy-Only backup' method of NetVault Backup Plug-in for SQL Server.
Copy-Only backup for Online VDI
Copy-Only backups are supported by:• SQL Server® versions: 2005 and later• Recovery models: AllCopy-Only backups are independent of the normal sequence of backups. A Copy-Only backup cannot serve as the base backup for a series of differential backups as a Full Backup can.Performing a Copy-Only backup does not affect what is backed up by the next differential backup. Copy-Only backups are ideal for situations that require special purposes, such as creating test environments or standby databases.This is because a Copy-Only data backup does not impact the differential bitmap within the SQL db, meaning, subsequent differential backups are unaffected by the Copy-Only backup. These types of backups are ideal for situations that require one off point in time full backups to be run for special purposes outside the normal backup sequence.
Copy-Only backups are used when requirements demand that a backup be created without interfering with regular scheduled backup sequences, Copy-Only backups is a valid strategy.Often times, you must create a full backup outside of the regular backup schedule, such as when refreshing your test databases.This process could potentially interfere with the regular backup schedule and throw off subsequent differential.Manual backups using 'Copy-Only' would be the way to avoid affecting the scheduled Full-Differential sequences.
Note that a 'Copy-Only' is an standalone FULL. So you can use a 'Copy-Only' to restore the databases as they were when the 'Copy-Only' backup was taken, the same than if you were restoring a Full Backup.