Rajendra Gupta
Database status in SSMS

SQL Restore Database using DBATools

April 3, 2019 by

This article will cover SQL restore database operations using the open-source PowerShell module, DBAtools, and will cover commands for backup restoration using the command Restore-DBABackup with many various permutations like restoring from file, separate directory, renaming databases, norecovery options and more

In my previous article, SQL Database Backups using PowerShell Module – DBATools, We explored taking SQL database backups using PowerShell Module DBATools. Database administrators used to get a frequent request for restoring a database to another instance. We also require backup restoration to recover from corruption, accidental data deletion, disaster recovery scenario. Usually, we restore backups using the SSMS GUI method or t-SQL commands. DBATools can also be a useful tool for SQL restore database operations.

In this article, we will explore SQL restore database operations using PowerShell Module DBATools.

DBATools commands for backup restoration

Let us view command related to keyword Restore in DBATools using Get-Help.

SQL Restore database - DBATools commands for backup restoration

We will explore restoration in a further step.

Restore-DbaBackup

We can perform SQL restore database operations using specified backup files using this command. We have multiple configuration options in this command. We can get syntax as well as brief information of Restore-DbaDatabase with the following command.

It gives the following output.

SQL Restore database - Restore-DbaDatabase command in DBATools

We will explore multiple SQL restore database scenarios using DBATools in a further section.

Restore database from backup files placed in a directory

Suppose we have a backup directory having SQL database full backups and we want to restore databases from these backup files.

In the following screenshot, you can see I have three full backups’ files for SQLShackDemo database.

  • SQLShackDemo-Full-201903170927.bak (backup time – 03/17/2019 09:27 AM)
  • SQLShackDemo-Full-201903170904.bak (backup time – 03/17/2019 09:04 AM)
  • SQLShackDemo-Full-201903170857.bak (backup time – 03/17/2019 08:57 AM)

sample backup files

In Restore-DbaDatabase command, specify SQL instance name and backup directory. Let us see if DBATool tries to restore all backup files.

I am trying to restore a database on source instance. We get an error message that data, log file already exists, and we need to specify WithReplace option to replace the existing database.

In the following screenshot, you can notice it restores SQLShackDemo-Full-201903170927.bak. Restore-DbaDatabase command scans the files in the specified directory and then filters backup files to prepare a database restoration plan. In my case, we have full backups for SQLShackDemo database taken at different time. It scans all files and chooses the latest full backup file.

Restore database SQL Server - Restore-DbaDatabase command in DBATools examples

Once the SQL Restore database operation is complete, it gives useful information as an output of it.

  • BackupFileName
  • Database Name
  • Backup Size
  • Compressed backup size
  • Script: it also gives you the SQL script used by DBATools for restore database in our example. You can also copy this script to use further restoration

Restore database SQL Server - Restore-DbaDatabase command in DBATools examples

Restore database from backup files with data and log files in a separate directory

In the previous example, we have used the database default directory to restore a database backup. Suppose we want to use a separate directory. We want to replace the existing database as well. We need to specify a new directory in -DestinationDataDirectory parameter.

Execute the following code in PowerShell.


Restore database SQL Server - Restore-DbaDatabase command in DBATools examples

In the following output, we can verify RestoreDirectory reflects new specified directory.

Restore database SQL Server - Restore-DbaDatabase command in DBATools examples

We can go to this directory and verify that a database log and data file exists in this.

Restore database SQL Server - Restore-DbaDatabase command in DBATools examples

Currently, we placed both data and log file in the same directory while doing SQL restore database operations. As per best practice, in a production environment, we place the log file in a separate directory. Let us do another restore database with the following directories.

  • Data file directory: C:\TEMP\DB, specify using – DestinationDataDirectory parameter
  • Log File directory: C:\TEMP\DB\Log, specify using – DestinationLogDirectory parameter

In the following code, we specified both directories. Execute this in PowerShell.


Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

Restore database from backup files as a new database and rename logical file names using DBATools

In the previous example, we restored the database on an existing SQL Server database with a REPLACE option. Many times, we need to restore the database on same instance with a new database name. Suppose we want to restore last full backup of SQLShackDemo database as SQLShackDemo_Restore.

Before we start a SQL restore database operation, execute a sp_helpfile command to get a list of logical and physical file names in our source database.

Database restore in SQL Server - Output of sp_helpfile

In the following command, we specified a new database name with parameter -DatabaseName and specified parameter –ReplaceDbNameInFile.

This command restores a database with a new name and stores physical files with new names.

Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

Let us run the command sp_helpfile. We can verify logical and physical file names.

Output of sp_helpfile

In the screenshot, you can observe that physical file name changed as per the new database name. We can see the logical name similar to the source database. We should have different logical file names for each database. We can change logical file names for the restored database using DBATools command Rename-DbaDatabase.

In the following query, we specified LogicalName in the format <DBN>_<FT>. Once we execute this command, SQL Server will replace DBN with database name and add suffix _FT. We should have a logical name of database files as SQLShackDemo_Restore and SQLShackDemo_Restore_Log.

It gives the following output. We cannot see the modified name in this command output.

Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

Rerun sp_helpfile command on SQLShackDemo_Restore database. We can see modified logical file names in the following screenshot.

Database restore in SQL Server - Output of sp_helpfile to reflect change in logical file names

Restore database from combination of backup files (Full differential and log backups)

In a production environment, we take multiple database backups for a single database to keep RPO and RTO in mind. Suppose we have a large database having a size in TB’s. Usually, a DBA follows the following approach for SQL Restore database operations in a large production database.

  • Weekly Full backup
  • Daily differential backup
  • Hourly transaction log backups

In the following screenshot, I have taken all these backups for SQLShackDemo database for this demo with a small interval.

sample database backups

If we are doing SQL restore database operations using SSMS or t-SQL, we need to specify each backup file and prepare the restore chain for it. We can do multiple backup restorations with an intelligent PowerShell module DBATool.

Once we specify directory has all backups, it scans all backup files. It further goes through each file and prepares a restoration plan automatically for us.

Let us execute database backup using Restore-DbaDatabase command and observe the behaviour.

In the following command, we want to restore all backup files placed in the specified directory. We want to create a new database for restoration.

In the output, you can notice that after performing a scan, it starts a backup of individual files in the correct order. The correct order for restoration is as follows.

  1. Restore Full database backup with NoRecovery
  2. Restore differential database backup with NoRecovery
  3. Restore log backup with Recovery

Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

Once it finishes restoration for all backup files, we get the following output for each backup files.

  • In the following screenshot, you can see it restores the full backup at first. You can also notice parameter NoRecovery: True. It shows that further backups will be restored on this database

    Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

  • Next, in the output, it shows database restoration of a differential backup file. In this step, we can see parameter NoRecovery: True to restore the database in NoRecovery mode

    Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

  • In the last step, it should restore transaction log backup with Recovery mode. In the output, you can verify that it starts restoration from a log backup file. We also have a parameter as NoRecovery: False. It shows that the database will be online after log backup restoration. It does not require any further restoration for this database

    Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

SQL restore database operations in NoRecovery Mode

We might not want a database to be available after backup restoration. Suppose you are maintaining another copy of the database for a disaster recovery purpose. You want to apply regular transaction log backup on this database. You might consider this approach as a manual log shipping approach.

We can fulfil this requirement using DBATool as well. We can use -NoRecovery parameter in restore database command.

In the following command, we want to restore backups placed in a specified directory with mentioned database name in NoRecovery mode. Execute the following command in PowerShell.


Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples

Once a SQL restore database operation is completed, you can connect to SQL instance and verify database status. In the following screenshot, you can see a status for the highlighted database as Restoring.

Database status in SSMS

We are doing regular restoring differential or log backup on this database. We should be able to recover a database and bring it online when required. We can easily perform recovery on this database with parameter –Recover. We do not need to specify any backup files while recovering this database.

In the output, you can see the script for this command. In this script, we can see With Recovery Clause. It performs recovery and brings the database online.

Database restore in SQL Server - Restore-DbaDatabase command in DBATools examples - Recovery

Refresh SQL instance in SSMS, and we can see database is showing online now.

Database status in SSMS

Conclusion

DBATools is an excellent tool to perform SQL restore database. We can efficiently manage the restoration task using this PowerShell module. I suggest you go through it and be familiar with DBATool. We will continue covering more on DBATools in my further articles.


Table of contents

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
904 Views