April 30, 2012 at 12:13 pm
For transaction log shipping, I get that a full backup needs to be created and then thereafter transaction log backups. Restore the full backup on the DR box and then have a collection of jobs to restore the transaction logs every so many minutes on the DR box.
Question is: after initial full backup on prod box and all the transaction logs created on prod box, do I need to keep them or can they be deleted as each of them are restored on the DR box?
Also, I've read that the DB on the DR box can't be used at all even to read... it just sits there doing nothing but restores as transaction logs are received? Trying to determine when to use mirroring vs. shipping since with mirroring, a snapshot can be created so can use the DR box for reporting, at least.
April 30, 2012 at 1:56 pm
Once they have been restored the backups can be deleted. However, it may be wise to keep them around for a time in case you need to restore to another system. In the event of loss of both systems or to try to get a copy of the database at a specific point in time for example.
As far as reading the restoring DB, you can restore with standby that leaves the DB in a restoreable state but also read-only. One important thing to keep in mind is that when you go and restore the next log file all users need to be out of the DB so it can do a restore. Which means if you're looking to do frequent restores it's not a technique you probably want to go with. However, if you're looking to do something less frequent it may work.
May 1, 2012 at 6:26 am
I would still be running a full backup on your system on a regular basis and having the log backups kept too. Rememeber, mirroring is an availability and DR option, but backups are still the fundamental recovery mechanism within SQL Server. For example, what do you do if someone drops a table or deletes data on the production system? How can you recovery? The mirrored server will replicate the drop or delete and all your data is now gone. Backups are still part of the process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2012 at 6:53 am
dixon_dwayne (4/30/2012)
Question is: after initial full backup on prod box and all the transaction logs created on prod box, do I need to keep them or can they be deleted as each of them are restored on the DR box?
there is a retention period setting which governs the length of time the files are retained on the primary backup location and the secondary backup location.
It would be ideal to keep them for a short period so that if you had to re initialise the log shipping for any reason the required files are on disk.
dixon_dwayne (4/30/2012)
Also, I've read that the DB on the DR box can't be used at all even to read... it just sits there doing nothing but restores as transaction logs are received?
That's not correct, you can have the secondary database in standby\readonly mode. The only disadvantage is that user connections are killed to restore the logs each time and it's only a disadvantage if you are restoring the logs at very tight regular intervals.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 7, 2012 at 2:21 am
dixon_dwayne (4/30/2012)Also, I've read that the DB on the DR box can't be used at all even to read... it just sits there doing nothing but restores as transaction logs are received?
often these destination servers are used for reporting purposes too
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply