SQLServerPedia

Here’s the actual question in its entirety:

We are trying to implement log shipping… We implement a transactional backup hourly. If our nightly full backup takes 1 hour and 45 minutes, what is our recovery process? Is it a bad thing to have a transactional backup in the middle of a full backup? 

Now, your question, whether you realize it or not, is pretty involved and has three major elements.  First, you want to know about implementing log shipping.  Second, you want to know about the recovery process considering your current backup process of hourly transaction log dumps and nightly full database dumps.  And finally, you want to know if a transaction log backup can cause problems if it occurs while a database backup is already processing. 

FULL DISCLOSURE - I’ll go ahead and say now that I’m going to include verbiage around a Quest product in this response – not a blatant advert, but I will be mentioning one of our products.

So, to your first question – remember that log shipping is a high-availability technique used to speed recovery of a database should you ever have a crash.  You don’t say which version of SQL Server you’re running.  But I assume that you must be running on SQL Server 2000, otherwise you would have mentioned other (and better, IMO) alternatives such as database mirroring.  Assuming you’re on SQL2000, then log shipping is not a big deal to implement and is widely discussed elsewhere on the internet, such as Microsoft’s TechNet (www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx) and MSDN (msdn.microsoft.com/en-us/library/ms187103.aspx).  I won’t rehash an elementary concept of SQL Server when others have already done an excellent job of telling you how to implement it.  Since you might still be in the planning stages of your high-availability solution, here are two caveats that are often overlooked by newbies.  One, don’t forget that the database that is the target of log shipping is going to be in recovery mode all the time until it becomes the primary database.  You pretty much cannot use it unless the source database fails.  Two, log shipping works great with LiteSpeed.  So, if you’re a Quest Software customer using LiteSpeed, then you can enjoy all the benefits of speed, reduced disk consumption, and encryption that LiteSpeed offers.

To your second question, your current backup process, in which you backup the transaction logs every hour and the database nightly, is pretty sound.  Keep in mind that you could potentially lose up to 59 minutes of data with any given database failure.  If 59 minutes does not represent a significant amount of work on the database in question, then great!  However, I rarely had a production database where I was comfortable making users re-key up to 59 minutes of work.  Fifteen minute intervals for a transaction log dump were more in my range of comfort.  Additionally, consider that most applications do NOT have users working around the clock.  Because of that very natural business cycle, you can often lessen or even completely discontinue transaction log backups during the wee hours.  For example, you might run the transaction log backups every 15 minutes from 6:00 AM EST to 8:00 PM EST every day, since you have few if any users after that time.  Alternately, you might run the log dumps ever 15 minutes from 6:00 AM to 8:00 PM EST, run them hourly from 8:00 – 10:00 PM EST, and then only do one more full database backup until the next morning.  Frequent log backups are important also because that’s the primary means by which you keep the transaction log from growing too large.

Finally, with your last question, starting a transaction log backup while a full database backup is still running will behave differently on different versions of SQL Server.  For SQL2000, SQL Server doesn’t allow you to run the transaction log backup while the full database backup is running (at least that’s what my fragile memory is telling me).  For SQL2005 and later, transaction log backups and full database backups can run concurrently though the transaction log backup will certainly be slower because of it and the database backup will likely be slower too.  The full database backup will contain all data in the state of the database at the time it completed, while the transaction log backup will contain all of the transactions that have run since the last full database backup OR transaction log backup.  Since the transaction log backup will probably finish before the 90 minute long database backup, you should think of that transaction log backup as the last log backup before the new full database backup starts the transaction log backup process all over again.  Thus, in an emergency restore situation, you could apply the previous day’s full database backup plus all transaction logs up to the time that the transaction log ran concurrently with tonight’s full database backup to get a recovery that included all transactions up to the most recent point in time.  Once the full database backup completes, however, the clock is started over and you would have to start applying transaction log backups that were subsequent to the completion of the full database backup.

Hope this helps,

-Kevin