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.
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.
|
1 |
>Get-help Restore-DbaDatabase |
It gives the following output.
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)
In Restore-DbaDatabase command, specify SQL instance name and backup directory. Let us see if DBATool tries to restore all backup files.
|
1 |
>Restore-DbaDatabase -SqlInstance Kashsish\SQL2019CTP -Path C:\TEMP\Backup |
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.
|
1 |
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -WithReplace |
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.
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 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.
|
1 |
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -DestinationDataDirectory C:\TEMP\DB -WithReplace |
In the following output, we can verify RestoreDirectory reflects new specified directory.
We can go to this directory and verify that a database log and data file exists in this.
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.
|
1 |
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -DestinationDataDirectory C:\TEMP\DB -DestinationLogDirectory C:\TEMP\DB\Log -WithReplace |
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.
In the following command, we specified a new database name with parameter -DatabaseName and specified parameter –ReplaceDbNameInFile.
|
1 |
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -DatabaseName "SQLShackDemo_Restore" -ReplaceDbNameInFile |
This command restores a database with a new name and stores physical files with new names.
Let us run the command sp_helpfile. We can verify logical and physical file names.
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.
|
1 |
>Rename-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Database "SQLShackDemo_Restore" -LogicalName "<DBN>_<FT>" |
It gives the following output. We cannot see the modified name in this command output.
Rerun sp_helpfile command on SQLShackDemo_Restore database. We can see modified logical file names in the following screenshot.
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.
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.
|
1 |
>Restore-DbaDatabase –SqlInstance Kashish\SQL2019CTP –Path C:\Temp\Backup –DatabaseName “SQLShackDemo_Restore_1” –ReplaceDbNameInFile |
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.
- Restore Full database backup with NoRecovery
- Restore differential database backup with NoRecovery
- Restore log backup with Recovery
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
-
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
-
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
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.
|
1 |
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path c:\Temp\backup -DatabaseName SQLShack_NoRecovery -NoRecovery |
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.
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.
|
1 |
>Restore-DbaDatabase –SqlInstance Kashish\SQL2019CTP –Recover –DatabaseName SQLShack_NoRecovery |
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.
Refresh SQL instance in SSMS, and we can see database is showing online now.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023