Restore Database Techniques Explanation in Details.

Whenever you faced restore db in SQL Server u could get error as see below

Msg 4305, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 146000000041500001, which is too early to apply to the database. A more recent log backup that includes LSN 146000000041000001 can be restored.

To Avoid this first understood the concepts.

First of all see the query to check db backup and tlog details as see below:

select top 10 database_name, type,case type
when 'L' then 'TLog backup'
When 'D' then 'Full backup'
end as Backuptype,first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn,backup_start_date,backup_finish_date
from msdb..backupset where database_name = 'northwind'
order by backup_finish_date desc

 Backup file details as see below:

TLOG BACKUP  file details as see below:

2014-04-07 12:00:03.000      Tlog3 happened time
2014-04-07 11:58:30.000      Tlog2 happened time
2014-04-07 11:57:38.000      Tlog1 happened time
2014-04-07 11:55:34.000      Backup happened time

Here if u restore db with backup file with condition of WITH RECOVERY then DB has been restored with only bakcup file.

But if 
if u restore db with backup file with condition of WITH NORECOVERY then DB has expect one or more Tlog file to restore.

But you should restore with order only like following option

1)

2014-04-07 11:57:38.000      Tlog1 happened time     WITH RECOVERY (db resored pointed to 11.57)
2014-04-07 11:55:34.000      Backup happened time  WITH NORECOVERY

2)

 2014-04-07 11:58:30.000      Tlog2 happened time WITH RECOVERY (db resored pointed to 11.58)

2014-04-07 11:57:38.000      Tlog1 happened time   WITH NORECOVERY 
2014-04-07 11:55:34.000      Backup happened time  WITH NORECOVERY

3) 

2014-04-07 12:00:03.000      Tlog3 happened time  WITH RECOVERY  (db resored pointed to 12.00)

 2014-04-07 11:58:30.000      Tlog2 happened time   WITH NORECOVERY

2014-04-07 11:57:38.000      Tlog1 happened time   WITH NORECOVERY 

2014-04-07 11:55:34.000      Backup happened time  WITH NORECOVERY

But you cant restore Backupfile with tlog  inorder  if  you do u could get error as 

Msg 4305, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 146000000041500001, which is too early to apply to the database. A more recent log backup that includes LSN 146000000041000001 can be restored.

Error scenario

a)

Restore backup file with  Tlog2

b)

Restore backup file with  Tlog3

That means you have to restore one by one or you can end with order as said above 1) or 2) or 3).

Example:

if you restore db of backup file see below

 

fter Restore db with backup file and RESTORE WITH NORECOVERY OPTION you can see 'northwind' db has still need one or more tlog file to expect to complete restore

So kindly select Tlog Backup file as see below

 As we already see if you select tlog file not an order then it will throw error

If u select  1158 (tlog 2) or 1200(tlog 3) before 1157(tlog1) time of tlog u should get eror as see below

select 1158(tlog 2) file as see below

Solution:

But if u should choose one by one or restore db with tlog file1 with RECOVERY.

RESTORE WITH RECOVERY----Database has been restored immediately no more file allowed to restore.

RESTORE WITH  NO RECOVERY----Database has been restored But it allows one or more file allowing to restore.

Here am using  RESTORE WITH  NO RECOVERY as see below

 Here I am restoring db with tlog1 so no error but u am using  RESTORE WITH  NO RECOVERY

option so i can able to resore another tlog file as 1158 as see below.

Here am select restore of 1158(tlog2) with RESTORE WITH RECOVERY

and we already restore 1157(tlog1)  So we cant error this time as see below.

Above This time u can see Northwind db has restored with order of tlog1,tlog2 successfully.

Thoeratical concepts as see below:

To restore a transaction log backup

1. Execute the RESTORE LOG statement to apply the transaction log backup, specifying:
   
   • The name of the database to which the transaction log will be applied.
     
   • The backup device where the transaction log backup will be restored from.
     
   • The NORECOVERY clause.
     
   The basic syntax for this statement is as follows:
   RESTORE LOG database_name FROM <backup_device> WITH NORECOVERY.
   Where database_name is the name of database and <backup_device>is the name of the device that contains the log backup being restored.
   
2. Repeat step 1 for each transaction log backup you have to apply.
   
3. After restoring the last backup in your restore sequence, to recover the database use one of the following statements:
   
   • Recover the database as part of the last RESTORE LOG statement:
     
     RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;
     GO
     

   • Wait to recover the database by using a separate RESTORE DATABASE statement:
     
     RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY; 
     RESTORE DATABASE <database_name> WITH RECOVERY;
     GO
     

     Waiting to recover the database gives you the opportunity to verify that you have restored all of the necessary log backups. This approach is often advisable when you are performing a point-in-time restore.

Requirements for Restoring Transaction Log Backups

