SQL 2012 Restore Database Relocate and Timeline Option

Hi Friends,

I our daily DBA routine couple of time we restore our backups either on same instance  (with a new name) or to a different instance.

If we are performing a restore on the same instance (source of the backup) and restoring it with a new name then during restore we have to change the name or location of the files. Similarily if its different location then also we have to move the location of files as per that instance setting or as we desire.

Untill now i.e. till SQL Server 2008 we have to change the name of the files (if we are restore database on same instance with a new name) or we have to change the location of database files using OPTIONS tab of Restore Windows.

In above screenshot we have a database "Test_Restore" already present on my SQL Instance. I took a backup of the same and trying to restore it with name "Test_Restore2".

Once we clien on Options table we are seeing that the file names got changed to "Test_Restore2.mdf" and "Test_Restore2_1.ldf". This is because while restoring we gave the name as "Test_Restore2" for the database.

This is simple but when we try to restore the database from one instance to second instance there we have to change the location of the file in OPTIONS tab which is manual.

So what got changed in SQL 2012 for this...?

In SQL 2012 during restore we have some new option in place

1) TimeLine option
2) Rellocate all files to folder
3) Tail Log of Source Database

In above screen shot on General tab  we are trying to restore "Test_Restore" database as "Test_Restore2" on the same server. In the screen shot (arrow at the top) we can see it says "A tail log backup at the source database will be taken. View the settings at the option tab." also it automatically displays when was the last backup taken (which we are using for restore) and a TimeLine button (will discuss shortly).

Another feature is "Verify Backup Media" , one we click on this it will run "RESTORE VERIFYONLY" option on the backup file. It will run it with NOUNLOAD & NOREWIND option.

RESTORE VERIFYOLNY FROM DISK=N'Location and Backup File Name' WITH FILE=, NOUNLOAD, NOREWIND

In above screen shot on Files tab we are seeing 2 things

* Relocate all files to folder

A check box which if we select Data file folder and Log file folder will get activated and we can specify where we want to keep the files (in which folder). This is good feature as just one time we have to specify the location and if we have a database with multipl files then the location will be changed automatically.

* Restore As

As displayed SQL Server 2012 onward we have this Restore As field which will automatically point to default location of DATA folder for the instance and restored files will be placed in this folder.

Isn't this cool, no need to change the location..... :)

Also check the name of the database & log files the name changed to TestRestoreSQL2012 as that is the new name of the database that we have given while restore (same as in it is there till SQL 2008).

In above screenshot, Recovery state: RESTORE WITH RECOVERY (same as its in SQL 2005, 2008, 2008 R2). Main thing to discuss here is TAIL LOG BACKUP option.

If we are restoring database on the same sever from which it originate and if the source database is online, during restore it will try to take a tail log backup prior to restore and bring the source database into RESTORING mode.

In above screen we are taking TailLog backup of source database but we are not keeping the source database in NO RECOVERY as we are restoring the backup with a new name TestRestoreSQL2012.

TimeLine Option

To test timeline option please use below code to create a database TestTimeLine and create table TestTimeLine, then insert 1000 records and take a full backup, then insert 500 records take transaciton log backup and then at last insert another 500 records and take another transaction log backup.

Create Database TestTImeLine;

Go

Create Table TestTimeLine (ID Int Primary Key, Name Varchar(100))

Go

Declare @I Int

Set @I=1

While @I<1001

Begin

Insert Into TestTimeLine Values (@I,'Ujsol*9kKkmm')

Set @I=@I+1

End

Go

Backup Database TestTimeLine TO Disk ='F:\TestTimeLine.Bak'

Go

-- Inserted another 500 records and took transction log backup ---



Declare @I Int

Set @I=1001

While @I<1501

Begin

Insert Into TestTimeLine Values (@I,'Ujsol*9kKkmm')

Set @I=@I+1

End

Go

Backup Log TestTimeLine TO Disk ='F:\TestTimeLine_Log1.trn'

Go

-- Inserted another 500 records and took transction log backup ---



Declare @I Int

Set @I=1501

While @I<2001

Begin

Insert Into TestTimeLine Values (@I,'Ujsol*9kKkmm')

Set @I=@I+1

End

Go

Backup Log TestTimeLine TO Disk ='F:\TestTimeLine_Log2.trn'

Go

Now lets try to restore it using TimeLine option.

First lets select the Full, 2 Transaction Log backups which we took by running above script.

Next lets click on TimeLine button and see what we get?
Here in below screen we are seeing that we have backups taken till 22-Apr-2012 10:12:23 AM (in time filed its showing 10:10:23 AM - as that it when we took out Full backup) and it consist of Full and Transaction Log Backups (see the legends for color).

Dark Green - Full
Parrot Green - TransactionLog
Red Line - End of backup

Now lets select the Specific Date and Time button: Lets select the bar and move it to 10:11:04 AM and then restore it.

At options tab we are not going to select any Tail Log backup of source database (as we are doing a Time Line Recovery from our backups).

So restore is successfull. Now lets try to do a SELECT * FROM TESTTIMELINE table in this newly restore TESTTIMELINE2 database. In my output I got records till 1500 only as the timeline which I mentioned to restore at that time only 1500 recrods were present in the database and 1500 onwards records were still not added (were in process getting added).

Important: TailLog backup restore option will only work if we have the source database residing onthe same server where we are restoring the backup. Don't expect that it will connect to remote server and take a fresh tail log backup... ;)

Hopefully this post is helpful to you. Let me know your feedback.

Regards
Gurpreet Singh Sethi