Enhanced Features in Database Restore in SQL Server 2012
Point of time Recovery
In a typical production environment, in
order to recover data in the even of disaster, most DBAs will schedule a
robust backup plan. Since scheduling a full back-up during peak times will have
an impact on database performance, most DBAs will have combination of full,
differential and log backup in the production environment.
Typically you will schedule a Full
Backup schedule to run once a week. A Differential Backup will be
scheduled on daily basis, preferably early mornings. Transaction Log Backup is
the most critical backup as it will allow DBAs to recover data to a point in
time. Apart from that, the Transaction Log Backup will keep the database log file
to a minimum size. Because of this, the Transaction Log Backup will be scheduled
quite frequently – typically from every 15 minutes to every hour.
In the event of disaster, DBAs need to
find out order of backups and order should not go wrong. If you are dealing
with large databases, you need to make sure that you restore databases at the
first chance rather than trying several times. More you consume the time, it’s
more losses will incur to the organization.
Prior to SQL Server 2012, at the
time of restoring, it was up to the DBA to manually configure backups by understanding the optimal
database backup type and what strategy to use, as shown below.
However, in SQL Server 2012 this process has been
visualized so it is more fool-proof. In the SQL Server 2012, when restoring you will now
see a new button named Timeline…
When Timeline… is clicked, you
will be taken to new screen with a visualized view backup schedule.
Here, you have two restore options:
- Last backup taken
- Specific date and time
Last backup taken will allow you to
restore the database up to the last backup you have taken. In case, your last
backup is a Transactional Log Backup, it will ensure the previous full backups and
subsequent log backups are used to restore the database.
Specific date and time will allow
you to recover data to a pointin time. When you select this option you have the
luxury of selecting a point of time to restore to by moving the red arrow.
Apart from the Full Database Backup, Differential Database Backup and
Transaction Log Backup, when ‘Specific date and time’ is selected you can also restore from the Tail-Log which is
shown in a light green colour.
When you select OK from the above screen, the
relevant backups will be selected as shown below.
If you script this using Script option
you will see following script. In this script, you can see relevant backups are
selected along with the correct NORECOVERY option and last Transaction Log
restore as the cut off time for the restoring.
USE [master] BACKUP LOG [BackupTimeLine] TO DISK = N'D:\Program Files\Microsoft SQL ServerDE\MSSQL11.DENALI\MSSQL\Backup\BackupTimeLine_LogBackup_2012-03-03_21-51-26.bak' WITH NOFORMAT, NOINIT, NAME = N'BackupTimeLine_LogBackup_2012-03-03_21-51-26', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5 RESTORE DATABASE [BackupTimeLine] FROM DISK = N'D:\Program Files\Microsoft SQL ServerDE\MSSQL11.DENALI\MSSQL\Backup\BackupTimeLine.bak' WITH FILE = 4, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG [BackupTimeLine] FROM DISK = N'D:\Program Files\Microsoft SQL ServerDE\MSSQL11.DENALI\MSSQL\Backup\BackupTimeLine.bak' WITH FILE = 5, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG [BackupTimeLine] FROM DISK = N'D:\Program Files\Microsoft SQL ServerDE\MSSQL11.DENALI\MSSQL\Backup\BackupTimeLine_LogBackup_2012-03-03_21-51-26.bak' WITH NOUNLOAD, STATS = 5, STOPAT = N'2012-03-03T21:44:49'
When restoring a database, you now have new a Files tab.
Using this option, you can change the path
where the database files are saved after the restore.
The Restore Options has also undergone some changes. Below are the the restore options prior to SQL Server 2012 :
In contrast, below is the restore options in SQL Server 2012 :
SQL Server 2012 adds two additional features:
- Tail-Log Backup
- Close existing connections
If you are restoring to the existing
database, you have the option of taking a Tail-Log Backup as precaution.
If you are overwriting the database, you
need to ensure that the database is not being used. However, in a typical
environment some user is often using the database. By clicking the ‘Close
existing connection to destination database’ you will kill other user
Restoring Data Pages
In SQL Server 2012, you can
restore corrupted data pages using the user interface. If
there is a corrupt page, it would be better if you have the option of restoring
only that page rather than restoring the entire database.
Page restore is a feature available from SQL Server 2005
but it can be used via TSQL scripts as shown below.
--1:343 is file id and page id respectively and CorruptDatabase_full_20120101.bak is the full backup and CorruptDatabase_20120101_1730.trn is the Transaction Log Backup. RESTORE DATABASE CorruptDatabase PAGE = '1:343' FROM DISK = 'D:\SQLData\Backup\CorruptDatabase_full_20120101.bak' WITH NORECOVERY -- Restore log RESTORE LOG CorruptDatabase FROM DISK = 'D:\Backup\CorruptDatabase_20120101_1730.trn' WITH NORECOVERY
In SQL Server 2012, users have the option
of restoring a page using SQL Server Management Studio. If you right click the database and
select Task -> Restore-> Page as shown in the below image, you
will be taken to interface to restore the page:
After selecting the database by clicking Check
Database Pages, all the corrupted pages will be listed and you can recover
them by selecting the correct backup set.