December 14, 2007 at 12:10 am
Actaully I have to restore my diff backup from live database to Local database on daily basis.
My live server is in US. and there is no network between my local machine & Live server.
I have to keep my backup on local.
I have taken full backup. & restore it to local.
But it's not possible to restore full on daily basis.
I m trying to restore differentail backup on daily basis.
And also I want my local (restored) db up every time.
Once I use diff with recovery it's not possible to go back apply another diff backup.
Can you suggest any one how to do that....
December 14, 2007 at 1:09 am
The only way you can achieve what you want is using logshipping, which would give you read access to the database. With any other solution using backups you always have start the restore cycle with a full backup.
[font="Verdana"]Markus Bohse[/font]
December 14, 2007 at 7:03 am
Hi,
You can use the STANDBY clause for the recovery. This way you can restore the differential backups from day to day. However, when you take a full backup from the live database you should apply this backup (with the STANDBY clause of course) to the local because differential databases are relative to the last full backup.
So you can use a script similar to this:
restore database LocalDB from disk = 'd:\sql\backup\LiveDBDiff20071214.DIF'
with STANDBY = 'd:\sql\LocalDB.undo'
The STANDBY clause gives you the ability to restore more differential backups while being able to query the database between restores.
Your restore schedule would look like this:
- every Sunday a full restore with STANDBY
- on the rest of the days a differential restore with STANDBY
Please note that it would work only if full backups are taken on a weekly basis on the live server. Every intermediate backup (e.g. at live upgrade) would broken the differential chain.
Of course if you don't wanna / can't restore the full DB each week then your only choice is a custom logshipping as Markus mentioned.
I hope it was useful.
-- Erik http://blog.rollback.hu
December 14, 2007 at 8:29 am
If you need to change the data in your local database, you have no choice but to use the full backup.
However, daily restores seems like a lot. Think about whether you really need this.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply