July 22, 2004 at 3:52 pm
Hi Folks,
I have just implemented a backup plan at a clients which has two jobs the Database backup and the hourly transaction backup. It is as follows:
Nightly Backup
Step 1 - Set Truncate Only
BACKUP LOG PETCWebsite WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'
Step 2 - Backup up database
BACKUP DATABASE PETCWebsite To PETCWebsite_Data WITH INIT
WAITFOR DELAY '00:00:05'
Step 3 - Copy to SAN
c:\BATCH\CopySQLBackups\copyPETCWebsiteData.cmd
copyPETCWebsiteData.cmd
(REM *********************************************************
REM This cmd file is designed to copy the database backup file onto Server ANPLSRV002
REM This is so that the Veritas backup system can backup the .bak file to tape
REM ---------------------------------------------------------
REM Written on the 22 July 2004
REM Written By Jamie Mack SBS Group Ltd
REM ---------------------------------------------------------
REM Please document any changes to this cmd file
c:\BATCH\robocopy.exe E:\SQLBackups\ \\anplsrv002\SQLScriptedbackups\ PETCWebsite_Data.BAK /NP /R:10 /W:2
IF ERRORLEVEL 2 GOTO :NOERRORLEVEL
EXIT
:NOERRORLEVEL
EXIT /B 0)
Transaction Log Backup
Step 1 - Backup Log
BACKUP LOG PETCWebsite To PETCWebsite_Log
WAITFOR DELAY '00:00:05'
Step 2 - Copy to SAN
(virtually the same as above)
To the Question
I have opened the transaction log backup with ApexLog SQL and I have found transactions from a week ago, these transaction cannot be reconstruted (UPDATE reconstruction failed due to lack of historic data.)however I am consirned that they are in the log backup as i can see no need for them to be there. The day that the Transaction backups occured can be reconstructed as I expected.
Can someone please explain to me what is happening as I cannot figure this out.
Thanks in advance.
Jamie Mack
July 23, 2004 at 6:28 am
I don't know why you are seeing old transactions in the log backup, but I believe that truncating the log before the nightly database backup is a BAD idea. If the database backup is lost or corrupted, and you need to recover from a previous night's backup, you will not be able to apply any of the transaction logs taken after the truncate.
July 23, 2004 at 7:45 am
I agree that truncating the log prior to backup is a bad idea. We use a scheduled job to b/u the db with the following steps:
1. Integrity check.
2. Reindex.
3. B/u trans log.
4. Full db b/u.
Since I've adopted that order, I've never had a restore test fail.
July 23, 2004 at 1:11 pm
Your method will start falling down if you want to do more than one tlog a day. There is no reason to backup the tlog then backup the database full. The full includes a tlog basically at the end to catch any transactions that happened during the backup. Diff backups dont' clear the tlog but fulls should.
I guess what I'm wondering is why the tlog right before the full? Just do your checks reindex if you want and then the full. No truncate tlog or tlog backup should be needed.
Wes
July 23, 2004 at 1:46 pm
I didn't start doing it the way I outlined above, since nothing in any book I read indicated to do it that way. But when I would do test restores, there would be an occassional disjoint between the b/u and the first trans log. Since I developed this set of steps, however, I have never had a problem with my test restores.
We do multiple translogs through the day, ranging from 1 on some dbs to 12 on the one with the most intense transactions. So far so trouble since I used this method.
July 23, 2004 at 6:23 pm
On our most active server I do weekly fulls nightly diffs and tlogs every 15 minutes. I've never had a problem at all with the schedule. I've been running that kind of schedule on as many as 60 servers since sql 7 came out and have never experienced a disjointed tlog(I'm not real sure what you mean by that) that I know of.
Wes
July 25, 2004 at 12:34 pm
Jamie,
Why don't you check out the backup/restore scripts here and see what you think:
http://weblogs.sqlteam.com/tarad
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
July 25, 2004 at 5:17 pm
Why go anywhere else? the script library here as a ton of good stuff for backups and restore.
Wes
July 26, 2004 at 11:15 pm
Hi Guys,
Thanks very much for all of your input, it is much appreciated. I am about to look into all of your suggestions above. I will post back and let you know how I got on.
Thanks very much,
Jamie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply