Solve Common SQL Server Restore Issues

By: Sergey Gigoyan | Comments (4) | Related: > Restore

Problem

Database backups and restores have many options in SQL Server. There are standard steps to implement backup and restore solutions, however in some situations additional knowledge is needed. Thus, understanding backup and restore processes is very essential for developers and DBAs to be more confident when dealing with uncommon situations and having the ability to suggest flexible solutions related to these processes.

In this tip we will discuss some questions related to backup and restore that any DBA or database developer will come across during his/her professional experience.

Solution

We will explain and illustrate some questions related to backup and restore with examples.

Do Full or Differential backups break the transaction log chain?

Let's suppose that we have a database named TestDB and we need to restore it. We have been provided with the last Full backup, the last Differential backup which was taken after the Full backup and several Transaction log backups. Standard restore steps are as follows:

First restore the Full backup 'WITH NORECOVERY'
Then restore the Differential backup, also 'WITH NORECOVERY'
After that restore each Transaction log backup after the Differential backup from oldest to newest using the 'WITH NORECOVERY' option, except the last one which should be restored 'WITH RECOVERY'.

Let's consider a situation where the Differential backup is missing, but we have the Full backup and all Transaction log backups taken after the Full backup. Can we restore the database up to the time the last transaction log backup was taken or does the missing Differential backup break the Transaction log chain?

Let's illustrate this case with an example. We are creating a database, set recovery model to FULL to guarantee that transaction log backups can be taken and create a table.

--Creating the database
CREATE DATABASE TestDB
GO

--Setting recovery model to Full
ALTER DATABASE TestDB SET RECOVERY FULL

USE TestDB
GO

--Creating the TestTable
CREATE TABLE TestTable
(
	ID INT
)

Now we are backing up this database and making data changes between backups.

USE TestDB
GO

--Backing up the database
BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBFull_1.bak';

   INSERT INTO TestTable (ID) VALUES (1);

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_1.trn';

   INSERT INTO TestTable (ID) VALUES (1);

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_2.trn';

   INSERT INTO TestTable (ID) VALUES (1);

BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBDiff_1.bak' WITH DIFFERENTIAL;

   INSERT INTO TestTable (ID) VALUES (1);

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_3.trn';

   INSERT INTO TestTable (ID) VALUES (1);

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_4.trn';

Let's restore the database in a new environment using Full, Differential and Transaction log backups (taken after Differential backup):

--Restoring the full backup
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBFull_1.bak'
WITH NORECOVERY, REPLACE

--Restoring the differential backup
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBDiff_1.bak'
WITH NORECOVERY

--Restoring log backups after differential backup
RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_3.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_4.trn'
WITH RECOVERY

As we can see below, the database was successfully restored as expected:

Now let's return to our question. Can we restore the Full backup and all Transaction log backups taken after the full backup and exclude the differential backup?

--Restoring the full backup
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBFull_1.bak'
WITH NORECOVERY, REPLACE

--Restoring log backups
RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_1.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_2.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_3.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_4.trn'
WITH RECOVERY

We can see, that database is restored successfully without using the Differential backup:

This means that the Differential backup does not break the Transaction log chain and we can restore the database using only the Full and Transaction log backups, regardless if there were Differential backups.

In addition, Full backups also don't break the Transaction log chain. Consider the code below where we have two Full backups.

USE TestDB
GO

--Backing up the database
BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBFull_1.bak'

   INSERT INTO TestTable (ID) VALUES (1)

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_1.trn'

   INSERT INTO TestTable (ID) VALUES (1)

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_2.trn'

   INSERT INTO TestTable (ID) VALUES (1)

BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBFull_2.bak' -- 2nd full backup

   INSERT INTO TestTable (ID) VALUES (1)

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_3.trn'

   INSERT INTO TestTable (ID) VALUES (1)

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_4.trn'

If we run the following restores using the first full backup and all transaction log backups, the database restores without issue.

--Restoring the full backup
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBFull_1.bak'
WITH NORECOVERY, REPLACE

--Restoring log backups
RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_1.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_2.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_3.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_4.trn'
WITH RECOVERY

Or we can restore the database this way using the second full backup and the transaction log backups that were taken after the second full backup.

--Restoring the full backup
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBFull_2.bak'
WITH NORECOVERY, REPLACE

--Restoring log backups
RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_3.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_4.trn'
WITH RECOVERY

