Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Solve Common SQL Server Restore Issues


By:   |   Last Updated: 2015-12-04   |   Comments (4)   |   Related Tips: More > 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:

the database was sucecessfully restored

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:

restored successfully

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:

COPY_ONLY

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	

RESTORE LOG TestDB

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.

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


Last Updated: 2015-12-04


next webcast button


next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 31, 2015 - 1:22:57 PM - Sergey Gigoyan Back To Top

Sasi,

No, log backup is not stored  in the memory, so it does not cause the problem.

Transaction log backup usually truncates the transaction log file, however there are some exceptions. You can find these exceptions in the following artcle - www.mssqltips.com/sqlservertip/4118/sql-server-backups-and-transaction-log-questions/ .

Thanks


Friday, December 18, 2015 - 10:05:38 AM - sasi Back To Top

 "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."

So does it take the log backup in the buffer? In that case if the tx log is as big as 32GB and memory on the server is set to 32GB would that cause problems?

Also does the log gets truncated automatically in every scenario?Like even if TX replication or DB mirroring is set up?

 


Friday, December 18, 2015 - 5:59:59 AM - Thomas Franz Back To Top

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).


Thursday, December 17, 2015 - 6:44:56 AM - Thierry Back To Top

Thx for sharing. One comment when you say " know that the backup to 'NUL' breaks the Transaction Log Chain and a full backup should be taken right after this operation completes"

No need to take a full, a diff is OK too

 


Learn more about SQL Server tools