To apply a transaction log backup, the following requirements must be met:

• Enough Log Backups for a Restore Sequence : You must have enough log records backed up to complete a restore sequence. The necessary log backups, including the tail-log backup where required, must be available before the start of the restore sequence.
  
• Correct restore order:  The immediately previous full database backup or differential database backup must be restored first. Then, all transaction logs that are created after that full or differential database backup must be restored in chronological order. If a transaction log backup in this log chain is lost or damaged, you can restore only transaction logs before the missing transaction log.
  
• Database not yet recovered:  The database cannot be recovered until after the final transaction log has been applied. If you recover the database after restoring one of the intermediate transaction log backups, that before the end of the log chain, you cannot restore the database past that point without restarting the complete restore sequence, starting with the full database backup.

Examples (Transact-SQL) 

By default, the AdventureWorks2012 database uses the simple recovery model. The following examples require modifying the database to use the full recovery model, as follows:

Transact-SQL

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

A. Applying a single transaction log backup

The following example starts by restoring the AdventureWorks2012 database by using a full database backup that resides on a backup device named AdventureWorks2012_1. The example then applies the first transaction log backup that resides on a backup device named AdventureWorks2012_log. Finally, the example recovers the database.

Transact-SQL

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorks2012_1
   WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 1,
   WITH NORECOVERY;
GO
RESTORE DATABASE AdventureWorks2012
   WITH RECOVERY;
GO

B. Applying multiple transaction log backups

The following example starts by restoring the AdventureWorks2012 database by using a full database backup that resides on a backup device named AdventureWorks2012_1. The example then applies, one by one, the first three transaction log backups that reside on a backup device named AdventureWorks2012_log. Finally, the example recovers the database.

Transact-SQL

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorks2012_1
   WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 1,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 2,
   WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 3,
   WITH NORECOVERY;
GO
RESTORE DATABASE AdventureWorks2012
   WITH RECOVERY;
GO

Sql Server : create new Database from the Backup of another Database







How to create new Database from the Backup of another Database?

Scenario:

I was using Adventureworks2008 database for practicing in Sql Server management system. During practice I wanted to use DDL, (create, alter, drop), and DML (delete, update) and many other operations as well. For that I wanted to keep my adventure works database safe for other practices as well.  Then I had a thought , if would have a copy of this same database , so that I could perform all kind of operations on one copy of my Adventureworks2008 database, and another remains safe for real projects.

Solution :

For the above scenario, we can create same database as Adventureworks2008 in two ways

A.      If Adventureworks2008 database exists on some other server or system, then we can take back up of Adventureworks2008, and then we can restore our empty database from the backup file.




B.      If Adventureworks2008 database exists on same server, then we can directly restore our empty database from Adventureworks2008 database.

Case A. When Adventureworks2008 database exists on some other server or system

Below are the steps that need to take place for creating backup file for Adventureworks2008

1: Right click on AdventureWorks2008 Database and go to Tasks->Backup











             2: Give a path as Destination, where you want to keep the backup., then click on ‘OK’








  Case B:  When Adventureworks2008 database exists on same server,

Then no need to create backup file, we can directly restore it from Adventureworks2008 Database

Below are the Steps, which will be followed to create and restore new database in both cases A and B

Step1:

Create an empty new database say Copy_AdventureWorks





Here in the below snapshot, the path mentioned, is the path where the new database will be created





Step2:

Right Click on Copy_AdventureWorks database then go to Tasks->Restore->Database








In the below snapshot of restoring database, we have to give the source and location for backup file to restore.

There can be two case, as mentioned above Case A and Case B.

Here we are assuming that Adventureworks2008 database exists on the same system that is Case B, So we select ‘from database’ and select ‘Adventureworks2008’ Database form the dropdown.

In Case B, when Adventure Works doesn’t exists on same system or server, then we will select ‘From Device’, and will give the path where the backup file exists.











When we click on OK, we get below error

‘Restore Failed and the backup set holds a backup of a database other than the existing Copy_AdventureWorks’





It’s because the backup taken is of Adventureworks2008 database. So we will need to overwrite the existing database. For this, go to option and check the checkbox ‘Overwrite the existing database’








Still we get some error as shown below in the snapshot:

The error is ‘…/.AdventureWorks2008_Data.mdf cannot be overwritten. It is being used by database ‘Adventureworks2008’.








The solution is to change the name for .mdf and .ldf file. That we can do by changing the name in Restore As column

 






 Change the name ‘AdventureWorks2008_Data’ to ‘Copy_AdventureWorks_Data’

And ‘AdventureWorks2008_Log’ to ‘Copy_AdventureWorks_Log’

And then click ‘ok’, it will work and you will get your new empty database Copy_AdventureWorks restored with the same Adventureworks2008 Database.