March 11, 2004 at 5:02 pm
Hi-
I am trying to avoid moving a large (6 gig) database file from one server to another (development-test) so I created a differential backup and attempted to apply it on the test server. Using restore I point to my file and sql server comes back and says that it is a different database then what exists, exiting restore abnormally.I think the recovery of the last restore may have something to do with it, but I am not sure
How do I move and apply these differntial backups on my servers? Right now, we have no need to standby servers or log shipping. These changes are few and far between.
Thanks in advance.
Joe
March 12, 2004 at 7:31 am
You can't apply a differential backup on its own. Think of it being rather like restoring TLOG backups except that you only need the most recent differential.
What you must do is restore the most recent full backup previous to the differential with no recovery, then apply the differential with recovery if you don't want to apply transaction log backups, with no recovery if you do wish to apply tlog backups.
Steve
March 12, 2004 at 1:55 pm
You CANNOT apply a differential without applying a FULL backup.
If I understand you correctly, you made a full backup and restored it to a 'backup' server. Now time has gone by and you want to update the information on the backup server; so you made a differential backup and want to apply it to the 'backup' server. Can't do it.
That's why log shipping and replication were created.
You can't apply a differential or transaction log backup to a database that is already operational. That's why when you are restoring several backups (FULL, Differential, and Logs) you have to use the WITH NORECOVERY option. Once the database recovers, that's it you can't restore any more backups to it.
-SQLBill
March 24, 2004 at 3:02 pm
Maybe I didn't undertand these responses, I tried restoring the databases with the NORECOVERY option and it didn't work. Any example sql so maybe I can figure out what I did wrong?
Thanks,
Joe
March 24, 2004 at 3:21 pm
Ok, did you restore a full backup and then the first differential wih RECOVER or with NO RECOVER. If RECOVER did you restore the full backup first then the each differentail thru the current one you are trying, using WITH NO RECOVER between each until done (which if you plan to add another differential later you need to again use NO RECOVER until you are ready to recover and use.
March 25, 2004 at 8:11 am
Antares686,
Its not necessary to apply all differential backups, only the most recent.
Joe,
You must apply your full database backup with no recovery first.
Then immediately apply your most recent differential backup. If you have transaction log backups that are more recent than the differential, then you would use NO RECOVERY in your differential restore also. If your differential is the LAST backup that you are restoring, then use WITH RECOVERY.
If you are applying transaction log backups, you must apply ALL transaction log backups beginning with the first one AFTER your full/differential (whichever is most recent) up to your target recovery date.
Basically, if you are restoring multiple backups, you must tell SQL that you will apply another one by using WITH NORECOVERY. You tell SQL that you are finished by using WITH RECOVERY.
RESTORE DATABASE PUBS
FROM DISK = 'D:\DB_Backup\PUBS_my-full-backup.bak'
WITH NORECOVERY
RESTORE DATABASE PUBS
FROM DISK = 'D:\DB_Backup\PUBS_my-diff-backup.bak'
WITH RECOVERY
If logs -
RESTORE DATABASE PUBS
FROM DISK = 'D:\DB_Backup\PUBS_my-full-backup.bak'
WITH NORECOVERY
RESTORE DATABASE PUBS
FROM DISK = 'D:\DB_Backup\PUBS_my-diff-backup.bak'
WITH NORECOVERY
RESTORE LOG PUBS
FROM DISK = 'D:\DB_Backup\PUBS_my-tlog-backup_1.bak'
WITH NORECOVERY ...
RESTORE LOG PUBS
FROM DISK = 'D:\DB_Backup\PUBS_my-tlog-backup_n.bak'
WITH RECOVERY
March 25, 2004 at 8:13 am
Oops, sorry about that I was thinking TL backups when I was typing.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply