How To Restore SQL Server Database Using PowerShell

In our previous article, we learned how to take a database backup using Powershell’s dbatools module, and in today’s article, I will explain how to restore the backup we took using dbatools.

First of all, let’s learn which commands include restore in DBATools.

To do this, we must run the following command in the powershell console where we run as administrator.

As we see in the picture above, there is function information we can use, but we will be examining the “Restore-DbaDatabase” function in the dbatools module.

Restore-DbaDatabase

We can perform SQL database restore operations using this command and the specified backup files.

We have multiple configuration options in this command.

Using the following command, we can get brief information as well as Restore-DbaDatabase information.

It gives the following output.

We had previous backups using DBATools.

Now let’s perform the restoration using these backups.
(We made a backup in the D:\SQLBackup\MSHOWTO\ folder.)

Our backups are as above, let’s check a database list.

As you can see, there is only a database named MSHOWTO and we have different backups for it.

Let’s run the Restore-DbaDatabase command by providing the sqlinstance and path information and see what happens.

As you can see in the picture above, it gave an error because I wanted to restore an existing database.

You can continue by selecting the “WithReplace” option to replace the existing database.

In the screenshot below, you can see that the backup named “localhost-MSSQLSERVER-MSHOWTO-201906182024.bak” in the path we specified has been restored.

After the SQL Restore process is completed, it gives us a transaction output as you see in Picture-6. (Backup name, database name, database owner and script used, etc.)

In our first example, we restored the existing database using “withreplace”, but if we wanted to restore the backup to a different directory, it would be sufficient to run the command below.

After running the command, the output below gives us the transaction details.

As you can see, the MSHOWTO database has now been restored to D:\SQLData. Let’s check the D:\SQLData path.

How to restore to a different directory?

So far, when restoring, we have restored both data files and log files to the same directory.

However, we know that the best method is to have data and log files on different disks.

In our example, let’s perform the restoration operations on the following file paths. (I have only one disk available in the environment I work in, so I will process different file paths on the same disk.)

Data: D:\SQLData\DataFile (The value to be given to the DestinationDataDirectory parameter)

Log: D:\SQLData\LogFile (The value to be given to the DestinationLogDirectory parameter)

Command:

After running the command, it will give us the following output.

Let’s check the file paths.

How to restore a new database of names?

In our previous example, we crushed an existing database with the Replace option and restored from backup.

However, in most cases, we need to restore a new database with a different name instead of crushing the existing one.

In this example, let’s restore the MSHOWTO database from the backup we have, from a new backup named MSHOWTO_Restore.

Before performing the operation, we need the logical and physical name information of the current database, this information can be obtained using the sp_helpfile command.

The output of the sp_helpfile command is as follows.

We have specified a new database with the ReplaceDbNameInFile specified along with the DatabaseName parameter in the command below.

The above command gives us the following output.

Let’s run the sp_helpfile command for the MSHOWTO_Restore database.

We can see that the physical file names have changed. Similar output is also available in the image above.

To change the logical file names, the Rename-DbaDatabase command in the DBATools module can be used to change the logical file names after the restoration.

This way, we can have a more readable restoration process.

When we check with sp_helpfile, we can see that the logical names have changed.

As seen in the picture above, the logical name change has been achieved, but if we want to define naming in data and log types, we can use the <DBN>_<FT> format as in the command below.

So for the DBN part, it will replace it with the database name and add the _FT suffix.

When we check the sp_helpfile again after running the above command, we will see a result like the following.

How to perform a Restore operation in NoRecovery mode?

We previously learned how to take differential and Log backups using the DbaTools module.

These backups we take are not backups that can be restored on their own, so after any backup is restored, they can be restored immediately if the database is in “NORECOVERY” or “STANDBY” state.

We can use the NoRecovery parameter to fulfill this requirement using Dbatools.

The output of the command is as follows.

Let’s check the accuracy of the transaction we made from SSMS.

Differential or log backup can now be restored for this database (MSHOWTO_NORECOVERY).

After completing these restoration processes, we can use the following command to make the database usable again.

The output of the command is as follows.

When we check from SSMS, we see that the MSHOWTO_NORECOVERY database is now accessible.