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 Centralized Backupper Database Edition 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.

In addition to managing systems, files and SQL databases on your local machine, this centralized backup software also supports you to backup and restore all Windows PCs, virtual machines and servers on the network.

Download the 30-day free trial to get started:

• Install Centralized Backupper Database on a machine as the central management console, it will auto detect all the available computers within LAN. Then switch to Computers tab and Install client program on them.
• With Agent package installed, you can Request Control over these clients for further management.

☛ How to create a SQL database backup task:

1. Go to Tasks tab and choose New Task to perform SQL Server Backup.

2. In Step 1, click + Add Computers to detect all the controlled computers with SQL database, and select the one you want to backup.

3. In Step 2, click + Add to detect instances on the select computer, and tick all the databases you need to protect.

4. Click on Step 3 and tap Add Storage, here you can enter the share or NAS path to specify a network location as backup destination. Then click Start Backup to execute the operation.

If you want to auto backup SQL databases, just click Schedule to set up the frequency and specific time point.

☛ How to restore SQL database from the backup:

1. The created backup task will be listed under Tasks tab, you just need to click Advanced on its top-right corner and choose Restore to proceed.

2. Select what to restore. You can select all the backed up databases or only specific databases from the backup. If you’ve executed the task for several times, you can also choose which backup file to restore from. Then click Next to continue.

3. Select a target location. You can either Restore to original location or Restore to a new location. Then click Start Restore to execute the restoration.

With the second option, you can specify another controlled client computer. Thus it’s possible to restore databases to another SQL Server.

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.