SQL Server Portal

Backup and restore are two major parts of any DBA’s job and this part should be done efficiently. In order to do it efficiently, DBA’s should take backups in such a way that makes their life easier in case of any failure. But we need to remember that we have different types of backup (Full, Differential, Log) and at the time of failure we must use a correct path (Sequence) to restore all data successfully.

Before SQL Server 2012, DBA’s should manually control all these aspects for restore. But in SQL Server 2012, a solution came namely “Recovery Adviser”, which can show you the timeline graphically and advise you how to restore it efficiently and also you can define your own best possible path of restore as well.

Lets discuss Recovery Adviser step by step in detail.

Step 1 :
Lets create table and insert few records in it to test.

use test_db
Go
Create table test
(
[ID] int,
[Name] nvarchar(50)
)
Go
Insert into test Values(1,'Imran')

Step 2 :
Take a full backup of test_db database.

BACKUP DATABASE [Test_DB] TO DISK = N'G:\DBBackup\test_DB_Full'
WITH NOFORMAT, NOINIT, NAME = N'Test_DB-Full Database Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 3 :
Insert one more record and take a differential backup of test_db database.

use Test_DB
GO
insert into test values(2,'Bob')

BACKUP DATABASE [Test_DB] TO DISK = N'G:\DBBackup\test_DB_Differential'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'Test_DB-Differential Database Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Step 4 :
Insert one more record and take a transaction log backup of test_db database.

use Test_DB
GO
insert into test values(3,'John')

BACKUP LOG [Test_DB] TO DISK = N'G:\DBBackup\test_DB_Log'
WITH NOFORMAT, NOINIT, NAME = N'Test_DB-Transaction Log Backup'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Step 5 :
We took three different types (Full, Differential, Transaction) of backup and now we can recover the database in case of any failure but how ? In earlier version we need to keep a track of backups as to which backup we took, at what time, in what sequence etc etc. But SQL Server 2012 “Recovery adviser”, absorbs all such worries and will handle it efficiently. Lets go to restore window to see what is inside this “Recovery adviser”.

In the restore screen, you can find a new button namely “Timeline” and the list of backups that we took earlier in the same sequence with lots of additional information that can help you recover your database.

Step 6 :
Click on the timeline button and you will be more than impressed to view the new timeline (on the basis of Hour, Six Hour, Day, Week) of backup. In this screen you have two options to restore the backup.

• Last backup taken
• Specific date and time

From the first option, you can restore on the basis of last backup. But sometime if you need to recover any particular transaction, you can restore at any point of time from option 2 as well.

Conclusion :
Database recovery adviser is a very handy tool shipped with SQL Server 2012. It helps you to restore your backup at any point of time with the graphical representation such as when and what type of backup you took and how can you restore it efficiently in case of any failure. This will resolve a lot of backup handling issues, for example, to maintain a sequence and to restore in a best possible path as well.

Read Full Post »