SQL Server Backups and Transaction Log Questions
By: Sergey Gigoyan | Updated: 2015-12-18 | Comments (2) | Related: More > Backup
In this tip we will continue to investigate some questions related to SQL Server backup and restore. In the previous tip (Solve Common SQL Server Restore Issues) we discussed questions about the transaction log chain, restoring from COPY_ONLY backups and backup to 'NUL' disk. Now we will address questions on how to run Full and Transaction log backups in parallel, will illustrate the difference between striped and mirrored backups, and discuss issues related to truncation of the transaction log file after a transaction log backup.
Now let's formulate each of these questions and explain them in detail.
Can we start a SQL Server transaction log backup when a Full or Differential backup is running?
Let's understand this with an example. Suppose, we have the TestDB database (make sure that your TestDB database is only used for testing purposes, to avoid damaging important data during testing process). Now we'll open two query windows and run the full backup and transaction log backups in parallel. First we run the full backup in the first query window with the following code:
--Query 1 --Tested on SQL Server 2014 USE master GO PRINT 'Full Backup start time:' PRINT (CONVERT( VARCHAR(24), GETDATE(), 121)) --Backing up the database BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBFull.bak' PRINT 'Full Backup completion time:' PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
And after that, while the full database backup is in progress, we will start the transaction log backup in the second window:
--Query 2 --Tested on SQL Server 2014 USE master GO PRINT 'Log Backup start time:' PRINT (CONVERT( VARCHAR(24), GETDATE(), 121)) --Backing up the transaction log BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog.trn' PRINT 'Log Backup completion time:' PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
As we see from the results in the screen shot below the transaction log backup started after the beginning of full backup, and finished before the full backup finished:
So, it is possible to run a transaction log backup while the full backup is running. Also, when a differential backup is running we can start a transaction log backup. This is useful when our database full or differential backup requires a long time to complete and we want to minimize data loss in case of database file corruption during the full\differential backup process.
What happens when we specify more than one disk in the SQL Server backup command?
We can specify more than one disk when we backup our SQL Server database using T-SQL commands or SQL Server Management Studio. Here is some sample T-SQL code:
USE master GO BACKUP DATABASE TestDB TO DISK = N'C:\Backup\TestDB1.bak', DISK = N'E:\Backup\TestDB2.bak' GO
The screen shot below shows how to create multiple backup files on different disks in SQL Server Management Studio by using the "Add..." button.
It is important to understand that with this technique we are striping database backup files to different paths and not mirroring the database backup. Each of these files are part of the whole backup. All these files are needed when we perform the database restore. If we try to restore the database using only one of the files we will receive the following error as shown in the screen shot below:
USE master GO RESTORE DATABASE TestDB FROM DISK = N'C:\Backup\TestDB1.bak' WITH REPLACE GO
When we use all of the striped backup files for the restore, this allows us to successfully restore the database as shown below:
USE master GO RESTORE DATABASE TestDB FROM DISK = N'C:\Backup\TestDB1.bak', DISK =N'E:\Backup\TestDB2.bak' WITH REPLACE GO
Mirroring SQL Server Backups
Mirroring SQL Server backup files to different locations is also possible. In this case we must specify the 'MIRROR TO' clause:
USE master GO BACKUP DATABASE TestDB TO DISK = N'C:\Backup\TestDB1.bak' MIRROR TO DISK = N'E:\Backup\TestDB2.bak' WITH FORMAT, INIT GO
We can use each of these backups to restore the database:
USE master GO RESTORE DATABASE TestDB FROM DISK = N'E:\Backup\TestDB2.bak' WITH REPLACE GO
Mirroring SQL Server Striped Backups
It is also possible to mirror striped backups:
USE master GO BACKUP DATABASE TestDB TO DISK = N'C:\Backup\TestDB1.bak', DISK = N'C:\Backup\TestDB2.bak' MIRROR TO DISK = N'E:\Backup\TestDB3.bak', DISK = N'E:\Backup\TestDB4.bak' WITH FORMAT GO
In this case we can restore the database either using the first group of backup files or the second. Below we are restoring the database using the first two striped backup files:
USE master GO RESTORE DATABASE TestDB FROM DISK = N'C:\Backup\TestDB1.bak', DISK =N'C:\Backup\TestDB2.bak' WITH REPLACE GO
This also works using the second set:
USE master GO RESTORE DATABASE TestDB FROM DISK = N'E:\Backup\TestDB3.bak', DISK =N'E:\Backup\TestDB4.bak' WITH REPLACE GO
These examples are essential to not confuse striped and mirrored SQL Server backups.
Is it true that after a SQL Server transaction log backup the transaction log file is truncated immediately?
No, not always. There are some instances which can delay the SQL Server transaction log file truncation. After a transaction log backup (if the log backed up normally, without COPY_ONLY) SQL Server usually truncates the transaction log file, which means that the inactive portion of log file can be reused not that any disk space is freed. However, there are some cases which prevent SQL Server from truncating the log file after a log backup. In the following cases the transaction log file cannot be truncated even after the log backup:
- When a data backup operation is running. During the data backup operation, the transaction log is needed, so it cannot be truncated.
- When long-running active transactions exist.
- When the database participates in mirroring, each transaction log must remain active until it is written to the mirrored server's disk. So, in this case the transaction log cannot be truncated.
- When database acts as a publisher in transactional replication, and all transactions related to publications, have not been delivered to the distribution database yet.
- Check out these resources:
Last Updated: 2015-12-18
About the author
View all my tips