This article will explain how to restore SQL database from backup using PowerShell scripts. If you find it difficult, here’s also an alternative.
The answer is yes. As an automated Windows scripting tool, PowerShell provides a variety of cmdlets, among which Restore-SqlDatabase can be used to restore database from backup.
Here's a basic tutorial and simple sample that can be used for one or several databases. If you need an easy way to restore more (or all) databases on SQL Server, you can also consider using professional SQL Server backup software.
First, using PowerShell script to restore a database assumes that you have created a backup file in .bak format. For the backup method, you can refer to PowerShell script to backup multiple SQL databases.
With the previously created backup files, you could follow this guide to restore SQL databases.
1. Search for “powershell” in Windows search bar, and run Windows PowerShell ISE as administrator.
Tip: PowerShell ISE is basically an upgraded version of PowerShell. It adds a notepad function allowing you to freely select and delete characters from the commands you type. You can also save the script as executable ps1 files.
2. Then you need to import the appropriate cmdlet. click New Script, type the following command and run it:
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.
The basic PowerShell script to restore SQL database is:
Restore-SqlDatabase -ServerInstance Server/InstanceName -Database DatabaseName `
-BackupFile "Path\FileName.bak" –ReplaceDatabase
Fill in your own information and run the command, then the specified database will be restored from the bak file. Here my example is:
Restore-SqlDatabase -ServerInstance Desktop-J899PPM -Database Date_1 `
-BackupFile " C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Date_1.bak" –ReplaceDatabase
*To customize the operation further, you can check this Microsoft page to learn more parameters of Restore-SqlDatabase.
It may not be difficult to restore one or two SQL databases from backup. But if you want to recover numerous or all databases of an instance, you may need to use very complex scripts, whether in SSMS, command line or PowerShell.
If you are not adept at this and are fed up with repeated failures due to minor mistakes, using professional database backup & restore software is also a good way to go.
AOMEI Cyber Backup is an easy-to-use tool that enables you to select as many SQL databases as you want for backup or restore. With it, you can easily set up a schedule to auto execute the backup in full or differential mode, and restore SQL databases to any backed up state. All the operations can be done intuitively without using scripts.
It works with all the Windows OS above (and including) Windows 7 and Windows Server 2008 R2. Its SQL Server Backup feature supports SQL Server 2005 to 2022, and enables you to back up or restore multiple databases at once. In addition, it also has virtual machine backup features that can work as VMware or Hyper-V backup solution.
Download the 30-day free trial to get started:
▶ 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.
A completed task will be listed in the Backup Task tab. Then you can edit or restore from SQL database from it when you need.
▶ How to restore SQL 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
When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.
✍Notes:
In this article, we have introduced how to use PowerShell script to restore SQL database from backup file. But when you have a large number of databases to protect, it will be much easier to use software like AOMEI Centralized Backupper Database Edition.
Its intuitive interface allows you to backup all MSSQL databases at once, and restore them directly from the created task. Besides, with all the powerful features to protect files, partitions, disks and OS on Windows computers, you can use it as an all-around small business backup strategy.