How to Restore a SQL Server Database Using dbatools Restore-DbaDatabase

In this blog post, we will audit the dbatools command Restore-DbaDatabase. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Restore-DbaDatabase is powerful tool to automate the restore of any database, and it works well in automated solutions such as daily refreshes or weekly refreshes of production to a lower environment.

Scope

Dbatools is a community project full of bright minds. This audit series is more an informational series on useful capabilities and considerations rather than rigorous testing. This is to inspire even greater use of PowerShell and by extension dbatools as I believe it will assist in automating many tasks as a DBA.

Restore-DbaDatabase Script Overview

Script Name: Restore-DbaDatabase
Author: Stuart Moore
Description: Restores an SQL Server Database from a set of backup files.

SQL Server Testing Environment Setup

I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on this blog post by Jonathan Kehayias.

Step-by-Step Execution of Restore-DbaDatabase

1. Download and Install dbatools

You are probably familiar with dbatools at this point, but if not now is the perfect time to familiarize yourself with the install and use of the module. On your SQL Server test environment you can open PowerShell and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post.

Install-Module Dbatools
Import-Module Dbatools

3. Initial Setup

To save both me and you time, I will skip the test environment steps since they are identical here to my first dbatools post.  Instead, I will give a PSA about how useless a backup strategy is. What you really want in your mind and for your company is a SQL Server restore strategy. Restore-DbaDatabase or a similar tool should be in place as part of every business continuity plan since you are only as good as your last good restorable backup. Knowing how to quickly restore a database, the time required to restore it, the potential amount of data lost in an emergency situation, and how to restore it to a point in time are all necessary steps in preparing for the worst.

This also goes along with another PSA in making sure you have multiple copies of your backups, especially one out of network location since ransomware software loves to delete these as their first step in applying pressure for you to pay up.

4. Executing Restore-DbaDatabase

Now that we have the environment set up and I’ve PSA’d your ear off, we can begin showing the capabilities of Restore-DbaDatabase in a few simple scenarios.

Scenario 1: A One-off SQL Server Database Restore

The idea of randomly needing to restore a database is a common DBA experience, even if it is not something that is done regularly. Fortunately, this command makes it very simple. We will be using splatting again since it makes for clean code, especially for blogs. Here is all we would need, assuming you have a single backup location, are using Ola scripts, have the same file structure between the two instances, and just wanted the most recent version of a database.

$Splat = @{
    #SqlCredential  = $credential
    SqlInstance     = 'Labsql1'
    Path            = '\\labshare\SQLBackups\LABSQL1\randomDB'
    }   

Restore-DbaDatabase @splat

As you can see, nothing too difficult.

Scenario 2: Scheduled SQL Server Database Restores

What about something more complicated, and a more common ask than one-off restores? Scheduled refreshes of a SQL Server database or set of databases for testing, reporting, development, etc. are common industry expectations.

The technique below extends a bit into SQL Server Agent so I copied my job create script to share here as well. I removed the hourly schedule, you can add that back if you want. The important parts are that you will need to use CmdExec to call a powershell file since it can access DBATools. Also, the DBATools module will need to be installed on the SQL Server instance running the agent job.

First, we need to modify the script from the first scenario to allow for a few new features. Specifically in enabling the overwriting of the database, and modifying the name and files to represent it as a testing refresh. We also pointed it at LabSQL2 as our test environment with the LabSQL1 acting as production. Last thing we needed to add was the trust certificate configuration on line 1.

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -register

$Splat = @{
    #SqlCredential  = $credential
    SqlInstance                 = 'Labsql2'
    Path                        = '\\labshare\SQLBackups\LABSQL1\randomDB'
    WithReplace                 = $true
    RestoredDatabaseNamePrefix  = 'Test_'
    DestinationFilePrefix       = 'Test_'  
    }   

Restore-DbaDatabase @splat

Here is the schedule-less SQL Server Agent job creation script, if you are interested in testing this in your own environment. There will be a few things to change, specifically the location of the powershell script, and within the powershell script the location of the database backup.