Can we use Differential or Transaction log backups with COPY_ONLY Full backups to restore the database?

COPY_ONLY Full backups might be required when we need to take a backup without affecting the restore sequence of the backups. But can we restore the database using the COPY_ONLY Full backup along with the Differential and Transaction log backups? Let's try it as shown below where we created a COPY_ONLY Full backup and then created a Differential backup and then a Transaction log backup.

USE TestDB
GO

--Copy_only backup
BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBCopyOnly_1.bak' WITH COPY_ONLY

--Differential backup
BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBDiff_1.bak' WITH DIFFERENTIAL

--Transaction log backups
BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_1.trn'

Now we want to restore the database using these backups.

--Using COPY_ONLY full and differential backup to restore the database
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBCopyOnly_1.bak'
WITH NORECOVERY, REPLACE

RESTORE DATABASE TestDB 
FROM DISK = 'E:\Backup\TestDBDiff_1.bak'
WITH RECOVERY

We can see that COPY_ONLY Full backup has been successfully restored, however the differential backup cannot be restored in this case:

After we get the above error, the TestDB database is in restoring state. We can still restore the transaction log backup as shown below to bring the database back online.

--Restoring log backup
RESTORE LOG TestDB
FFROM DISK = 'E:\Backup\TestDBLog_1.trn'
WITH RECOVERY

Overall we can say that Transaction log backups taken after a COPY_ONLY Full backup can be used with the COPY_ONLY backup to restore the database, but it is not possible to use differential backups with a COPY_ONLY Full backup for restoring.

What is the use of backup on 'NUL' disk and how can it be dangerous?

Once the Transaction log is backed up, the transaction log file is truncated and the log file space is released which can be reused (except for some cases such as databases participating in mirroring, replication, etc.). Sometimes DBAs can face a situation where the transaction log file becomes full and its size needs to be reduced. To solve this problem, we can shrink log file after taking a Transaction log backup. Another option would be to change the database recovery model to Simple, truncate the log and then changing it to Full again.

However in some situations there might not be free space on the disk to take a log backup if the transaction log grew out of control or you are unable to change the recovery model for various reasons. An option some DBAs take is to back up the transaction log to an non-existent disk drive such as 'NUL'. In this case the transaction log will be truncated, however the Transaction log backup will not be saved anywhere, because 'NUL' is does not exist.

This process looks like a transaction log backup was taken and immediately deleted. This is a risky, because it breaks the log chain, in other words we cannot restore the next log backup because the 'NUL' backup does not exist. In the following example we can see this.

USE master
GO

BACKUP DATABASE TestDB TO DISK = 'E:\Backup\TestDBFull_3.bak'

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_31.trn'

--Transaction log backup to 'NUL' disk
BACKUP LOG TestDB TO DISK = 'NUL'

BACKUP LOG TestDB TO DISK = 'E:\Backup\TestDBLog_32.trn'

Now let's restore the database using these backup files.

--Restoring the full backup
RESTORE DATABASE TestDB
FROM DISK = 'E:\Backup\TestDBFull_3.bak'
WITH NORECOVERY, REPLACE

--Restoring log backups
RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_31.trn'
WITH NORECOVERY

RESTORE LOG TestDB
FROM DISK = 'E:\Backup\TestDBLog_32.trn'
WIWITH RECOVERY

The Full and first transaction log backups restored, but the TestDBLog_32.trn backup failed because there was another Transaction log backup taken between TestDBLog_31.trn and TestDBLog_32.trn.

So, it is recommended to avoid doing backups to 'NUL' disk to release space in the transaction log file. However, if there is no other way, it is very important to know that the backup to 'NUL' breaks the Transaction Log Chain and a full backup should be taken right after this operation completes.

Conclusion

Generally backup and restore is one of the most common tasks that DBAs face. However it can be complex and it is essential to understand the process details and be very careful when dealing with non-standard cases to avoid making mistakes.

Next Steps

Check out these MSSQLTips.com resources:
More info on backup and restore

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Friday, December 18, 2015 - 5:59:59 AM - Thomas Franz	Back To Top (40274)
Some people use a backup to NUL for speed testings (if you want to know how fast data can be read). But I suggest to do this only on a copy of the prod db (nobody cares about the performance of your test server) and / or to use COPY_ONLY and to disable compression (because of CPU).