SQL database recovery advisor is a newly introduced feature in SQL server 2012 (denali). It helps database administrator (DBA) to maintain correct restore sequence from an existing backups. Now DBA can restore database from the SQL server management studio (SSMS). By default, database recovery advisor will try to restore database to the last backup taken. If you want to restore another backup instead of last backup taken then use Timeline feature of database recovery advisor. Timeline displays the backup history of the database which helps DBA to restore database backup.
To illustrate the feature of database recovery advisor, let’s create a database & maintain backups to recover SQL database.
Create database employee-details
-- To create employee details database
GO
Use employee-details
GO
Create table age (c int)
-- To create table age
GO
BACKUP DATABASE [employee-details] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\employee-details.bak' WITH NOFORMAT, NOINIT, NAME = N'employee-details-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take full backup of employee details database (First Backup)
Insert into age values (1)
-- First value is inserted in age table
BACKUP DATABASE [employee-details] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\employee-details.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'employee-details-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take differencial backup of employee details database (Second Backup)
Insert into age values (2)
-- Second value is inserted in age table
BACKUP LOG [employee-details] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\employee-details.bak' WITH NOFORMAT, NOINIT, NAME = N'backup_employee-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take transaction log backup of employee details database (Third Backup)
-- Now recover to the differential backup
USE [master]
ALTER database [employee-details] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE Database [employee-details] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\employee-details.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO
RESTORE Database [employee-details] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\employee-details.bak' WITH FILE = 2, NOUNLOAD, STATS = 5
GO
ALTER Database [employee-details] SET MULTI_USER
GO
Use employee-details
GO
Insert into age values (3)
BACKUP LOG [employee-details] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\employee-details.bak' WITH NOFORMAT, NOINIT, NAME = N'employee-details-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- To take backup of transaction log of employee detail database (Fourth Backup)
How to Perform Database Restore? To perform database restore, go to the employee details node in object explorer, right click on the database node and select restore task. By default, database recovery advisor will restore database to the last backup taken i.e. Fourth Backup. To restore full, differential or first transaction log backup use timeline feature of recovery advisor.
You may like: How to backup the Transaction log in SQL Server?
No comments:
Post a Comment