How to Restore SQL Express Database from Backup (5 Ways)

About SQL Server Express

SQL Server Express (2012, 2014, 2016, 2017, 2019) is the free version of Microsoft SQL Server. Compared to the paid version, it has some technical restrictions that make it unsuitable for large-scale deployments, such as the artificial hardware usage limits, and 10 GB limit of database size.

Also, it lacks SQL Server Agent which is commonly used to schedule administrative tasks. But since there is usually no need to restore SQL Express database automatically, your recovery operations should not be affected much.

Restore SQL Express database from backup

To restore database from backup in SQL Express, you need previously made .bak files. If you are not sure how to do, here’s a tutorial about MSSQL backup.

Below you will find 5 methods for SQL Server Express to restore database from backup. They are roughly the same as restoring SQL Server database, you can pick one as you need, and click on the anchor text to quickly view it.

Method 1. SSMS GUI: Simple, click-based, but only allows you to restore one database at a time.

Method 2. T-SQL: Powerful and flexible, requires a certain level of scripting knowledge.

Method 3. Command line: Execute T-SQL statement from Command Prompt, requires SQLCMD.

Method 4. PowerShell: Use cmdlets in PowerShell to restore database from previous made bak file.

Method 5. SQL Server backup software: Flexible and easy-to-use, no expertise required.

Method 1. Use SSMS GUI to restore SQL Express database

In SQL Server Express, restore database backup can also be done via SSMS GUI, here are the detailed steps:

1. Launch SSMS and connect to your instance. Find the database you want to restore under Object Explorer, right-click it and choose Tasks>Restore>Database...

2. In this step, you can select “Source” as Database or Device. If your backup files are stored in the default directory, you can choose the former. Otherwise you can choose Device and click ... to specify a bak file manually.

3. If there’s no problem with the backup you Add, the progress will show as “Done”, and you can directly click OK to restore it.

Method 2. Use T-SQL to restore SQL Express database from .bak file

Launch SSMS and connect to the instance you want to restore database to. Click New Query to create a SQLQuery window, enter the restore command as below:

This is the basic statement to restore SQL database from bak file. Just fill in your own database name and backup file path. Please note the path you use should contain the file name and .bak extension.

If you want to restore database from a differential backup, the you need to restore from the last full backup in advance. The commands will be like:

After inputting the T-SQL statement correctly, you can click Execute to run it.

Note: If you receive “The tail of the log for the database has not been backed up” during the restoration, you can follow the instructions, i.e. BACKUP LOG WITH NORECOVERY first (if it contains work you don’t want to lose), or restore database WITH REPLACE or WITH STOPAT to overwrite the contents of the log.

Method 3. Use command line to restore SQL Express database

In fact, you can also run T-SQL statements in Command Prompt via SQLCMD. This is especially suitable for users who do not want to log into SSMS every time to perform restore operations.

Press Win + R to open Run window, enter “cmd” in the textbox and hit Enter key. Then you can input commands in the popping out window to perform SQL Express database backup and restore.

Tip: To run backup statement from Command Prompt, please refer to backup SQL database with command line.

The basic command to restore SQL Express database is:

To restore SQL Express database from a differential backup:

Tip: If you need to perform the same operation frequently, you can also input the command in a txt editor, and save it as a .bat file. Thus you can easily execute the task by double-clicking.

Method 4. Use PowerShell to restore SQL Express database

Using PowerShell to restore database also requires previously created .bak files. If you want to perform the backup with PowerShell as well, please refer to PowerShell script to backup SQL databases.

With the backup files, you can follow steps below to restore SQL Express databases.

1. Click Start menu, select Windows PowerShell, and start Windows PowerShell ISE as administrator.

2. Click New Script, type the following command to import the corresponding cmdlet:

Note: If you get an error when importing the module, indicating that SQLPS is not trusted, try executing Set-ExecutionPolicy RemoteSigned first, and then re-importing the cmdlet.

3. Then enter the following command to run SQL Express database backup:

Method 5. Restore SQL (Express) databases from backup task directly

Although all the above methods can help you restore SQL Express database from backup, each of them has some limitations in use. If you want to try a more intuitive and flexible way to perform SQL Server (Express) backup and restore, AOMEI Centralized Backupper Database is a good choice.

It is click-based like SSMS GUI, with the difference that it allows you to backup and restore multiple databases at once. When specifying a destination, you can choose to restore to the original location or a new location, so you can even restore database to another SQL Server directly.

Noteworthy is the fact that Centralized Backupper Database is a centralized enterprise endpoint backup software. With it, you can create and manage backups for multiple PCs, workstations and servers within LAN.

To use it, first you need to download and install Centralized Backupper Database as the central console. Here’s a 30-day free trial you can try out:

Then, launch it and Install client program as you need. It can be done manually or remotely. For the computers with client program installed manually, you need to Request Control over them by entering account or sending message.

After confirmation, you can create centralized backup tasks for all the controlled clients from the central console. In order to restore SQL Express databases, first you need to navigate to Tasks > New Task > SQL Server Backup to make a database backup.

The setup window consists of three steps:

Step 1. Click + Add Computers to choose the client computer with the database you want to backup.

Step 2. Click + Add to select the source data. You can add multiple databases or even instances on the client.

Step 3. Click on the select box and hit Storage to enter the path of a target share or NAS. You can also click Schedule to set up automatic backup, or manage Settings like backup encryption or compression. Then click Start Backup.

How to restore SQL Express database from the backup:

Step 1. The created task will be listed under Tasks tab, you can restore database from it any time you need. Click Advanced on the upper right corner or the task, choose Restore from the menu. Or you can expand the task, right-click the specific source computer to Restore it.

Step 2. After choosing the computer to restore from, you can select databases (all or specific) in the backup to restore. If you have performed this task more than once, you can also select a specific full or differential backup in the upper right corner.

Step 3. Finally, select the destination to restore to. You can either Restore to original location, or Restore to a new location. The “new location” here could be another SQL Server on another client computer. Click Start Restore to execute it.

Conclusion

This article provides different solutions for SQL Express 2012/2014/2016/2017/2019 to restore database from backup. Among them, AOMEI Centralized Backupper Database Edition is powerful, easy-to-use and does not require any expertise.

It works with all PCs and servers on your LAN and supports not only SQL databases, but also computer files, systems, disks, partitions. You can use it as a complete client-server backup software.