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 sessions.
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.
]]>