Restore your SQL Server Express Database

by Philipp Stiefel, originally published March 9^th, 2017

Based on a photo by russ101, used here under CC0 licensing

This final article in this series about backups of your Microsoft SQL Express database wraps up the topic by explaining how to restore a database from a backup created earlier.

The previous articles covered the fundamentals of SQL Server Backups and the implementation of automatic backups on SQL Express. You should be at least familiar with the fundamentals of backup before continuing to read about restoring databases.

What is covered here, is basically valid for all SQL Server Editions. However, as the focus of the series is on SQL Express Edition, I will cover only how to restore a complete database from a full or differential backup. If you are responsible for a huge database on a “bigger” edition of SQL Server, you should be familiar with the restore of individual files and filegroups and the transaction log as well. - That is beyond the scope of this article.

Everybody wants Restore!

Let’s get started with an old sysadmin-proverb: “Nobody wants backup, everybody wants restore!” – There is an essential truth in this. Backups on their own are not worth anything. You need to be sure it is actually possible to restore the data from the backup back to working condition. Furthermore, you should know exactly how to do this.

If your backup was done properly, the actual restore of a database should not be much of a problem. A restore operation usually isn’t something you are going to automate. You’ll fire up SQL Server Management Studio and restore the database manually.

There are two different, common scenarios that will require you to restore a database.

Hardware failure

Here is the first of the two scenarios we are going to look at. Assume the hardware, most likely the hard disks, of your SQL-Server computer failed and the database is corrupted or lost entirely. - Storing your database on a RAID greatly reduces the risk of such an event leading to data loss. That being said, I actually had a situation with a second disk in a RAID failing within 48 hours after the first and before the first one was replaced. - Bad things do happen.

For the sake of this example, we assume the computer running your SQL Server failed and the actual database files cannot be recovered. Furthermore, we assume you prepared for this and copied your backups to another location. So there is a recent backup available to restore.

The first thing to do now is to find another SQL-Server that can host your database, at least temporarily. That server needs to run the same or a newer version of SQL-Server. It is not possible to restore a database backup on another SQL-Server running an older version than the one the backup was created on. If there is no other SQL Server available, you obviously need to install one on another computer.

Then copy your most recent backup to the new (replacement) server. Now start up SQL Server Management Studio (SSMS) and connect to the SQL Server instance using an account belonging to either the sysadmin or the dbcreator server role.

In SSMS select the databases folder in the Object Explorer Tree View and invoke the context menu. Select the item Restore Database. Now, in the Restore dialog perform the following steps.

1. The database does not exist on the replacement server yet. So change the Source to Device.
2. Click the …-button in the Device line to open the Select Backup Device dialog
3. In the Select Backup Device dialog click the Add-button, to invoke the Locate Backup File dialog.
4. Select the backup file (Backup Device) containing the most recent backup of the database.
5. Confirm the Locate Backup File dialog with OK.
6. Confirm the Select Backup Device dialog with OK.
7. Type in the database name as Destination … Database.
8. (Not in screenshot) Confirm the Restore Database dialog with OK, to start the restore process.

After the restore process completes, you database itself is restored to the state of the most recent backup. - Any data that was entered after the last backup is lost.

What you probably need to do now, is to configure the appropriate permissions for the users of your database. Pay attention to the fact that there might exist users in the database that have got the same names as the ones on the new server but have got different principal SIDs. So these user are not the same even though the name is identical. You need to delete the old users and create new ones pointing to the correct principals on the new server.

The final step to put your database back into operation is to point your frontend application to the replacement server. If the new server will permanently replace the old one and wasn’t previously used for other applications, the easiest solution is to simply name the new server exactly as the old one.

If that is not an option, you might have got a bit of a problem at hand. You need some way to change the configuration of all client applications out there to use the new database sever now.

I usually have got some sort of update script for my applications installed on each client computer. This script will check a central location for updates to the application each time a user logs in to Windows and update the application if necessary.  -  Maybe I’ll write more about that another time, but it is beyond the scope of this article.

Accidental misuse by an user

Now for the second fairly common scenario that will require you to restore a backup of your database. This situation might occur if someone accidentally modified or deleted a significant amount of important data in the database. - Good application design should prevent this type of situation, but let’s assume it just happened anyway.

The server computer and the database is technically undamaged and fully functional. However there is data missing you desperately need. - Be aware, things can get pretty messy now.

There are several facts you need to assess. Quickly!

1. How quick was the user error leading to data loss detected? Did the user ring you up right away after he accidentally corrupted the database, or did it go unnoticed for days or weeks?
2. How serious is the impact of taking the database offline/inaccessible for a couple of minutes? Would that mildly annoy a couple of users but do no real harm, or would it bring mission critical operations at your company to a grinding halt?
3. Is there a risk that ID-Values of deleted records get reused? Are those Ids application-internal Ids only or are they public Ids? It’s bad enough with Internal Ids, this will make your recovery work much harder, but if there are public IDs is, like Customer-Ids or Invoice-Ids that were send out to actual customers, you are in deep s**t. You definitely need to consult the applications users on the way to proceed.

If there is a risk the ID-Values of the deleted records are reused by new records entered in the database you need to act quickly and put your application in a state where users cannot enter new records. Putting the database in Restricted-Access-Mode would be one option to achieve that.

To do that select the database in the SSMS’s Object Explorer and click Properties in the context menu. In the Database Properties dialog go to the Options tab and scroll down to the Restrict Access option. Set it to RESTRICTED_USER. That will terminate all active connections to your database and then limit access to the DB to members of the db_owner role only. - Your application users are not in the db_owner role, are they? - This scenario is once more underlining that this is a bad idea.

Remember to set the above option back to MULTI_USER when you completed the manual recovery operation.

This data loss situation cannot be completely resolved with restoring a database backup alone. You’ll need to manually recover/recreate some data.

To do this, you restore your most recent backup taken before the data loss. But you do not overwrite your current production database in the process. Instead you restore that backup to new database with a different name on the same server, like YourDatabase_recovery.

The actual steps to restore the database are the same as detailed above in the hardware failure scenario. Except that you need to enter a database name other than that of the live production database as restore destination.

It is a good idea to make another copy-only-backup before starting the manual recovery process of the data. Just in case you mess anything up during the recovery.

After the backup-restore operation, you obviously need to manually copy over the deleted records from the database restored from the backup to the production database. The details of this process is highly dependent on the structure of your database and the data that got deleted. So I cannot give you any further advice on this.

Any data entered and accidentally deleted after the restored backup was taken, is lost permanently.

Prepare for disaster

Now, we are through with all the technical stuff regarding backup and restore. Let me finish this article and the series with some advice on the next steps you should take to make your strategy solid.

I strongly recommend, you dry-run your restore process under real live conditions. Pretend your “data center” just burnt down and you need to restore your backups on a completely different server elsewhere. - Be strict, don’t cheat! - If there is something essential missing in your backups, anything you need to copy over from the production environment to get your database up and running on the new server, your backup strategy failed and needs to be revised and amended.

You succeeded and everything works as planned? Good! – Now write down the steps you performed to get the backup up and running. - I’m serious, write . it . down! - If a critical production database is destroyed in mid-operation this will be a stressful situation. Your inbox fills with emails reporting the problem, you phone might ring non-stop and your boss might be in your office, yelling. In middle of this mayhem you will be very, very grateful for a complete step-by-step guide on how to restore the database. – Believe me, I have been there. Thanks to the insistence of my project manager, I had that guide. I’m not sure if I would have managed without.

Done? – Congratulations, you are now pretty solidly prepared for any sort of disaster happening to your database.

I hope you enjoyed this mini-series about backup and restore. Subscribe to my newsletter if you like to be informed about future updates on this site.

Share this article: