How to Restore Database with Tail Log Backup? Explanation and Demo of Backups/Restore and Recovery models in SQL Server
Backing up and restoring the tail-log end of a transaction log
But why we take backups in the first place? Disaster-Recovery is the first thing that comes to mind.We also use backups in Log Shipping,Database Mirroring,Replication. We do backups when we want to transfer our database from the development to the production server.Finally we take backups when moving or upgrading a database.
One could argue that we take backups when we want to manage the size of the transaction log.The only operation that clears the transaction log of a database that is in FULL or BULK logged recovery model is a transaction log backup. Some people are performing a backup before performing a database repair or an upgrade of any kind.
Before we proceed I would like to explain the various recovery models. In the Simple Recovery Model every time I have a checkpoint operation or the .l d f file is full, the .l d f file is automatically truncated.In this model the transaction log is not backed up.You can not have point in time data recovery with this model.Data is recoverable only to the most recent backup.
The Simple Recovery Model is only suitable for small databases and databases with few transactions.We could use this recovery model if we want to have a small transaction log and for databases that are read-only or in development stage.
You can't able to take Transaction Log Backup when Database have Recovery Model Simple.
kindly see below
If you Tried to take Transaction log backup Whose DB Have simple you should get below Error
The Full Recovery Model is the default recovery model.In this model the transaction log grows rapidly since all database transactions are logged in at a very detailed level.If I have a catastrophe in my database I can rollback to the time where the catastrophe occurred.This recovery model is suitable for databases with thousands of transactions per day and where no loss of data is tolerated.
The Bulk Recovery Model works almost identically with the Full Recovery Model.It logs everything in extents=8 pages and all bulk tasks (B C P,bulk insert) are logged minimally.We can go back to the time of failure but it will take more time to get the database back on-line.This model would be very suitable for databases with large bulk tasks.The disadvantage of this model is that you cannot go back at a specific point in time.
Having looked into the recovery models,let’s have a look on the various SQL Server backup types.
Let’s see what happens when we take a full database backup.The first thing to say is that you need always a full database backup.This is the base for all subsequent backups. You cannot have a differential backup,transaction log backup if you do not have at least one full backup.With a full database backup you backup all your data.You can have a full backup with all the recovery models that you choose to have for your database.Every full backup I perform in a database contains in it all the data previously fully backed up.
The backup operation is an online operation and the first thing it happens is to log that a backup was started. Then we write the data in the backup file.Then we do log any changes that occur as we backup.Finally the backing up of the data is finished and we log that fact.If you’re performing only full backups, you might lose some data in the event of a system crash—specifically, any changes made since the last full backup.
The backup operation is an online operation and the first thing it happens is to log that a backup was started. Then we write the data in the backup file.Then we do log any changes that occur as we backup.Finally the backing up of the data is finished and we log that fact.If you’re performing only full backups, you might lose some data in the event of a system crash—specifically, any changes made since the last full backup.
A differential backup is cumulative backup. It includes all data and structures that have changed since the last full backup, regardless of when that last full backup was made, or how many previous differential backups have been run.You can have a differential backup with all the recovery models.
The Transaction Log backup only works for full or bulk-logged recovery models.Each T-Log backup contains all the transactions that are in my database since the last T-Log backup.Always perform a full back up before you take a T-log backup.The time taken for a T-log backup is little and there is no performance penalty.An important thing to remember is that for every t-log back up I need the previous backup to have a restore.
I know a lot of people that are confused about the whole backup and restore process of a database. There are many issues that confuse people but the one I have found the most confusing for people is the use of Tail-log backup. Well, a tail-log backup is a transaction log backup that holds all the records that have not been backed up. So before we begin our restoration process,because we want to recover our SQL Server database to its latest point in time we must back up the tail of its transaction log.This will be the last back up.One thing that I want to immediately highlight is that you can not have a transaction log backup or tail-log backup unless the database is in either FULL or BULK-LOGGED recovery model.
How often you should back up a database depends on how long you have to restore it. In general, the more often you back up a database and the type of backup you take, the shorter the restore time.
You can tailor backups and restores for each database. The kind of backup you decide to use will depend on the size of the database and the amount of transaction activity.
You can tailor backups and restores for each database. The kind of backup you decide to use will depend on the size of the database and the amount of transaction activity.
Having only full backups is not a good strategy.If we have only one full database backup taken on 12 a.m every day for a database and our system crashes on 5 p.m, then we lose 5 hours of data.
If you can’t tolerate any data loss on restore, use the full plus log backup strategy.With this strategy we have a full database backup taken every day and then numerous T-Log backups at short periods of time. This method guarantees against data loss and works well for databases that are updated frequently. Although using this strategy increases your database’s complexity and maintenance, the total amount of time necessary to back up the database will decrease.So one might think that this is the appropriate restore strategy.At some point we will have thousands of files.It will take forever to restore thousands T-Log files.In order to have a good restore strategy for our average highly transaction database we need to include differential backups in our strategy. We could have for example 1 full backup every week, 7 differential backups every night and 336 (a week has 168 hours and I take 2 T-Log backups per hour) T-Log backups.
Demo:
If you have an instance of SQL Server running in your machine,simply connect to it.
In an new query window type
GO
CREATE DATABASE sample database
GO
ALTER DATABASE sample database SET RECOVERY FULL
USE sample database
GO
CREATE TABLE Sample Data
(
id INT PRIMARY KEY ,
col 1 tiny int NOT NULL ,
col 2 BIT NOT NULL ,
col 3 BIT NOT NULL ,
Registration Date DATE TIME NOT NULL
)
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94495, 25, 1, 1, '02-03-2008')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94496, 26, 1, 1, '02-04-2008')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94497, 27, 1, 1, '02-05-2008')
BACKUP DATABASE sample database TO DISK ='C:\s q l data\backups\sample data_full.b a k' WITH INIT,STATS;
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94498, 29, 1, 1, '02-03-2009')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94499, 28, 1, 1, '02-04-2009')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94500, 30, 1, 1, '02-05-2009')
SELECT * FROM sample data
BACKUP DATABASE sample database TO DISK = 'C:\s q l data\backups\sample data_diff.b a k' WITH DIFFERENTIAL
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94501, 29, 1, 1, '02-03-2010')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94502, 28, 1, 1, '02-04-2010')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94503, 30, 1, 1, '02-05-2010')
BACKUP LOG sample database TO DISK ='C:\s q l data\backups\sample data_Log 1.b a k' WITH INIT,STATS;
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94505, 29, 1, 1, '02-08-2010')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94506, 28, 1, 1, '02-09-2010')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94507, 30, 1, 1, '12-05-2010')
BACKUP LOG sample database TO DISK ='C:\s q l data\backups\sample data_Log 2. b a k' WITH INIT,STATS;
GO
INSERT [db o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94508, 29, 1, 1, '02-28-2011')
INSERT [ d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94510, 28, 1, 1, '02-27-2011')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (94511, 30, 1, 1, '12-12-2011')
BACKUP LOG sample database TO DISK ='C:\s q l data\backups\sample data_Log 3.b a k' WITH INIT,STATS;
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date])
VALUES (100000, 30, 1, 1, '12-02-2012')
SELECT TOP 5*
FROM sample data
ORDER BY id DESC
USE master
GO
ALTER DATABASE sample database SET OFFLINE WITH NO_WAIT;
--simulate the disaster by deleting the .m d f file
ALTER DATABASE sample database SET ONLINE
--this will result in a nasty error since there is no database file
BACKUP LOG sample database TO DISK ='C:\s q l data\backups\sample data_Tail Log.b a k' WITH INIT,NO_TRUNCATE;
GO
RESTORE DATABASE sample database FROM DISK='C:\s q l data\backups\sample data_full.b a k' WITH NO RECOVERY, REPLACE
RESTORE DATABASE sample database FROM DISK='C:\s q l data\backups\sample data_diff.b a k' WITH NO RECOVERY
RESTORE DATABASE sample database FROM DISK='C:\s q l data\backups\sample data_Log 1.b a k' WITH NO RECOVERY
RESTORE DATABASE sample database FROM DISK='C:\s q l data\backups\sample data_Log 2.b a k' WITH NO RECOVERY
RESTORE DATABASE sample database FROM DISK='C:\s q l data\backups\sample data_Log 3.b a k' WITH NO RECOVERY
RESTORE DATABASE sample database FROM DISK='C:\s q l data\backups\sample data_Tail Log.b a k' WITH NO RECOVERY
RESTORE DATABASE sample database WITH RECOVERY
USE sample database
GO
SELECT TOP 5*
FROM sample data
ORDER BY id DESC
Let me explain what I am doing in the T-SQL script above.
1) In lines 1-21 I create a sample database,set the recovery model to FULL and create a sample table in it.
2) In lines 23-30 I insert 3 records in the table.
3) In lines 32-33 I take a full database backup.
4) In lines 35-42 I insert 3 more records in the table.
5) In lines 46-47 I take a differential backup.
6) In lines 49-56 I insert 3 more records in the table.
7) In lines 58-59 I take another transaction log backup.
8) In lines 61-68 I insert 3 more records in the table.
9) In lines 70-71 I take another trans action a log backup.
10) In lines 73-80 I insert 3 more records in the table.
11) In lines 82-83 I take another transaction log backup.
12) In lines 85-86 I insert a new record in the table.
13) In lines 88-90 Ι perform a simple “Select” to see the newly inserted record.
14) In lines 92-95 I take the database offline.Then I go to the local path and delete the .m d f file.In my case the path is C:\Program Files\Microsoft SQL Server\M S SQL 11.SQL EXPRESS\M S S Q L\DATA. As you can understand I am simulating a disaster.
15) In line 99 I try to take the database online but I receive a nasty error as expected.
16) In lines 103-104 I take a tail-log backup.This is the first step in the restore process.
17) In lines 106-117 I restore with the correct sequence (full,differential,3 t-log backups,final tail-log backup). Everything works fine.
18) In lines 122-124, I am looking for the last record (id=100.000) that was inserted after the last t-log backup but was picked up from the tail-log backup. So despite the catastrophe I had, my database is back online and there is no data loss.
Please note that not everyone has the permission to backup a database.This permission is limited by default to members of the sysadmin fixed server role,members of the db_owner and db_backup operator fixed database role.
For More Ref:
h t t p s://dot net stories. word press.com/2012/07/05/backing-up-and-restoring-the-tail-log-end-of-a-transaction-log/
Different Ways to Restore a SQL Server Database
The diagram below will be used to explain the solution. It is a simple visual, but I find that in many cases it is an effective method to visualize and describe SQL Server database restores.
.............................You can do it like this as see Before FULL BACKUP see below
so you can see final step of Transaction log backup see below
This is the entire Script:
USE master
GO
CREATE DATABASE [sample db]
GO
Whenever you created new db it was always saved in Simple Recovery model it is default.
Select Name [DB Name],Recovery_model_desc,* from s y s. data bases
where name=' [sample db]'
So you have to change it to FULL Recovery Model as see below.
ALTER DATABASE sample db SET RECOVERY FULL
USE sample db
GO
CREATE TABLE Sample Data
(
id INT PRIMARY KEY ,
col 1 tiny int NOT NULL ,
col 2 BIT NOT NULL ,
col 3 BIT NOT NULL ,
Registration Date DATE TIME NOT NULL,
Description N var char(100)
)
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94105, 24, 1, 1, '02-03-2008','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94106, 48, 1, 1, '02-05-2008','FULL_B K 1_BEFORE')
SELECT * FROM sample data
BACKUP DATABASE sample db TO DISK ='F:\sample db_full B K 1. b a k' WITH INIT,STATS;
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94108, 29, 1, 1, '02-03-2009','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94122, 30, 1, 1, '02-05-2009','T LOG_B K 1_BEFORE')
SELECT * FROM sample data
BACKUP LOG sample db TO DISK ='F:\sample db_Log BK 1. b a k' WITH INIT,STATS;
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94196, 26, 1, 1, '02-04-2008','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94199, 66, 1, 1, '02-04-2009','T LOG_BK 2_BEFORE')
SELECT * FROM sample data
BACKUP LOG sample db TO DISK ='F:\sample data_Log BK 2.b a k' WITH INIT,STATS;
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94201, 24, 1, 1, '02-03-2010','Test')
INSERT [db o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94202, 98, 1, 1, '02-04-2010','DIFF_BK 1_BEFORE')
BACKUP DATABASE sample db TO DISK = 'F:\sample data_diff 1.b a k' WITH DIFFERENTIAL,STATS
GO
SELECT * FROM sample data
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94220, 12, 1, 1, '02-05-2009','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94223, 12, 1, 1, '02-05-2010','T LOG_BK 3_BEFORE')
BACKUP LOG sample db TO DISK ='F:\sample data_Log B K 3. b a k' WITH INIT,STATS;
GO
INSERT [d b o ].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94305, 19, 1, 1, '02-08-2010','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94306, 23, 1, 1, '02-09-2010','T LOG_BK 4_BEFORE')
BACKUP LOG sample db TO DISK ='F:\sample data_Log BK 4.b a k' WITH INIT,STATS;
GO
GO
SELECT * FROM sample data
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2 ], [col 3], [Registration Date],Description)
VALUES (94312, 11, 1, 1, '12-05-2010','Test')
INSERT [d b o].[Sample Data] ([id],[col 1 ], [col 2], [col 3], [Registration Date],Description)
VALUES (94313, 11, 1, 1, '10-05-2010','DIFF_BK 2_BEFORE')
BACKUP DATABASE sample db TO DISK = 'F:\sample data_diff BK 2.b a k' WITH DIFFERENTIAL,STATS
GO
GO
SELECT * FROM sample data
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94408, 12, 1, 1, '02-28-2011','Test')
INSERT [ d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94410, 23, 1, 1, '02-27-2011','T LOG_BK 5_BEFORE')
BACKUP LOG sample db TO DISK ='F:\sample data_Log BK 5.b a k' WITH INIT,STATS;
GO
SELECT * FROM sample data
INSERT [ db o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94508, 67, 1, 1, '12-12-2011','FULL_BK 2_BEFORE')
SELECT * FROM sample data
BACKUP DATABASE sample db TO DISK ='F:\sample data_full B K 2.b a k' WITH INIT,STATS;
GO
INSERT [ d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94511, 46, 1, 1, '12-12-2011','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94512, 30, 1, 1, '12-12-2011','T LOG_BK 6_BEFORE')
BACKUP LOG sample db TO DISK ='F:\sample data_Log 6.b a k' WITH INIT,STATS;
GO
GO
SELECT * FROM sample data
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94558, 85, 1, 1, '02-28-2011','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (94560, 74, 1, 1, '02-27-2011','T LOG_BK 7_BEFORE')
BACKUP LOG sample db TO DISK ='F:\sample data_Log 7.b a k' WITH INIT,STATS;
GO
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (99999, 74, 9, 9, '09-09-2012','Test')
INSERT [d b o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (100000, 89, 9, 9, '12-12-2012','DIFF_B K 3 _BEFORE')
BACKUP DATABASE sample db TO DISK = 'F:\sample data_diff 3.b a k' WITH DIFFERENTIAL,STATS
GO
INSERT [db o].[Sample Data] ([id],[col 1], [col 2], [col 3], [Registration Date],Description)
VALUES (100001, 1, 1, 1, '12-12-2012','T LOG_BK 8_BEFORE')
SELECT * FROM sample data
BACKUP LOG sample db TO DISK ='F:\sample data_Log 8.b a k' WITH INIT,STATS;
GO
SELECT TOP 5*
FROM sample data
ORDER BY id DESC
Once Again find out Backup Strategies see below
In the diagram, I have 3 typical types of SQL Server backups
- full database backup denoted by F"x"
- differential database backup denoted by D"x" and
- transaction log backup denoted by T"x".
The "x" represents an in c r e men ting number that corresponds to a point-in-time when the specific backup type is taken. The character F, D and T denotes the type of backup taken. The point-in-time (P) denotes the point-in-time a backup is taken.
For example, F 1 refers to a full database backup taken at a point-in-time P 1. Some time later, another full backup F 2 is taken at point-in-time P 9. Similarly T 1 refers to a transaction log backup taken at point-in-time P 2 which happens after the full database backup F 1, then a second transaction log backup T 2 is taken. Subsequently a differential database backup D 1 occurred at point-in-time P 4 and so on.
Point-in-time P 13 is a visual indicator for a committed transaction that has occurred, but a transaction log backup was not taken until point-in-time P 14. This will be used in example 3 which will describe a technique to recover the database to point-in-time P 13.
Below are 3 examples of common database restore scenarios to learn the SQL Server restore behavior.
Example 1 - restore to point in time P 8
Recovery path options to restore the database backup to point-in-time P 8
Option 1: F 1 > D 2 > T 5
Option 2: F 1 > D 1 > T 3 > T 4 > T 5
Option 3: F 1 > T 1 > T 2 > T 3 > T 4 > T 5
Option 2: F 1 > D 1 > T 3 > T 4 > T 5
Option 3: F 1 > T 1 > T 2 > T 3 > T 4 > T 5
In this example, the fastest database recovery path to point-in-time P 8 would be Option 1.
Differential backups are cumulative (this means that any differential backup after the last full backup contains all of the changes) and therefore only one can be restored after the base full backup has been restored. Hence, option F 1 > D 1 > D 2 > T 5 is not required nor supported.
Example 2 - restore to point in time P 10
Recovery path options to restore a database backup to point-in-time P 10
Option 1: F 2 > T 6
Option 2: F 1 > D 2 > T 5 > T 6
Option 3: F 1 > D 1 > T 3 > T 4 > T 5 > T 6
Option 4: F 1 > T 1 > T 2 > T 3 > T 4 > T 5 > T 6
Option 2: F 1 > D 2 > T 5 > T 6
Option 3: F 1 > D 1 > T 3 > T 4 > T 5 > T 6
Option 4: F 1 > T 1 > T 2 > T 3 > T 4 > T 5 > T 6
The fastest database recovery path to point-in-time P 10 would be Option 1.
For whatever reason, if full database backup F 2 is missing, it is still possible to restore from full backup F 1, with a combination of differential and transaction log backups to get to P 10. A database full backup does not break the transaction log backup chain, which means transactions logs can be restored for earlier full backups that have occurred.
DEMO
For Example Full Backup File 2 has corrupted/Deleted so if it was not available then we can follow below scenario to get that as see below
-- Restore path F 1 > D 2 > T 5 > T 6 > T 7
RESTORE DATABASE [sample db] FROM DISK = N'F:\sample data_full BK. b a k' WITH FILE = 1,
NO RECOVERY, NO UNLOAD, REPLACE, STATS = 5-----------------------------Full Backup 1
GO
RESTORE DATABASE [sample db] FROM DISK = N'F:\sample data_diff 2.b a k' WITH FILE = 1,
NO RECOVERY, NO UNLOAD, STATS = 5----------------------------Diff Backup 2
GO
RESTORE DATABASE [sample db] FROM DISK = N'F:\sample data_Log 6.b a k' WITH FILE = 1,
NO RECOVERY, NO UNLOAD, STATS = 5---------------------------Transaction Log Backup 6
GO
RESTORE DATABASE [sample db] FROM DISK = N'F:\sample data_Log 7.b a k' WITH FILE = 1,
NO RECOVERY, NO UNLOAD, STATS = 5------------------Transaction Log Backup 6
GO
Above scenario you can restore Full Backup and D iffrential log backup but you will get error if you miss any t log backup kindly see below you were missed Tl og backup 5 because you restore Tlog backup 6 only after restored Diff backup 2 with full backup
So you need to restore T log backup 5 and T log backup 6 and t log backup 7 respectively see below
RESTORE DATABASE sample db FROM DISK = N'F:\sample data_Log BK 5.b a k' WITH FILE = 1,
NO RECOVERY, NO UNLOAD, STATS = 5
Go
Now you can able to restore without any issue after you restored Diff Backup 2
see below more details
RESTORE DATABASE sample db with Recovery
SELECT TOP 5*
FROM sample db.d b o. sample data
ORDER BY id DESC
That s it. we are restored in point in time with T log backup 7 data.
Note:
Some of error while you will face as see below
Causes:
Backup file will be different. I mean if you take 3 more backup file but not giving in correct file in restore command.
Solution;
1)Kindly check correct db backup file or db name in restore command
2) or use Restore db with replace command
RESTORE DATABASE db nameFROM DISK = 'C:\Backup Adventure works.b a k'WITH REPLACE----------------------------see here
for more info ref see below
h t t p://blog.s q l authority.com/2007/09/27/s q l-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/
b) This differential Backup cannot be restored Because the database has not been restore to the correct earlier state
Causes:
if you restore Full backup with Diff Backup 2 instead of Diff Backup 1 you will get this error when you restore Tlog backup 5 after that
like this
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 1--------------went fine
Restore db T log backup 5-------------throws error below b cos you have to go forward restore not Backward
like this
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 1--------------went fine
Restore db T log backup 5-------------throws error below b cos you have to go forward restore not Backward
correct method is
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 2--------------went fine
Restore db T log backup 5-------------went fine
Restore db T log backup 6-------------went fine
Restore db T log backup 7-------------went fine
B cos DIFF BACKUP 2 Before T log backup 3 & T log backup 4 is available so you can't skip those restore files but you can possible in t log skip using diff backup like this
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 1--------------went fine
Restore db Diff Backup 2--------------went fine
Restore db FULL BACK UP 2---------went fine
Restore db Diff Backup 3--------------went fine
Solution:
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 2--------------went fine
Restore db T log backup 5-------------went fine
Restore db T log backup 6-------------went fine
Restore db T log backup 7-------------went fine
B cos DIFF BACKUP 2 Before T log backup 3 & T log backup 4 is available so you can't skip those restore files but you can possible in t log skip using diff backup like this
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 1--------------went fine
Restore db Diff Backup 2--------------went fine
Restore db FULL BACK UP 2---------went fine
Restore db Diff Backup 3--------------went fine
you have to take full backup with diff Backup 2 then t log 5,t log 6,t log 7 then you will solve this issue.
or
Alter solution:
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 1--------------went fine
Restore db Diff Backup 2--------------went fine
Restore db T log backup 5-------------went fine
Restore db T log backup 6-------------went fine
Restore db T log backup 7-------------went fine
or
Alter solution:
Restore db FULL BACK UP 1---------went fine
Restore db Diff Backup 1--------------went fine
Restore db Diff Backup 2--------------went fine
Restore db T log backup 5-------------went fine
Restore db T log backup 6-------------went fine
Restore db T log backup 7-------------went fine
c)
Last but not least
consider this scenario
you will get following error while you decided to restore like this to restore in point time p 14 or up to
T log backup 8
M s g 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
M s g 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Because Whenever Full Backup taken you have to include that full backup restore those files to solve this issue or do some alter method like see below
to solve above error:
Error happened method
Correct method:
Last but not least
consider this scenario
you will get following error while you decided to restore like this to restore in point time p 14 or up to
T log backup 8
-- Restore path F 1 > D 1 > D 2 > D 3 > T 8-----------------Error
M s g 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
M s g 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Because Whenever Full Backup taken you have to include that full backup restore those files to solve this issue or do some alter method like see below
to solve above error:
Error happened method
-- Restore path F 1 > D 1 > D 2 > D 3 > T 8-----Error happening method due to avoid full backup 2
Correct method:
-- Restore path F 1 > D 1 > D 2 > F 2 > D 3 > T 8-------------included full backup 2
Alter method
-- Restore path F 1 > D 1 > D 2 > T 6 > T 7 > T 8OR-- Restore path F 1 > F 2 > T 6 > T 7 > T 8OR-- Restore path F 1 > T 1 > T 2 > T 3 > T 4 > T 5 > T 6 > T 7 > T 8.....etcCorrect method: Image see below
For More Ref:
h t t p s://w w w.m s s q l tips. com/s q l server tip/3049/different-ways-to-restore-a-s q l-server-database/