/* create a schedule-less job that references the powershell Restore-DbaDatabase script we created.
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'_TestMaint_Automatic restore', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@category_name=N'[Server Maintenance]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'_TestMaint_Automatic restore', @server_name = N'LABSQL1'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'_TestMaint_Automatic restore', @step_name=N'Run Powershell Script', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N' Powershell.exe -executionpolicy Bypass -file "D:\SQLRoot\MSSQL16.MSSQLSERVER\MSSQL\JOBS\Automated restore.ps1"', 
		@database_name=N'master', 
		@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'_TestMaint_Automatic restore', 
		@enabled=1, 
		@start_step_id=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@description=N'', 
		@category_name=N'[Server Maintenance]', 
		@owner_login_name=N'sa’, 
		@notify_email_operator_name=N'', 
		@notify_page_operator_name=N''
GO

There were a few more moving parts here, but through these two simple scripts we now have hourly refreshes of the production database into our lower environment. Having up to date testing data with little to no load on the production instance is a win here. An even better scenario would be if the job was running on the lower environment host, making the only draw on production resources the initial backup, and having all the restore processing be with the lower environment and the backup host.

Scenario 3: Point-in-time SQL Server Database Restore

Similar to the first scenario, a point-in-time SQL Server restore is not something done frequently, but is a common experience as a DBA. The difference here is that when a point-in-time restore is needed, it is usually an emergency. Having a script ready to go with minimal modifications required in a pinch reduces downtime.

I have two versions below; for the first you can input the date and time of your point in time restore, the second version allows you to just input the minutes to have the script dynamically fill in the current time minus the minutes you put in as the recovery target.

# Version 1 – Manual time

$RT         = Get-Date -Year 2025 -Month 6 -Day 5 -Hour 12 -Minute 15 -Second 00
$timestring = get-date $RT -format 'yyyy-MM-dd_HH-mm-ss'

$Splat = @{
    #SqlCredential              = $credential
    SqlInstance                 = 'Labsql1'
    Path                        = '\\labshare\SQLBackups\LABSQL1\randomDB'
    RestoreTime                 = $RT
    RestoredDatabaseNamePrefix  = "PIT_$timestring`_"
    DestinationFilePrefix       = "PIT_$timestring`_"  
    }   

Restore-DbaDatabase @splat | Out-GridView

# Version 2 – Dynamic Time

$minutes    = 30
$RT         = get-date $(get-date).AddMinutes(-$minutes)
$timestring = get-date $RT -format 'yyyy-MM-dd_HH-mm-ss'

$Splat = @{
    #SqlCredential              = $credential
    SqlInstance                 = 'Labsql1'
    Path                        = '\\labshare\SQLBackups\LABSQL1\randomDB'
    RestoreTime                 = $RT
    RestoredDatabaseNamePrefix  = "PIT_$timestring`_"
    DestinationFilePrefix       = "PIT_$timestring`_"  
    }   

Restore-DbaDatabase @splat | Out-GridView

As you can see, not too difficult again. I added the prefix of ‘PIT_’ for ‘point in time’ and the target restore time as well since sometimes you need to do multiple restores to troubleshoot a problem that happened an unknown but recent time ago. I also redirected the results to the gridview window since it is easier to review what happened than in the PowerShell terminal. We will take a look at that output in the next section.

These three scenarios cover the basics and should be a good jumping off point into the other examples in dbatools documentation if you have a more unique scenario.

Restore-DbaDatabase Results

Scenario 1: A Simple One-off Restore

Since it is a tiny database and speed of the restore isn’t the point between using this tool or the GUI I will show you the output of the restore with the very simple configuration we provided:

As you can see, the file structure was searched and the most recent full and log backup were found to restore with. It also used the default paths for data and log files. This is definitely an easy way to start a restore if you accept the many assumptions it will make. In my test environment and instances with properly configured default data and log paths, this is no issue.

On the SQL Side, we can see a similar story, although a bit less information related to sources and other switches used :

Scenario 2: Automated Restores

The second scenario expanded on the first, and with the job created we can have error tracking (you can also see how many attempts It took me to figure out that dbatools needed to be on the other instance and that the trust server certificate command needed to be added to the powershell file…).

There is one caveat here when using CmdExec to run a powershell file, in that if the powershell file starts without issue, the job will succeed, even if the script returns errors, see below. Fortunately, it usually gives enough to start troubleshooting with.

Connecting to LabSQL2 and running that same restore checking sql script we can see how often the “randomdb” is being restored, looks like the 12pm and 1pm automatic restores worked well, noting that the 11:36am and 1:20pm were my own testing. Easy automatic restores with tracking!

Scenario 3: Point-in-time Recovery

As mentioned above, point in time is usually an emergency so having a simple script you know works or takes very little time to get ready is ideal. The scripts provided are helpful in that they add the prefix of PIT and the timestamp to each restored database, which does make it a bit easier in the middle of troubleshooting an issue. The time of running both versions of the point-in-time recovery script  was 1:45:32. That means we should get a 12:15 manual time restored version of the database, as well as a 1:15:32 dynamic time restored version of the database.

In the screenshot below, I also show off the out-gridview window, which presents the output of the Restore-DbaDatabase in a clean and easily explored way.

As you can see, both SQL Server restores worked, the databases with prefixed names are present and the output is ready for review.

Restore-DbaDatabase Review and Analysis

There is a common theme with the commands I am choosing, in that I believe them to be extremely useful to the DBA and that they should be added to your PowerShell toolbox. Restore-DbaDatabase is another one of those specific commands that opens up the world of automation by simplifying a necessary, and sometimes repetitive task using tools they already have on their SQL Server and the PowerShell environment (free and usually included). The capabilities of this to simplify large swathes of repetitive work, as well as the option to prepare emergency restore scripts that use local or azure backups are a powerful tool in the DBA toolbox.

Warnings and Caveats

The warning here will spread across all of the commands we audit as well as anytime you use PowerShell. This script restores a database, with the option to replace the current version of the database. That scenario pointed at the wrong instance could be devastating. Take care that you don’t accidentally overwrite important data. Also, as mentioned above, assuming the instance has correctly configured default data and log paths when it does not could mean a database is unintentionally restored to the C:\ drive, which brings the possibility of the drive filling and bringing the server down. As always, anything you build should be built in a test environment with unimportant data and the results should be reviewed before deploying to production.

Recommendations

This is a simple recommendation, use this script for any of the above scenarios as well as others I didn’t cover but are referenced in the documentation. The scope of this command is very narrow in that it deals solely with restoring a database from various sources and to various targets and the modifications possible in the restore command, and it does that well. If you have regularly scheduled database refreshes that are done manually or require manual intervention, take some time with this script to automate it to save yourself some time!

Conclusion

Restore-DbaDatabase is a very useful tool, especially since restoring a database is a relatively common task, at the very least for verifying backups are good – although my next post there will be an even better tool for that purpose. This tool makes it relatively easy to restore a database with custom modifications and targets that work seamlessly in most scenarios. If you are spending more than a few minutes per week restoring databases, and plan on doing the same or more in the future, Restore-DbaDatabase will be your time-saving hero!