dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Restore-DbaDatabase

This command scans a set of backup files to build up a restore plan, and then restores this to the SQL Server of the user’s choice. It was first released on 14 Feb 2017 in release “snowball”, along with an accompanying in-depth blog post.

Easy and flexible

We wanted to ensure that this command as as easy and powerful to use as possible. Options for the restore address a number of common scenarios and include:

  • Restore to latest point available or to chosen point in time
  • Relocate restored database files
  • Restore as a different database name
  • Files can be scanned from a normal directory, an Ola Hallengren style backup folder, using SQL Server’s own xp_dirtree, or the user can pipe in their own filelist.
  • Recovery and NoRecovery: the database can be recovered after the final restore or left for more files to be restored.
  • Renaming logical files.

Restore-DbaDatabase in action

Parameters

All of these options are exposed via these parameters:

SqlServer

The SQL Server instance.

SqlCredential

Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted.

Path

Path to SQL Server backup files.

Paths passed in as strings will be scanned using the desired method, default is a non recursive folder scan
Accepts multiple paths seperated by ‘,’

Or it can consist of FileInfo objects, such as the output of Get-ChildItem or Get-Item. This allows you to work with
your own filestructures as needed

DestinationDataDirectory

Path to restore the SQL Server backups to on the target instance.
If only this parameter is specified, then all database files (data and log) will be restored to this location

DestinationLogDirectory

Path to restore the database log files to.
This parameter can only be specified alongside DestinationDataDirectory.

DestinationFilePrefix

This value will be prefixed to ALL restored files (log and data). This is just a simple string prefix. If you
want to perform more complex rename operations then please use the FileMapping parameter

This will apply to all file move options, except for FileMapping

UseDestinationDefaultDirectories

Switch that tells the restore to use the default Data and Log locations on the target server

RestoreTime

Specify a DateTime object to which you want the database restored to. Default is to the latest point available

MaintenanceSolutionBackup

Switch to indicate the backup files are in a folder structure as created by Ola Hallengreen’s maintenance scripts.
This swith enables a faster check for suitable backups. Other options require all files to be read first to ensure
we have an anchoring full backup. Because we can rely on specific locations for backups performed with OlaHallengren’s
backup solution, we can rely on file locations.

DatabaseName

Name to restore the database under

NoRecovery

Indicates if the database should be recovered after last restore. Default is to recover

WithReplace

Switch indicated is the restore is allowed to replace an existing database.

OutputScriptOnly

Switch indicates that ONLY T-SQL scripts should be generated, no restore takes place

VerifyOnly

Switch indicate that restore should be verified

XpDirTree

Switch that indicated file scanning should be performed by the SQL Server instance using xp_dirtree
This will scan recursively from the passed in path
You must have sysadmin role membership on the instance for this to work.

FileMapping

A hashtable that can be used to move specific files to a location.
$FileMapping = @{‘DataFile1’=’c:\restoredfiles\Datafile1.mdf’;’DataFile3’=’d:\DataFile3.mdf’}
And files not specified in the mapping will be restore to their original location
This Parameter is exclusive with DestinationDataDirectory

IgnoreLogBackup

This switch tells the function to ignore transaction log backups. The process will restore to the latest full or differential backup point only

ReuseSourceFolderStructure

By default, databases will be migrated to the destination Sql Server’s default data and log directories. You can override this by specifying -ReuseSourceFolderStructure.
The same structure on the SOURCE will be kept exactly, so consider this if you’re migrating between different versions and use part of Microsoft’s default Sql structure (MSSql12.INSTANCE, etc)

*Note, to reuse destination folder structure, specify -WithReplace

Confirm

Prompts to confirm certain actions

WhatIf

Shows what would happen if the command would execute, but does not actually perform the command

Examples

Scans all the backup files in \server2\backups$, filters them and restores the database to server1\instance1

Scans all the backup files in \server2\backups$ stored in an Ola Hallengreen style folder structure,
filters them and restores the database to the c:\restores folder on server1\instance1

Takes the provided files from multiple directories and restores them on server1\instance1

Scans all the backup files in \server2\backups$ stored in an Ola Hallengreen style folder structure,
filters them and restores the database to the c:\restores folder on server1\instance1 up to 11:19 23/12/2016

This will take all of the files found under the folders c:\backups and \server1\backups, and pipeline them into
Restore-DbaDatabase. Restore-DbaDatabase will then scan all of the files, and restore all of the databases included
to the latest point in time covered by their backups. All data and log files will be moved to the default SQL Sever
folder for those file types as defined on the target instance.

Scans all the backup files in \server2\backups$ stored in an Ola Hallengreen style folder structure,
filters them and restores the database to the c:\restores folder on server1\instance1 up to 11:19 23/12/2016, and then renames the logical files test_data to restored_data, and test_log to restored_log

Here we use the dbatools function Get-DbaBackupHistory to get the backup history of 2 databases on the ProdServer1 instance, pipeline the output to Restore-DbaDatabase and have them restored to the latest point in time into the d:\Staging folder.

Notes

Some of the restore file location mapping switches are mutually exclusive. Please refer to the table below for which ones can be used together

FileMapping DestinationDefault SourceLocations DestinationDataDirectory DestinationLogDirectory
Default n y n n n
y n n n n
n n y n n
n n n y n
n n n y y

Get-Help

From PowerShell, execute Get-Help Restore-DbaDatabase -Detailed for more information on this function.

Source Code

Want to see the source code? View Restore-DbaDatabase.ps1 on GitHub
 

Related commands