December 14, 2011 at 12:50 pm
We are running SQL Server 2005 SP3 on Windows 2003 Server. We are currently taking a daily full database backup at 4 A.M. Now, we would like to add transaction log backups every 2 hours during the day. Our system is not too volatile. We would like to have the following daily backup plan:
1. Full Backup at 4:00 A.M.
2. Transaction Log Backups at 8:00 A.M., 10:00 A.M., 12:00 P.M., 2:00 P.M. and 4:00 P.M.
The above backup plan would be scheduled with the SQL Server Maintenance Plans. One question, in order to get the Transaction Log Backups to run every two hours I am thinking I would have to create a separate Maintenance Plan/Job for each Transaction Log Backup so I may schedule the Job at the two hour intervals (8, 10, 12, 2 and 4). Is this the best way to go about it? (I do not think I can have multiple Back Up Database Tasks within a Maintenance Plan and have separate schedules for each of those Back Up Database Tasks within the same Maintenance Plan.)
Note: The Back Up Database Task (within the Maintenance Plan) appends the Timestamp to the database name (pubs_201112140400.bak, pubs_201112140800.trn, pubs_201112141000.trn, pubs_201112141200.trn, pubs_201112141400.trn and pubs_201112141600.trn)
Is .trn an appropriate Backup file extension for Transaction Logs or does it matters with SQL Server?
Below is a Recovery Plan Example. In this example, I need to restore/recover to my noon transaction log backup. Would the following work?
--Put DB in Singele User Mode (Must get exclusive access to DB)
USE Master
GO
Alter Database Pubs
SET SINGLE_USER With ROLLBACK AFTER 10
--Restore Full Backup.
RESTORE DATABASE Pubs
FROM DISK = 'D:\SQL Backups\Full\Pubs\Pubs_pubs_201112140400.bak'
WITH NORECOVERY, REPLACE
--Restore first (8 A.M.) Transaction Log Backup.
RESTORE LOG Pubs
FROM DISK = 'D:\SQL Backups\TLogs\Pubs\Pubs_Log_pubs_201112140800.trn'
WITH NORECOVERY
--Restore second (10 A.M.) Transaction Log Backup.
RESTORE LOG Pubs
FROM DISK = 'D:\SQL Backups\TLogs\Pubs\Pubs_Log_201112141000.trn'
WITH NORECOVERY
--Restore third (12 A.M.) Transaction Log Backup.
RESTORE LOG Pubs
FROM DISK = 'D:\SQL Backups\TLogs\Pubs\Pubs_Log_201112141200.trn'
WITH NORECOVERY
--Bring the database back online.
RESTORE DATABASE Pubs WITH RECOVERY;
--Put DB in Multiple User Mode.
USE Master
GO
Alter Database Pubs
SET MULTI_USER
Lastly, I do not think we need a Differential Backup in our situation. I believe the Differential Backup takes longer to back up than the Transaction Log Backup but it (Differential Backup) takes less time to restore. Is this correct?
Thanks in advance, Kevin
December 14, 2011 at 1:53 pm
Hi Kevin,
What is the size of the database? The reason for asking this is... to suggest you to make sure you have the required disk space to store the FULL backups.
You are right about 2 Maintenance Plans and that is what is done (at least what I have seen in most cases) and also the file extension (.trn), I do the same, though SQL Server will accept an extension as .bru or .you. It is for our convenience that we name the backup accordingly just to avoid someone deleting the files thinking they are something else..
I think you have got the restore sequence right (so no worries there)..
But I would like to ask you this, why is the log backups only every 2 hours, why not more often?
Finally, abt the Diff backup, yes they take more time when backing up as it backups all the changes since last full backup, but would be saving a lot of time in case of restoring when there are lot of T-log backups.. This is more common when there are weekly full, daily diff and log backups every 10 mins. in those cases, restoring 200 Log backups one by one can be saved by the recent differential backups.. (I guess you might be aware of this)..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 14, 2011 at 2:00 pm
just add another subplan to your existing maintenance backup plan.
use .trn for the extension. You can use whatever you want but .trn is the industry standard.
In the restore sequence just use with recovery on the last log restore.
don't forget to add a maintenance cleanup task to the maintenance plan to delete old backups and control space usage that way.
How often you take tran log backups depends on the maximum amount of data you can afford to lose.
---------------------------------------------------------------------
December 15, 2011 at 4:15 am
Your approach is basically sound, yes. Be sure that two hours is how much data the business is prepared to lose in the event of a catastrophic issue. Also, test the restore process on a different server so that you know how to do it, not just that it can be done. Then practice it a few more times to be sure. Taking the backups is the easy part. It's restoring the data that really counts.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply