October 11, 2005 at 2:35 pm
Hi
We have a backup setup where we take "FULL" backup once a week (On Sunday), "DIFFERENTIAL" daily (at night) and "TRANSACTION" thoughout the day at certain intervals (every hour)
We have a backup server where these files are copied immediately after they are created.
Here is what I wanted to do.
Restore the FULL backup on backup server on say early Monday morning (once a week) and then every day (Tue-Sat) after that just restore the "DIFFERENTIAL" backup on top of what was restored the previous day.
I think I can do it If I keep the restored database on backup server in "non-operational" mode or in "read-only" mode where "additional transaction/diff logs can be applied"
But can we leave the restored database in full operation mode and just apply DIFFERENTIAL backup daily without actually restoring FULL first then applying DIFFERENTIAL.
I did think about this and it looks like not a practical situation because what will happen to the changes that happened on the database if we leave it operational and if we want to just apply a DIFFERENTIAL from another server on top of that.
So again I am not sure if I am asking the right question but probably I might get some good suggestion so I am asking this question here anyway.
Thanks for all the help.
October 11, 2005 at 3:11 pm
It sounds like you're talking about what Microsoft calls a standby server using differential backups instead of log backups. Check out "standby servers" in BOL.
You're right that you can't restore another differential after restoring "with recovery" to put the database in full operational mode. Also, I don't think you can restore a differential backup and leave the database in standby mode. That seems to only be an option when restoring a transaction log backup.
Greg
Greg
October 12, 2005 at 10:01 am
Why fool around with the differential backups when you've got the transaction log backups?
If you do RESTORE LOG WITH STANDBY= for each transaction log (schedule it to run after the files are copied to the backup server), you will have a read-only copy of the production database.
There is no way to set up a standby database that you can modify and still apply diff or tlog backups from production. You could create a second database on the backup server for the updates. You could also use replication to keep some tables on the backup in sync with production while allowing inserts & updates to other tables.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply