SQL Server database transaction log store sequence

Original

Original: http://blog.csdn.net/tjvictor/article/details/5251351

?

If your database is running in full or bulk log recovery mode , you will need to use the job to back up the transaction log regularly, keeping your transaction file size in a manageable range. When you need to restore transaction logs, you need to restore them in the order in which they were created. You can refer to the existence of msdb: Backupset The information in the table to determine the order in which the files are restored, using the values of the firstlsn and lastlsn columns for reference. When you back up, the backup information will be present in the backupset table

As long as the sequence is maintained, you can use the corresponding log to restore the data to any recovery point. Unfortunately, the sequence of some instances has been corrupted. The following two cases are common causes of damage:

• The recovery model of the database was switched to a simple (Simple) Once again, it is switched back to full or bulk logs.
• Backup Log when the command runs, it comes with a Truncate_only/no_log options.

When the above two situations occur, you need to be able to make a full backup of the database as a new recovery point for transaction log recovery. So how do you tell if the sequence has been compromised?

in the SQL Server , this is really a bit of a hassle. If the database recovery model has been changed, or if the log has been truncated at the time of the backup, then when you first back up the transaction log,SQL Server Three displays the following output:

There is no current database backup. This log backup cannot is 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 this is only a message. Although the backup will still be completed successfully, it is not available. Because this is a scheduled job, you can't see anything, but when the transaction log is truncated, It is the relevant warning log in the Windows event log:

Note: If you are using SQL Server , log transactions are very important to the database, so the Windows An uninterrupted monitoring log transaction event in the event log.

If you're not looking at the warning message or monitoring Windows Event Log, then basically you have a batch of unrecoverable transaction log backups. should SQL Server not warn us? Should I stop an invalid backup? If you're using SQL Server 2005, the answer is definitely yes, and it does. Here is the message that appears when the log backup sequence is destroyed :

Server:msg 4214, Level A, State 1, line 1?
Backup LOG cannot be performed because there are no current database backup.
Server:msg 3013, Level A, State 1, line 1?
BACKUP LOG is terminating abnormally.

is not much better. In summary, if you are using SQL ServerTwo, you need to be concerned with the above mentioned warning events, which will destroy your log backup sequence and invalidate your backup.

Here are some common operations to ensure that log sequences are not corrupted:

• The database recovery model can be from full to bulk log, and vice versa
• perform a full database backup, but a differential backup is a file / File Group Backup

If you have a backup sequence like this (F stands for full data backup,T represents transaction log )

F1,t1,t2,t3,t4,t5,t6

now suppose you want to revert to T6 time points, such as the following recovery sequence, will help you achieve your goal:

F1,t1,t2,t3,t4,t5,t6

F2,t3,t4,t5,t6

F3,t5,t6

??

??

This article is translated from Sqlbackuprestore For more highlights, please visit http://www.sqlbackuprestore.com

SQL Server database transaction log store sequence