This lesson is the third and last part of topic for Managing Data which covers 20% of the certification exam.
Restore options depends on the database recovery model and the backup set that you do have. The following rules apply;
Full backups only – If you have the latest backup of your database then you can use this to restore your database to the point in time the full backup operation completed.
Full and differential backups – You can restore the database to the point in time when the most recent differential backup operation completed. You restore the full backup and use the NORECOVERY option first. Then restore your last differential backup using the RECOVERY option.
Full and transaction log backups – You can restore your full backup using the NORECOVERY option. Then restore your transaction log from the oldest to the most recent taken from the last successful full database backup, still using the NORECOVERY option. You then restore the most recent backup using the RECOVERY option.
Full, differential and transaction log backups – For this scenario, you restore the full database backup using the NORECOVERY option. Then recover your most recent differential backup and restore using the NORECOVERY option. Then start to restore your transaction log backups from the oldest to the most recent, still using NORECOVERY option, and using RECOVERY option on the last restore of transaction log backups.
Note that you cannot restore databases in previous versions if the database was backed up using SQL Server 2012.
Performing File Restores
You can perform a file restore by restoring the corrupt file from the most recent backup that includes the file. You can do the file restores independently of whether you have a full or file backup. It doesn’t matter. Restore then your differential backup taken of the file. Lastly restore all your transaction log backups in the sequence taken since the most recent backup of the file.
Performing Page Restores
You can use the RESTORE DATABASE statement to do a page restore operation if you have the file ID of the file that is hosting the page and the page ID of the page that you need to restore. To recover the necessary pages, you can run;
RESTORE DATABASE <DatabaseName> PAGE ‘2:42, 2:81, 2:1023′
Then restore your transaction log backups taken after the file backup with the NORECOVERY option. Then perform a new log backup. Complete this operation by restoring the newly created log backup and using the RECOVERY option.
Restoring your Database protected with TDE
You can restore a database protected with TDE as long as you have the access to the certificate and private keys. When planning for the restore operation, include the certificate used to create the Database Encryption Key (DEK) and the Database Encryption Key in your backup scheme. There is no way you can restore the database if the certificates and DEK is not accessible to you.
Restoring System Databases
You can restore the model and msdb system databases by following the steps for restoring user databases. As these 2 system database uses the simple recovery model by default, you need only the full backup to restore.
As for the master database, you can run the following;
RESTORE DATABASE master
FROM <DatabaseBackupFile> WITH REPLACE
Be extra careful handling the restore for the master database, as you may not be able to start your database engine if the master database is corrupted.
Checking Database Status
To verify the status of your database after a restore operation, you can run the command;
SELECT databasepropertyex (<DatabaseName>, ‘Status’)
You would want to see the ‘ONLINE’ result here meaning that your restore operation was successful and the database is now online.
To verify if users can connect to the database;
SELECT databasepropertyex (<DatabaseName>, ‘UserAccess’)
You would want a result of ‘MULTI_USER’ if you need your users to access the database.
One important tip in the examination is that you must be able to perform a tail-log backup and complete a restore operation.