| |
 |
If you
run a database in the full or bulk-logged recovery models, you know
that you need to periodically perform transaction log backups in
order to back up your transactions, keep the transaction log
file(s) to a manageable size, and basically keep your job. When the
time comes to restore the transaction logs, you will need to
restore them in sequence, in the order they were created. You can
use the information stored in the msdb..backupset
table,
on the instance where the backups were created, to determine the
order in which to restore the files, using the FirstLSN
and
LastLSN
values
as guides.
As long as the sequence is maintained, you can restore to whichever
point you want to using the available logs. Unfortunately, there
are instances where the sequence is broken. The 2 most common
causes are when:
|
|
·
|
the
database's recovery model is switched to SIMPLE and back again to
either full or bulk-logged
|
|
|
·
|
a
BACKUP LOG command is ran with the TRUNCATE_ONLY/NO_LOG option
|
When this happens, you need to immediately take a full database
backup as a new starting point for new transaction log restores.
How would you know when the sequence is broken?
Things get a little tricky when you are using SQL Server 2000. If
the database recovery model was changed, or if the log was
truncated without a backup, SQL Server 2000 displays the following
output the first time you back up the transaction log after
the change:
There is no current database backup. This log backup
cannot be used to roll forward a preceding database backup.
Processed 1 pages for database 'logtest', file 'logtest_log' on
file 1.
BACKUP LOG successfully processed 1 pages in 0.078 seconds (0.019
MB/sec). |
Note that it's only a message. The backup will still complete
successfully, though it's unusable. Well, what if you don't see the
message because it's a scheduled job? Well, there's always the
Windows Event log, which warns you when the transaction log has
been truncated.
| | | | NOTE: If you are using SQL Server 2000 and log truncation is fatal
to your database, monitor the Windows Event log consistently for
log truncation events. | | | |
What if the recovey model was changed? Unfortunately, that isn't
recorded anywhere. Simply make it a practice to take a full
database backup after every change to the database recovery
model.
If you don't notice the messages, nor monitor the Windows Event
log, you're basically stuck with a bunch of unrecoverable
transaction log backups. Shouldn't SQL Server warn us? And stop
making useless backups? Yes, it should, and it does, if you are
using SQL Server 2005. This is the message that's displayed when
the log backup sequence has been broken.
Server: Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database
backup.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally. |
That's much better. So in summary, if you are using SQL Server
2000, you need to be on the alert for the above 2 mentioned events,
which can break your log backup sequence and leave you with useless
backups.
Here are some common operations that do not break the log
sequence:
|
|
·
|
changing
a database's recovery model from full to bulk-logged, or vice-versa
|
|
|
·
|
performing
a full database backup, differential backup or file/filegroup
backup
|
|
|
If you
have the following sequence of backups (where F represents a full
database backup, and T a transaction log backup):
|
|
|
F1,
T1, T2, F2, T3, T4, F3, T5, T6
|
|
|
Now
assuming you want to restore to a point in time in T6. Any of the
following restore sequences will get you there:
|
|
|
- F1,
T1, T2, T3, T4, T5, T6
|
Document history
| 6/27/2008 | Initial release. |
|
