70-457 Reviewer #12, Restoring SQL Server Databases

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’
FROM <BackupFile>

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;

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: