We help IT Professionals succeed at work.

SQL 2005 Express database backup plan

KANEWONG
KANEWONG asked
on
Hi;

I am using SQL 2005 Express and using SQLCMD utility to back up my database and transaction logs.  Here is my backup schedule..

Transaction Log backup starts at 12:30 am everyday, and run for every hour that means I have the log backup like at 12:30am, 1:30 am, 2:30 am,... 8:30pm, 9:30pm...

My full database backup starts at 9pm every night.

I want to know, if I restore my 9pm full backup and using the 9:30pm transactions log backup to restore it, so in this scenario, would 9:30pm transaction log includes those logs after 8:30pm?  And because my full backup is run at 9pm, then it should include all entries between 8:30pm to 9pm already, if the 9:30pm log restored, any duplicated record?

thanks!
Comment
Watch Question

David ToddSenior Database Administrator

Commented:
Hi,

I'm reasonably sure that duplicate records wont happen.

Heres how: The backup records something about the position in the log file as to where things are upto, and then when restoring the logs they 'replay' from that point on.

HTH
  David
Commented:
The way the log backups work is by keeping track of the LSN (Log Sequence Number).  The Full backup will contain the LSN of the last transaction that it has in it.  When you apply the 9:30 PM transaction log it will recognize that the Full backup already restored to a certain LSN and will start restoring it from that point on.

There will never be duplicated transactions restored.  Transaction Log backups will always containt LSN in sequence to be able to start or stop on an LSN as it needs to as directed by the user restoring the database backup.  SQL Server maintains transactional consistency for you and will not let you restore or miss transactions either.

Hope that helps.
David ToddSenior Database Administrator
Commented:
PS Try it and see what happens. You could also try restoring a transaction log backup twice and see that it fails the second time. Try restoring the log files out of order and see that the first out-of-sequence restore fails.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Nope, no dupes...

The FULL backup includes the transaction log, so it is like having a transaction log backup at that point anyway... Your next transaction log is from the full backup, not from the previous transaction log backup.

You start the restore process from the last good full backup and then apply the transaction logs. It will sort out the transactions for you (but in this case there is no real overlap).

It is a bit strange to have your transaction logs at a different time sequence - may as well make the 9:30pm instance the full backup...
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Just to clarify that last comment...

It is very good to see you do frequent transaction logs on a FULL recovery database. Normally the FULL backup would coincide with the time sequence. Is there any particlualr reason why the FULL backup is at 9:00pm and not 9:30pm ?

Author

Commented:
no particular why full backup at 9pm but the trans log at 9:30pm because the trans log backup runs every hour only.