Skip to main content

SQL Server - Transaction Log Backups.

Tail-Log Backups

This topic is relevant only for databases that are using the full or bulk-logged recovery models.

In most cases, under the full or bulk-logged recovery models, SQL Server 2005 and later versions require that you back up the tail of the log to capture the log records that have not yet been backed up. A log backup taken of the tail of the log just before a restore operation is called a tail-log backup.

SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

Not all restore scenarios require a tail-log backup. You do not have to have a tail-log backup if the recovery point is contained in an earlier log backup, or if you are moving or replacing (overwriting) the database and do not need to restore it to a point of time after the most recent backup. Also, if the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost. For more information, see "Restoring Without Using a Tail-Log Backup" later in this topic.

Backing Up the Tail of the Log
Like any log backup, a tail-log backup is taken by using the BACKUP LOG statement. We recommend that you take a tail-log backup in the following situations:

If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

Note
To avoid an error, the NORECOVERY option is necessary.

If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.


The following table summarizes these options.

BACKUP LOG option
Comments
NORECOVERY

Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup.

The log is truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified.

Important
We recommend that you avoid using NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERROR
Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database.
Note
When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. For more information, see "Tail-Log Backups with Incomplete Backup Metadata," later in this topic.

To create a transaction log backup when the database is damaged

How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL)

How to: Back Up the Tail of the Transaction Log (SQL Server Management Studio)

Tail-Log Backups That Have Incomplete Backup Metadata
Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.

If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1.

If the metadata in a tail-log backup is incomplete, the backupfilegroup table will be missing most of the information about filegroups at the time of the tail-log backup. Most of the backupfilegroup table columns are NULL; the only meaningful columns are as follows:

backup_set_id

filegroup_id

type

type_desc

is_readonly

Restoring Without Using a Tail-Log Backup
Restore scenarios in which a tail-log backup is unnecessary include the following:

Restoring a database to a point in time that is contained in a previous log backup.

A tail-log backup is unnecessary if you restore a database and specify the STOPAT, STOPATMARK, or STOPBEFOREMARK option in every RESTORE statement in your restore sequence.

To restore a database to an earlier point in time

To use Transact-SQL to restore to a specific point in time, see How to: Restore to a Point in Time (Transact-SQL), Recovering to a Marked Transaction, or Recovering to a Log Sequence Number (LSN).

To use SQL Server Management Studio, see How to: Restore to a Point in Time (SQL Server Management Studio) or How to: Restore a Database to a Marked Transaction (SQL Server Management Studio).

Restoring a copy of a database to a new location.

When you restore a database, you can use the same database name only if you restore the database to a different server instance, such as when you create a mirror database for database mirroring or a secondary database for log shipping. If you move a database on the same server instance, you must specify a new name for the database.

To restore a database to a new location

Using Transact-SQL, in every RESTORE statement in your restore sequence, specify the MOVE option. For more information, see How to: Restore a Database to a New Location and Name (Transact-SQL) or How to: Restore Files to a New Location (Transact-SQL).

Using SQL Server Management Studio, specify the new location of each file in the Restore As field on the Restore Database (Options Page). For more information, see How to: Restore a Database Backup (SQL Server Management Studio).

Completely replacing (overwriting) the database.

Caution
Restoring with the REPLACE option should be used rarely and only by experienced database administrators, after careful consideration. For more information, see Using the REPLACE Option.

To replace a database

Using Transact-SQL, specify the REPLACE option in your RESTORE statements.

Using SQL Server Management Studio, specify the new location of each file in the Restore As field on the Restore Database (Options Page). For more information, see How to: Restore a Database Backup (SQL Server Management Studio).


Labels:

Comments

Post a Comment

Popular posts from this blog

Self Introduction and Responsibilities of a SQL Server DBA.

Hi, Self Introduction:                      I am Vang chew bigger , currently I am working with   Citronics , Here my role is SQL Server Database Administrator , I am having 3+ yrs Exp in SQL SERVER DBA with T-SQL, I have experience on SQL Server 2000, 2005 and 2008, I have a great experience on installation and configuration of SQL SERVER versions, and also applying the patches, hot fix, services packs and RTM’s accordingly, involved in upgrading on in place and side by side as per client requirement, and also Migrating the database on SQL SERVER 2000 to 2005 and 2008, Solid experience on configuring and maintenance of High availability SQL Server solutions, including Log shipping, Database Mirroring, Replication(Transactional and Merge) and SQL Server Clustering., having experience on performance tuning on server level, database level and query level, Responsible for working with application developers in identifying,...
3 comments
Read more

Shrinking a SQL Server Log File with Database in Always ON Availability.

Shrinking a SQL Server Log File with Database in Always ON Availability.                    Once we had a situation in our production server that the database which is in Always ON Availability group has been grown to the maximum size.  Many of them say that we can't shrink the log file as the database is in Always ON and we should take the downtime to perform the operation. But we shrink-ed the database log file to 5 GB from 180 GB. Steps are as below: --To Check the Number of pages allocated by the LogFile. DBCC LOGINFO --To check the file sixe sp_helpdb [SQLPantry] --To check the Log file Size DBCC SQLPERF(LOGSPACE) The above statement will show the logspace of all db at present situation. --To check the Log_reuse_wait description of a log file. select log_reuse_wait_desc,* from sys.databases The above statement will show the status of the log whether it has to be backuped i.e., LogBackup, Nothing, Ac...
1 comment
Read more

SQL Server 2022 Features in Detail.

SQL Server 2022 Features in Detail. 1. Data Virtualization: Data virtualization in SQL Server 2022 enables you to access and query data from various sources as if they were part of a single database. This eliminates the need for complex data integration processes and allows for seamless querying of distributed data. 2. Machine Learning Services Enhancements: SQL Server 2022 offers enhanced integration with machine learning frameworks like TensorFlow and PyTorch. This enables you to train and deploy machine learning models directly within the database, bringing AI-driven insights closer to the data. 3. Improved Security: The Data Discovery and Classification capabilities in SQL Server 2022 help identify sensitive data and apply appropriate security measures. Always Encrypted technology ensures that sensitive data remains encrypted in various scenarios. 4. Big Data Clusters: Big data clusters allow you to manage and query large datasets across SQL Server, Hadoop, and Spark clusters. This...
Post a Comment
Read more