Transaction Log Backups - the point-in-time is? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Transaction Log Backups - the point-in-time is?

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-06 : 06:27:06

Feeling a bit blonde - I am trying to find in BOL an explicit answer to this question:

If a transaction log backup takes 5 minutes to run, when it is restored, does it restore to the beginning of that period, or does it include transactions that occur during the 5 minutes?

EG:

12:30 - Backup Log starts
12:31 - tran 1 commits
12:31 - tran 2 starts and commits
12:35 - Backup Log completes

When I restore on a Log Shipped server, do I get as at 12:30 or 12:35?

TIA

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-06 : 06:32:56

Backups should contain the information up to the point when the backup completes.

-------
Moo. :)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-06 : 07:26:29

beginning of the period. the time it took for the backup to run will be covered by the next backup

quote:
Originally posted by Wanderer

Feeling a bit blonde - I am trying to find in BOL an explicit answer to this question:

If a transaction log backup takes 5 minutes to run, when it is restored, does it restore to the beginning of that period, or does it include transactions that occur during the 5 minutes?

EG:

12:30 - Backup Log starts
12:31 - tran 1 commits
12:31 - tran 2 starts and commits
12:35 - Backup Log completes

When I restore on a Log Shipped server, do I get as at 12:30 or 12:35?

TIA

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

--------------------
keeping it simple...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-06 : 07:29:29

Any idea where I can see that explicitly stated I BOL or some other "official" source - I need to respond to PM.

Alternatively, I need to test and prove, but am struggling for time ... the story of the It professional everywhere, eh?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-06 : 07:50:17

Open two windows in Query Analyzer. In one window enter the code to create a new table and add some rows, but don't run it yet. In the other window enter the code to do a log backup. Start the log backup, and immediately after run the code in the other window. Restore the log backup, and if you see the new table then you know it's working.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-06 : 08:00:24

quote:
I need to respond to PM

Regan you certainly moved up in the world working for the prime minister and all.

Alright its friday and I am a little wierd today.

Jim
Users <> Logic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-06 : 08:08:38

@Rob: I had tried that - guess I need to do more transactions - my log backups happen too fast.

@Jim:

quote:
Alright its friday and I am a little wierd today.

I beg to differ - on the evidence of 953 (and counting) posts, you're a little weird everyday, and soemtimes a LOTtle :)
Thanks Guys...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-06 : 08:54:08

Sure Pay the guy a complment on his promotion an get an insult.

Then again your right Weird is a default condition around here.

Jim
Users <> Logic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-09 : 03:55:11

schedule the backup and the transaction in a job perhaps with wait/delay of 1 second?

--------------------
keeping it simple...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-10 : 12:44:17

Just to confirm that I've tested this, and the answer is as suspected, i.e. the Log Backup is from the checkpoint at the BEGINNING of the log backup.

while doing a looped insert of 100000 rows into the table, I backed up the log, selection the max ID (identity column) and datetime from table before and after the log backup


Start of Script : 2005-05-10 18:20:24.977
NumberOfRows 
------------ 
100921

MaxTimeStamp                                           
------------------------------------------------------ 
2005-05-10 18:20:58.803

Processed 5464 pages for database 'RG_Scrap_Pad', file 'RG_Scrap_Pad_Log' on file 1.
BACKUP LOG successfully processed 5464 pages in 18.445 seconds (2.426 MB/sec).
NumberOfRows 
------------ 
102112

MaxTimeStamp                                           
------------------------------------------------------ 
2005-05-10 18:21:17.790

End of Script. Runtime was : 52876 MilliSeconds

and then applied it to another database, and the max's were:


NumberOfRows 

------------ 

100921

 

MaxTimeStamp                                           

------------------------------------------------------ 

2005-05-10 18:20:58.803

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources