July 30, 2009 at 8:59 am
Can two consecutive logs backups be made so that they contain the same transactions?
I have a SQL Agent job that backups all of our databases to the D: drive on the server. The script performs full backups, log backups with INIT, or log backups with NOINIT, depending on the time of day. We do a full backup at 11PM, the start with a new set of logs at 12am, and add logs to the file until 10PM.
Now, my manager wants me to run two consecutive backups, one to a folder on D: and one to a folder on F:.
I told him that once you successfully back up the log, you can't make the same backup again, and that the the log file should be copied from D: to F: using the operating system COPY (or XCOPY) command.
He insists that log consecutive log backups can be taken, and that they will be identical, if I use WITH NO_TRUNCATE.
Who's correct?
July 30, 2009 at 9:09 am
The log backups won't necessarily be identical. The NO_TRUNCATE on the first one will ensure that they both start at the same point, but the second may contain records the first didn't.
Rather take the backup once and copy the resultant file whereever. SQL 2005 added the MIRROR clause to a backup, to allow the backup to be written to two places, but 2000 can't do that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2009 at 9:10 am
The NO_TRUNCATE option for BACKUP LOG allows the backing up the log in situations where the database is damaged. Such as when taking a tail log backup after losing a database mdf file.
This will not provide you with two identical log backups. Your option to copy the backup file to a second location is the proper way to accomplish the task you desire.
July 30, 2009 at 10:15 am
Gail and Lynn,
Thanks for responding so quickly. I performed a quick test whereby I made a full backup followed by 4 sets of dual log backups. This is a summary of the steps:
Take FULL backup
Make some changes
Take Log backup WITH NO_TRUNCATE to location A
Take Log backup to location B
Make some changes
Take Log backup WITH NO_TRUNCATE to location A
Take Log backup to location B
..etc
FIRST RESTORE TEST:
I restored the full backup using WITH STANDBY= so I could check the state of the test table between each restore.
I then restored the 4 log backups from the first backup file, again using WITH STANDBY=. They all restored fine.
SECOND RESTORE TEST:
I restored the full backup using WITH STANDBY=
I then restored the all 8 log backups, alternating between the first and second log files, which represented
the actual order of all of the log backups. This was successfull.
THIRD RESTORE TEST:
I restored the full backup using WITH STANDBY=
I then tried to restore using only the second log backup file. This failed with
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 5000000009500001, which is too late to apply to the database. An earlier log backup that includes LSN 5000000009100001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
So, the goal of having two separate and independent log backups is not achieved.
Thanks again.
August 15, 2009 at 5:27 am
Exactly what do you want to achieve?
If you are using NO_LOG or TRUNCATE_ONLY options in any case your log sequence is going out of order.
don't you want to recover the db back?
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 17, 2009 at 12:18 pm
The goal is to have two copies of each full backup and each log backup stored on different physical media (in this case, the D: drive and F: drive on the SQL Server in question):
BACKUP LOG to D: (WITH NO_TRUNCATE)
BACKUP LOG to F: (log truncated)
I already knew that the request to perform two consecutive backups (particularly the log backups) would not work. I just wanted some expert opinions to support my assertion.
I wasn't using TRUNCATE_ONLY, I used NO_TRUNCATE on the first backup of each backup pair. The idea is that a log backup is performed with NO_TRUNCATE, immediately followed by another normal log backup that does truncate the log. The problem is that if a new transaction is added during the backup process, and that transaction appears in the second backup (F: file), but not the first (D: file), then the log backups on D: could not be used by themselves to perform a restore, because not all of the transactions were recorded in those files.
The proper way to accomplish the goal is to perform one SQL Server backup, then make as many copies of those backup files as is required using operation system copy commands.
Thanks,
Mike
August 17, 2009 at 3:14 pm
Backup log yyy to disk = xxxx
Copy xxxx to zzzzz
THe Agent can instantiate a Command prompt batch file that does the copy. Powershell or VBScript can handle this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply