USE Database Recovery Advisory | TIMELINE Restore SQL Server

Leave a comment (0) Go to comments

What is Database Recovery Advisor ?

Database Recovery Advisor is newly introduced in SQL Server 2012 which simplifies database restore operations and allows administrators to quickly and easily restore database to a point in time from an existing backup set using a visual timeline of the backup chain.

In short, Database Recovery Advisor is Graphical tool for performing POINT in TIME Recovery for any SQL Server Database.

How can I access Database Recovery Advisor in SQL Server?

Database Recovery Advisor can be accessed while performing Database Restore operations. To access Database Recovery Advisor, Right Click on Database >>> SELECT RESTORE and you will get following screen

USE Database Recovery Advisory | TIMELINE Restore SQL Server sql server TIMELINE Restore sql server Database Recovery Advisory sql server 2012 restore new feature

Once you Click TIMELINE button, you will get Backup TIME LINE Screen, which will give you what you have in your backup and you can specify time up to which you want to perform recovery either by entering Time or by scrolling Arrow


USE Database Recovery Advisory | TIMELINE Restore SQL Server sql server TIMELINE Restore sql server Database Recovery Advisory sql server 2012 restore new feature

HOW SQL Server RESTORE is DIFFERENT in SQL Server 2012 then previous versions ?

By default SQL SQL Server 2012 first complete the tail LOG backup prior to perform the restore. If we generate a TSQL CODE for above operation, that will look like this

USE [master]
BACKUP LOG [CRM] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\CRM_LogBackup_2012-02-10_14-27-18.bak' WITH NOFORMAT, NOINIT,  NAME = N'CRM_LogBackup_2012-02-10_14-27-18', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [CRM] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\CRM.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE DATABASE [CRM] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\CRM.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CRM] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\CRM.bak' WITH  FILE = 3,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CRM] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\CRM.bak' WITH  FILE = 4,  NOUNLOAD,  STATS = 5,  STOPAT = N'2012-02-10T14:25:11'
GO

The above mentioned script will do a POINT in TIME recovery up to 2012-02-10T14:25:11(specified as STOPAT), which means we will not be able to see any data which had entered after that time.

** This default, we can change this too.

For More information about SQL Server Disaster recovery solutions, check

10 SQL Server Disaster Recovery Solutions

EOF - USE Database Recovery Advisory | TIMELINE Restore SQL Server, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.