February 22, 2011 at 2:05 pm
Hi all.
I just want to verify something I was told would work. Here is the scenario:
1) Restore a full backup of the data to my local server.
2) using automation, copy all transactional backup files every 2 hours each day.
3) using automation, restore said backups to the local data
If I keep the cycle of 2 & 3 up every day, will I have a current copy of the data without ever having to restore a full backup again?
(Note: Every morning, I am doing a full backup on the main production server either way.)
Thanks,
Mike
February 22, 2011 at 2:32 pm
Yes, that is a log shipping scenario. Is there a reason you wouldn't just use Log Shipping rather than building this up yourself?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 22, 2011 at 2:40 pm
Yes, there is a reason. All the options, log shipping, mirroring, etc., leave the database in a non-usable (standby) state. I need a database copy that I can run backups from and even use to debug problems as they pop up.
If this works, I might modify my maintenance plan from 1 per hour to 1 per 20 or 30 min for transactional backups to keep our local database very current.
Thanks for the info.
Mike
February 22, 2011 at 2:50 pm
If you are going to continue to restore the log backups then you are going to have to leave it in recovery making it unusable as well.
Have you read THIS ?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 22, 2011 at 2:51 pm
slight problem, what you describe is exactly the same as logshipping. As soon as you bring the database online (with recovery option) you won't be able to restore any more log backups without starting from a full backup again.
---------------------------------------------------------------------
February 22, 2011 at 2:55 pm
You can configure the log shipping to leave the database in STANDBY mode which allow all of the querying and troubleshooting that you need. You won't be able to edit data, but then in your original plan you wouldn't be able to do it that way either.
February 22, 2011 at 2:59 pm
If I left the secondary database in Standby - can I make backups from the secondary database?
February 22, 2011 at 3:08 pm
No, not while it is in STANDBY mode. You can recover the database, but then that will break the log shipping configuration.
February 22, 2011 at 3:45 pm
Can you recover the database from Standby, make a backup, put the database back to standby and re-initiate log shipping without starting from a fresh full backup?
February 22, 2011 at 3:47 pm
nope.
what you are hoping for cannot be done, whichever way you cut it.
---------------------------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply