October 29, 2013 at 6:44 am
Hi All,
I have taken full backup of database yesterday(with Recovery Model=full ) from sql 2005
and taken differential backup today 5PM(IST)
now i am restored the full backup in sql2008r2 server(restore with no recovery mode)
Restoring completed and databases state is restoring mode
now i am restoring differential backup to above db with "with recovery mode" . but i am getting this issue
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server '10.102.3.55'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
can any one help me, what is problem?
October 29, 2013 at 6:47 am
Your full backup wasn't a copy-only backup, was it?
October 29, 2013 at 6:50 am
OR had somebody else possibly taken another full backup after yours and before the differential backup?
October 29, 2013 at 7:08 am
PRR.DB (10/29/2013)
Hi All,I have taken full backup of database yesterday(with Recovery Model=full ) from sql 2005
and taken differential backup today 5PM(IST)
now i am restored the full backup in sql2008r2 server(restore with no recovery mode)
Restoring completed and databases state is restoring mode
now i am restoring differential backup to above db with "with recovery mode" . but i am getting this issue
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server '10.102.3.55'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
can any one help me, what is problem?
Try this script and find out your latest full backup.
SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.has_bulk_logged_data,b.is_copy_only,f.mirror
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name='DB name'
--AND B.type='D'
ORDER BY b.backup_finish_date DESC
http://www.sqlserverblogforum.com/2012/03/find-the-latest-backup-in-sql-server/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 29, 2013 at 8:44 am
You have a disconnected differential and full backup. Some other process is running full backups between the time you ran yours and you ran the differential. You won't be able to use that full backup with that differential. You'll need to track down the other full backup.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 29, 2013 at 11:12 am
muthukkumaran Kaliyamoorthy (10/29/2013)
PRR.DB (10/29/2013)
Hi All,I have taken full backup of database yesterday(with Recovery Model=full ) from sql 2005
and taken differential backup today 5PM(IST)
now i am restored the full backup in sql2008r2 server(restore with no recovery mode)
Restoring completed and databases state is restoring mode
now i am restoring differential backup to above db with "with recovery mode" . but i am getting this issue
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server '10.102.3.55'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
can any one help me, what is problem?
Try this script and find out your latest full backup.
SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.has_bulk_logged_data,b.is_copy_only,f.mirror
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name='DB name'
--AND B.type='D'
ORDER BY b.backup_finish_date DESC
http://www.sqlserverblogforum.com/2012/03/find-the-latest-backup-in-sql-server/
Hi all,
I found the problem , SQL Agent job taken fullbackup after my backup.
thanks for your reply
October 29, 2013 at 11:26 am
PRR.DB (10/29/2013)
muthukkumaran Kaliyamoorthy (10/29/2013)
PRR.DB (10/29/2013)
Hi All,I have taken full backup of database yesterday(with Recovery Model=full ) from sql 2005
and taken differential backup today 5PM(IST)
now i am restored the full backup in sql2008r2 server(restore with no recovery mode)
Restoring completed and databases state is restoring mode
now i am restoring differential backup to above db with "with recovery mode" . but i am getting this issue
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server '10.102.3.55'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
can any one help me, what is problem?
Try this script and find out your latest full backup.
SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.has_bulk_logged_data,b.is_copy_only,f.mirror
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name='DB name'
--AND B.type='D'
ORDER BY b.backup_finish_date DESC
http://www.sqlserverblogforum.com/2012/03/find-the-latest-backup-in-sql-server/
Hi all,
I found the problem , SQL Agent job taken fullbackup after my backup.
thanks for your reply
I ran into this very same problem a number of years ago. I was managing a database on a hosted/managed service. Turned out that the data center was performing their own full backups without my knowledge. So when it came time to apply transaction logs I had a rude awaking.
Happens to the best of us....
Glad you found your issue.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 29, 2013 at 11:30 am
Kurt W. Zimmerman (10/29/2013)
I ran into this very same problem a number of years ago. I was managing a database on a hosted/managed service. Turned out that the data center was performing their own full backups without my knowledge. So when it came time to apply transaction logs I had a rude awaking.
Happens to the best of us....
Glad you found your issue.
Kurt
For transaction logs it shouldn't matter as long as you have all the log backups. Only for differentials do you have to match a specific full backup to a specific differential.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply