By Delia / Last Updated December 20, 2021

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.

sql server express

Restore SQL Express database from backup

To restore database from backup, 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

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...

restore task

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.

select database

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.

add bak file to restore from

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:

RESTORE DATABASE DatabaseName FROM DISK = 'Filepath'

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.

t-sql restore database from backup

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:

RESTORE DATABASE DatabaseName FROM DISK = 'Filepath_full' WITH NORECOVERY
GO
RESTORE DATABASE DatabaseName FROM DISK = 'Filepath_diff' WITH RECOVERY
GO

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:

SqlCmd -E -S ServerName -Q "RESTORE DATABASE DatabaseName FROM DISK ='Filepath'"

cmd restore database

To restore SQL Express database from a differential backup:

SqlCmd -E -S ServerName -Q “RESTORE DATABASE DatabaseName FROM DISK='Filepath_full' WITH NORECOVERY”
SqlCmd -E -S ServerName -Q “RESTORE DATABASE DatabaseName FROM DISK='Filepath_diff' WITH RECOVERY”

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:

Import-Module SQLPS -DisableNameChecking

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.

import module

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

Restore-SqlDatabase -ServerInstance ServerName -Database DatabaseName `
-BackupFile "Filepath" -ReplaceDatabase

powershell restore database

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:

Download Free Trial Win PCs & Servers
Secure Download

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.

install client program

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.

sql server 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.

add computer

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

add databases

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.

select target path

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.

restore database from backup task

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.

select what to restore

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.

restore to new location

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.