Use PowerShell Script to Restore SQL database from backup

Can you use PowerShell script to restore SQL database from backup

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.

Restore SQL database from backup using PowerShell script

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.

Preparations: Import the required cmdlet

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.

Restore one or several SQL databases from backup

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.

Restore multiple SQL databases from backup easily

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:

• Run AOMEI Cyber Backup on the server machine, click 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 -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

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

• Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs.
• Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

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

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 button to see restore details or cancel it.

When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.

✍Notes:

• "Restore to original location" must confirm the original location exist. Or else, you can only select "Restore to new location".
• "Restore to original location" will overwrite or delete the original database data, if the original database has important data, it is recommended to choose "Restore to new location".

Conclusion

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.