SQL Server Backup transaction log end (Tail)

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

?

The end of the transaction log often commits transaction log content that is not backed up by the database. Basically, every time you perform a transaction log backup, you are performing a backup at the end of the transaction log.

then why is it so designed? Because maybe because of the media corruption, when the database is no longer available, the trouble comes. If the next logical step is just to back up the current transaction log, you can apply this backup to keep the database in a wait (Standby) state. You can even use no_truncate to back up the transaction log in a state where the database file is not available , for example:

BACKUP LOG AdventureWorks to DISK = ' G:/backups/adventureworks_log_tail.bak ' withno_truncate

you can then use the results of the backup log to put the data in the waiting state " Bring back " to the state before the error occurred.

Note: This is another reason why you put the transaction log file on a different disk than the data file. If they are on the same disk, and the disk is damaged, you will not be able to take a backup of the transaction log from the disk.

Another problem is that when your data is using the bulk-logged recovery model, the current transaction log includes only the minimized log transactions. In this case, a transaction log backup needs to store the modification of the data page. If your data file is not available, you cannot back up the transaction, even with the no_truncate option.

Finally, if you are using a SQL Server 2005 or later, each time you are recovering a database that is already in existence, and is a full or bulk log recovery model, and the transaction log includes the active transaction, you receive an error message similar to the following:

server:msg 3159, Level, State 1, line 1?
The tail of the log for the database "AdventureWorks" have not been backed up. Use of backup LOG with NORECOVERY to backup the log if it contains work for you does not want to lose. Use the and REPLACE or with STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Server:msg 3013, Level A, State 1, line 1?
RESTORE DATABASE is terminating abnormally.

The information above is SQL Server you are told that some log records in the transaction log have not been backed up. If the current transaction log can be discarded, you can use the REPLACE option to have SQL Server ignore the current transaction log. as follows:

RESTORE DATABASE AdventureWorks from DISK = ' G:/backups/adventureworks_full.bak ' with? REPLACE

??

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

SQL Server Backup transaction log end (Tail)