If you have backed up SQL Server Express 2012, 2014, 2016, 2017, 2019 in case of data loss or corruption, the next issue is how to restore SQL Express database from the backup. Keep reading to find the answer.
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.
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.
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.
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.
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.
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:
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 Cyber Backup 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 AOMEI Cyber Backup is a centralized backup software. With it, you can create and manage backups for multiple servers within LAN.
To use it, first you need to download and install it. Here’s a 30-day free trial you can try out:
Click the .exe file to install AOMEI Cyber Backup on the server machine, go to Source Device -> Microsoft SQL -> Add Microsoft SQL.
Download proxy program and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device with SQL installed.
Next, click
👉How to backup SQL Server databases:
1. Click Backup Task on the left menu bar, select Backup Task -> Create New Task to open the task creating page. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.
2. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one or multiple database flexibly.
3. Select Target to save backups. You can specify a local or network path.
4. Before Start Backup, you can also set up a Schedule to run the SQL database backup automatically. Meanwhile, you can select the backup method as Full, Incremental or Differential Backup.
The created task will be listed in the Backup Task tab. Then you can edit it or restore SQL database from it when needed.
👉How to restore SQL (express) database from backup:
1. Click Backup Task on the left menu bar, locate the task you want to restore, and click
2. Select a backup version that you want to restore.
3. Select the target location that you want to restore to. If you restore to original location, you don’t need to select target. Restore to original location is selected by default and you can directly click Start Restore.
If you want to Restore to new location, you need to select target and specify the name of the new database. You can also modify the storage location.
4. After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the
✍Notes:
This article provides different solutions for SQL Express 2012/2014/2016/2017/2019/2022 to restore database from backup. Among them, AOMEI Cyber Backup is powerful, easy-to-use and does not require any expertise.