March 10, 2005 at 3:23 pm
Hi ,I am facing a peculiar problem on SQL 2000 which has bugged me since last few days , with regard to the differential database restore.
On of my servers I have a differential database backup job as defined -
BACKUP DATABASE test1
TO DISK = 'D:\Diff\Diffbackups\Test1_diff.bak'
WITH DIFFERENTIAL
, RETAINDAYS = 2
The full backup job runs at 2:00 am.The differential backup job runs every 6 hrs.
Another tran log backup job runs every 10 min.-
BACKUP LOG test1
TO test1_log
WITH RETAINDAYS = 2
The recovery model of my DB is full.When ever I try to restore the latest diff backup after restoring the full backup on a different server,I get a error saying the "cannot apply the backup on device 'D:\Diff\Diffbackups\Test1_diff.bak' to database Test1.Restore database is terminating abnormally."
Any help will be highly appreciated.
Thanks in advance!!
KG
KG
March 10, 2005 at 4:16 pm
In each RESTORE statement up until the last transaction log restore, you must specify WITH NORECOVERY. This tells SQL that you will be applying more backups. When you execute that last transaction log backup, use the WITH RECOVERY option to tell SQL to bring the database online. By the way, if you forget and issue a NORECOVERY on the last restore, no problem. Just issue one more RESTORE statement as follows- RESTORE databasename WITH RECOVERY but don't provide a backup name.
Steve
March 10, 2005 at 8:35 pm
How are you performing your restore ? Can you please post the scripts ?
March 10, 2005 at 9:16 pm
I have been performing the restore operation directly through enterprise manager and had also selected the options "leave database nonoperational/read only to allow further transaction logs" which is equivalent to the norecovery clause that Steve mentioned.
Is this something to do with the timings of the differential and the transaction log backups?While restoring I am considering the last differential backup set ,as usual.However I have tried all possible options.
regards,
KG
March 10, 2005 at 10:25 pm
Can you list all the backup files U have at present and their backup datetime. We can then help you better....
--Kishore
March 11, 2005 at 8:00 am
Does the backup include all the previous differentials?
You need to have all the differentials in sequence to make a complete backup.
To check you have the right ones available, use the restore with header only command.
That'll give you all the header info for the backups you're trying to restore.
Make sure the LSNs are sequential.
If they are, that's that idea junked, but it's the first thought that comes to mind.
March 11, 2005 at 10:20 am
Rich, The whole purpose of differential backups is that you do NOT need all of them. You only need the most recent. If you needed all, they would be the same as transaction log backups, and therefore redundant.
Steve
March 11, 2005 at 11:51 am
The original post mentioned that the restore was being performed on a different server. I have duplicated the original error in my test environment using the following commands
restore database northwind_test
from disk = 'E:\mssql\MSSQL\Northwind_20050311.bak'
with norecovery
,move 'northwind' to 'E:\mssql\MSSQL\Data\Northwind_test.mdf'
,move 'northwind_log' to 'E:\mssql\MSSQL\Data\Northwind_test_log.ldf'
restore database northwind_test
from disk = 'E:\mssql\MSSQL\Northwind_20050311_dif_2.bak'
with norecovery
,move 'northwind' to 'E:\mssql\MSSQL\Data\Northwind_test.mdf'
,move 'northwind_log' to 'E:\mssql\MSSQL\Data\Northwind_test_log.ldf'
The error was encountered when moving from one server to another. If I did the restore of the differential to the same (original) database it worked
March 13, 2005 at 5:53 pm
The most ignominious thing that I found is that if I reexecute the full backup and the differential in the morning on the source server with the same set of scripts, the restore works fine on the destination server.
The full backup is a maintenance plan job.I don't see any problem with the scheduled one.
kg
KG
March 15, 2005 at 8:46 am
Hi All,
can anyone pls advice me on the next steps regaarding this issue?
Thank you,
kg
KG
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply