< Back
Restore-DbaDatabase
Post
NAME Restore-DbaDatabase
SYNOPSIS
Restores a SQL Server Database from a set of backup files
SYNTAX
Restore-DbaDatabase -SqlInstance <DbaInstanceParameter> [-SqlCredential <Pscredential>] -Path <System.Object[]>
[-DatabaseName <System.Object[]>] [-DestinationDataDirectory <String>] [-DestinationLogDirectory <String>]
[-DestinationFileStreamDirectory <String>] [-RestoreTime <Datetime>] [-NoRecovery <Switch>] [-WithReplace
<Switch>] [-KeepReplication <Switch>] [-XpDirTree <Switch>] [-OutputScriptOnly <Switch>] [-VerifyOnly <Switch>]
[-MaintenanceSolutionBackup <Switch>] [-FileMapping <Hashtable>] [-IgnoreLogBackup <Switch>]
[-UseDestinationDefaultDirectories <Switch>] [-ReuseSourceFolderStructure <Switch>] [-DestinationFilePrefix
<String>] [-RestoredDatabaseNamePrefix <String>] [-TrustDbBackupHistory <Switch>] [-MaxTransferSize <Int>]
[-BlockSize <Int>] [-BufferCount <Int>] [-DirectoryRecurse <Switch>] [-EnableException <Switch>]
[-StandbyDirectory <String>] [-Continue <Switch>] [-AzureCredential <String>] [-ReplaceDbNameInFile <Switch>]
[-DestinationFileSuffix <String>] [-KeepCDC <Switch>] [-GetBackupInformation <String>]
[-StopAfterGetBackupInformation <Switch>] [-SelectBackupInformation <String>] [-StopAfterSelectBackupInformation
<Switch>] [-FormatBackupInformation <String>] [-StopAfterFormatBackupInformation <Switch>] [-TestBackupInformation
<String>] [-StopAfterTestBackupInformation <Switch>] [-StatementTimeout <Int>] [<CommonParameters>]
Restore-DbaDatabase -SqlInstance <DbaInstanceParameter> [-SqlCredential <Pscredential>] -Path <System.Object[]>
[-DatabaseName <System.Object[]>] [-OutputScriptOnly <Switch>] [-TrustDbBackupHistory <Switch>] [-MaxTransferSize
<Int>] [-BlockSize <Int>] [-BufferCount <Int>] [-EnableException <Switch>] [-AzureCredential <String>]
[-GetBackupInformation <String>] [-StopAfterGetBackupInformation <Switch>] [-SelectBackupInformation <String>]
[-StopAfterSelectBackupInformation <Switch>] [-FormatBackupInformation <String>]
[-StopAfterFormatBackupInformation <Switch>] [-TestBackupInformation <String>] [-StopAfterTestBackupInformation
<Switch>] -PageRestore <System.Object> -PageRestoreTailFolder <String> [-StatementTimeout <Int>]
[<CommonParameters>]
Restore-DbaDatabase -SqlInstance <DbaInstanceParameter> [-SqlCredential <Pscredential>] [-DatabaseName
<System.Object[]>] [-OutputScriptOnly <Switch>] [-EnableException <Switch>] [-AzureCredential <String>] [-Recover
<Switch>] [-GetBackupInformation <String>] [-StopAfterGetBackupInformation <Switch>] [-SelectBackupInformation
<String>] [-StopAfterSelectBackupInformation <Switch>] [-FormatBackupInformation <String>]
[-StopAfterFormatBackupInformation <Switch>] [-TestBackupInformation <String>] [-StopAfterTestBackupInformation
<Switch>] [-StatementTimeout <Int>] [<CommonParameters>]
DESCRIPTION
Upon being passed a list of potential backups files this command will scan the files, select those that contain
SQL Server
backup sets. It will then filter those files down to a set that can perform the requested restore, checking that
we have a
full restore chain to the point in time requested by the caller.
The function defaults to working on a remote instance. This means that all paths passed in must be relative to the
remote instance.
XpDirTree will be used to perform the file scans
Various means can be used to pass in a list of files to be considered. The default is to non recursively scan the
folder
passed in.
PARAMETERS
-AzureCredential [<String>]
The name of the SQL Server credential to be used if restoring from an Azure hosted backup using Storage Access
Keys
If a backup path beginning http is passed in and this parameter is not specified then if a credential with a
name matching the URL
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BlockSize [<Int>]
Specifies the block size to use. Must be one of 0.5kb,1kb,2kb,4kb,8kb,16kb,32kb or 64kb
Can be specified in bytes
Refer to https://msdn.microsoft.com/en-us/library/ms178615.aspx for more detail
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BufferCount [<Int>]
Number of I/O buffers to use to perform the operation.
Refer to https://msdn.microsoft.com/en-us/library/ms178615.aspx for more detail
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Continue [<Switch>]
If specified we will to attempt to recover more transaction log backups onto database(s) in Recovering or
Standby states
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DatabaseName [<System.Object[]>]
Name to restore the database under.
Only works with a single database restore. If multiple database are found in the provided paths then we will
exit
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationDataDirectory [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationFilePrefix [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationFileStreamDirectory [<String>]
Path to restore FileStream data to
This parameter can only be specified alongside DestinationDataDirectory
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationFileSuffix [<String>]
This value will be suffixed to ALL restored files (log and data). This is just a simple string suffix. 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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationLogDirectory [<String>]
Path to restore the database log files to.
This parameter can only be specified alongside DestinationDataDirectory.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DirectoryRecurse [<Switch>]
If specified the specified directory will be recursed into
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FileMapping [<Hashtable>]
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 restored to their original location
This Parameter is exclusive with DestinationDataDirectory
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FormatBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Format-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GetBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Get-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IgnoreLogBackup [<Switch>]
This switch tells the function to ignore transaction log backups. The process will restore to the latest full
or differential backup point only
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KeepCDC [<Switch>]
Indicates whether CDC information should be restored as part of the database
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KeepReplication [<Switch>]
Indicates whether replication configuration should be restored as part of the database restore operation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaintenanceSolutionBackup [<Switch>]
Switch to indicate the backup files are in a folder structure as created by Ola Hallengreen's maintenance
scripts.
This switch 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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaxTransferSize [<Int>]
Parameter to set the unit of transfer. Values must be a multiple by 64kb
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoRecovery [<Switch>]
Indicates if the databases should be recovered after last restore. Default is to recover
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputScriptOnly [<Switch>]
Switch indicates that ONLY T-SQL scripts should be generated, no restore takes place
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PageRestore [<System.Object>]
Passes in an object from Get-DbaSuspectPages containing suspect pages from a single database.
Setting this Parameter will cause an Online Page restore if the target Instance is Enterprise Edition, or
offline if not.
This will involve taking a tail log backup, so you must check your restore chain once it has completed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PageRestoreTailFolder [<String>]
This parameter passes in a location for the tail log backup required for page level restore
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<System.Object[]>]
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 separated 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 file structures as needed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Recover [<Switch>]
If set will perform recovery on the indicated database
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ReplaceDbNameInFile [<Switch>]
If switch set and occurrence of the original database's name in a data or log file will be replace with the
name specified in the DatabaseName parameter
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RestoredDatabaseNamePrefix [<String>]
A string which will be prefixed to the start of the restore Database's Name
Useful if restoring a copy to the same sql server for testing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RestoreTime [<Datetime>]
Specify a DateTime object to which you want the database restored to. Default is to the latest point
available in the specified backups
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ReuseSourceFolderStructure [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SelectBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Select-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<DbaInstanceParameter>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StandbyDirectory [<String>]
If a directory is specified the database(s) will be restored into a standby state, with the standby file
placed into this directory (which must exist, and be writable by the target Sql Server instance)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StatementTimeout [<Int>]
Timeout in minutes. Defaults to infinity (restores can take a while.)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterFormatBackupInformation [<Switch>]
Switch which will cause the function to exit after returning FormatBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterGetBackupInformation [<Switch>]
Switch which will cause the function to exit after returning GetBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterSelectBackupInformation [<Switch>]
Switch which will cause the function to exit after returning SelectBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterTestBackupInformation [<Switch>]
Switch which will cause the function to exit after returning TestBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-TestBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Test-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-TrustDbBackupHistory [<Switch>]
This switch can be used when piping the output of Get-DbaDbBackupHistory or Backup-DbaDatabase into this
command.
It allows the user to say that they trust that the output from those commands is correct, and skips the file
header read portion of the process. This means a faster process, but at the risk of not knowing till halfway
through the restore that something is wrong with a file.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-UseDestinationDefaultDirectories [<Switch>]
Switch that tells the restore to use the default Data and Log locations on the target server. If they don't
exist, the function will try to create them
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-VerifyOnly [<Switch>]
Switch indicate that restore should be verified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-WithReplace [<Switch>]
Switch indicated is the restore is allowed to replace an existing database.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-XpDirTree [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: DisasterRecovery, Backup, Restore
Author: Stuart Moore (@napalmgram), stuart-moore.com
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups
Scans all the backup files in \\\\server2\\backups, filters them and restores the database to server1\\instance1
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups -MaintenanceSolutionBackup
-DestinationDataDirectory c:\\restores
Scans all the backup files in \\\\server2\\backups$ stored in an Ola Hallengren style folder structure,
filters them and restores the database to the c:\\restores folder on server1\\instance1
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-ChildItem c:\\SQLbackups1\\, \\\\server\\sqlbackups2 | Restore-DbaDatabase -SqlInstance server1\\instance1
Takes the provided files from multiple directories and restores them on server1\\instance1
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$RestoreTime = Get-Date('11:19 23/12/2016')
PS C:\\> Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups -MaintenanceSolutionBackup
-DestinationDataDirectory c:\\restores -RestoreTime $RestoreTime
Scans all the backup files in \\\\server2\\backups stored in an Ola Hallengren style folder structure,
filters them and restores the database to the c:\\restores folder on server1\\instance1 up to 11:19 23/12/2016
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>$result = Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups
-DestinationDataDirectory c:\\restores -OutputScriptOnly
PS C:\\> $result | Out-File -Filepath c:\\scripts\\restore.sql
Scans all the backup files in \\\\server2\\backups, filters them and generate the T-SQL Scripts to restore the
database to the latest point in time, and then stores the output in a file for later retrieval
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path c:\\backups -DestinationDataDirectory c:\\DataFiles
-DestinationLogDirectory c:\\LogFile
Scans all the files in c:\\backups and then restores them onto the SQL Server Instance server1\\instance1, placing
data files
c:\\DataFiles and all the log files into c:\\LogFiles
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path
http://demo.blob.core.windows.net/backups/dbbackup.bak -AzureCredential MyAzureCredential
Will restore the backup held at http://demo.blob.core.windows.net/backups/dbbackup.bak to server1\\instance1. The
connection to Azure will be made using the
credential MyAzureCredential held on instance Server1\\instance1
-------------------------- EXAMPLE 8 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path
http://demo.blob.core.windows.net/backups/dbbackup.bak
Will attempt to restore the backups from http://demo.blob.core.windows.net/backups/dbbackup.bak if a SAS
credential with the name http://demo.blob.core.windows.net/backups exists on server1\\instance1
-------------------------- EXAMPLE 9 --------------------------
PS C:\\>$File = Get-ChildItem c:\\backups, \\\\server1\\backups -recurse
PS C:\\> $File | Restore-DbaDatabase -SqlInstance Server1\\Instance -UseDestinationDefaultDirectories
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
Server
folder for those file types as defined on the target instance.
-------------------------- EXAMPLE 10 --------------------------
PS C:\\>$files = Get-ChildItem C:\\dbatools\\db1
PS C:\\> $params = @{
>> SqlInstance = 'server\\instance1'
>> DestinationFilePrefix = 'prefix'
>> DatabaseName ='Restored'
>> RestoreTime = (get-date "14:58:30 22/05/2017")
>> NoRecovery = $true
>> WithReplace = $true
>> StandbyDirectory = 'C:\\dbatools\\standby'
>> }
>>
PS C:\\> $files | Restore-DbaDatabase @params
PS C:\\> Invoke-DbaQuery -SQLInstance server\\instance1 -Query "select top 1 * from Restored.dbo.steps order by dt
desc"
PS C:\\> $params.RestoredTime = (get-date "15:09:30 22/05/2017")
PS C:\\> $params.NoRecovery = $false
PS C:\\> $params.Add("Continue",$true)
PS C:\\> $files | Restore-DbaDatabase @params
PS C:\\> Invoke-DbaQuery -SQLInstance server\\instance1 -Query "select top 1 * from restored.dbo.steps order by dt
desc"
PS C:\\> Restore-DbaDatabase -SqlInstance server\\instance1 -DestinationFilePrefix prefix -DatabaseName Restored
-Continue -WithReplace
In this example we step through the backup files held in c:\\dbatools\\db1 folder.
First we restore the database to a point in time in standby mode. This means we can check some details in the
databases
We then roll it on a further 9 minutes to perform some more checks
And finally we continue by rolling it all the way forward to the latest point in the backup.
At each step, only the log files needed to roll the database forward are restored.
-------------------------- EXAMPLE 11 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server\\instance1 -Path c:\\backups -DatabaseName example1 -NoRecovery
PS C:\\> Restore-DbaDatabase -SqlInstance server\\instance1 -Recover -DatabaseName example1
In this example we restore example1 database with no recovery, and then the second call is to set the database to
recovery.
-------------------------- EXAMPLE 12 --------------------------
PS C:\\>Get-DbaDbBackupHistory - SqlInstance server\\instance1 -Database ProdFinance -Last | Restore-DbaDatabase
-PageRestore
PS C:\\> $SuspectPage -PageRestoreTailFolder c:\\temp -TrustDbBackupHistory
Gets a list of Suspect Pages using Get-DbaSuspectPage. The uses Get-DbaDbBackupHistory and Restore-DbaDatabase to
perform a restore of the suspect pages and bring them up to date
If server\\instance1 is Enterprise edition this will be done online, if not it will be performed offline
-------------------------- EXAMPLE 13 --------------------------
PS C:\\>$BackupHistory = Get-DbaBackupInformation -SqlInstance sql2005 -Path \\\\backups\\sql2000\\ProdDb
PS C:\\> $BackupHistory | Restore-DbaDatabase -SqlInstance sql2000 -TrustDbBackupHistory
Due to SQL Server 2000 not returning all the backup headers we cannot restore directly. As this is an issues with
the SQL engine all we can offer is the following workaround
This will use a SQL Server instance > 2000 to read the headers, and then pass them in to Restore-DbaDatabase as a
BackupHistory object.
-------------------------- EXAMPLE 14 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path "C:\\Temp\\devops_prod_full.bak" -DatabaseName
"DevOps_DEV" -ReplaceDbNameInFile
PS C:\\> Rename-DbaDatabase -SqlInstance server1\\instance1 -Database "DevOps_DEV" -LogicalName "<DBN>_<FT>"
This will restore the database from the "C:\\Temp\\devops_prod_full.bak" file, with the new name "DevOps_DEV" and
store the different physical files with the new name. It will use the system default configured data and log
locations.
After the restore the logical names of the database files will be renamed with the "DevOps_DEV_ROWS" for MDF/NDF
and "DevOps_DEV_LOG" for LDF
-------------------------- EXAMPLE 15 --------------------------
PS C:\\>$FileStructure = @{
>> 'database_data' = 'C:\\Data\\database_data.mdf'
>> 'database_log' = 'C:\\Log\\database_log.ldf'
>> }
>>
PS C:\\> Restore-DbaDatabase -SqlInstance server1 -Path \\\\ServerName\\ShareName\\File -DatabaseName database
-FileMapping $FileStructure
Restores 'database' to 'server1' and moves the files to new locations. The format for the $FileStructure HashTable
is the file logical name as the Key, and the new location as the Value.
RELATED LINKS
https://dbatools.io/Restore-DbaDatabase
SYNOPSIS
Restores a SQL Server Database from a set of backup files
SYNTAX
Restore-DbaDatabase -SqlInstance <DbaInstanceParameter> [-SqlCredential <Pscredential>] -Path <System.Object[]>
[-DatabaseName <System.Object[]>] [-DestinationDataDirectory <String>] [-DestinationLogDirectory <String>]
[-DestinationFileStreamDirectory <String>] [-RestoreTime <Datetime>] [-NoRecovery <Switch>] [-WithReplace
<Switch>] [-KeepReplication <Switch>] [-XpDirTree <Switch>] [-OutputScriptOnly <Switch>] [-VerifyOnly <Switch>]
[-MaintenanceSolutionBackup <Switch>] [-FileMapping <Hashtable>] [-IgnoreLogBackup <Switch>]
[-UseDestinationDefaultDirectories <Switch>] [-ReuseSourceFolderStructure <Switch>] [-DestinationFilePrefix
<String>] [-RestoredDatabaseNamePrefix <String>] [-TrustDbBackupHistory <Switch>] [-MaxTransferSize <Int>]
[-BlockSize <Int>] [-BufferCount <Int>] [-DirectoryRecurse <Switch>] [-EnableException <Switch>]
[-StandbyDirectory <String>] [-Continue <Switch>] [-AzureCredential <String>] [-ReplaceDbNameInFile <Switch>]
[-DestinationFileSuffix <String>] [-KeepCDC <Switch>] [-GetBackupInformation <String>]
[-StopAfterGetBackupInformation <Switch>] [-SelectBackupInformation <String>] [-StopAfterSelectBackupInformation
<Switch>] [-FormatBackupInformation <String>] [-StopAfterFormatBackupInformation <Switch>] [-TestBackupInformation
<String>] [-StopAfterTestBackupInformation <Switch>] [-StatementTimeout <Int>] [<CommonParameters>]
Restore-DbaDatabase -SqlInstance <DbaInstanceParameter> [-SqlCredential <Pscredential>] -Path <System.Object[]>
[-DatabaseName <System.Object[]>] [-OutputScriptOnly <Switch>] [-TrustDbBackupHistory <Switch>] [-MaxTransferSize
<Int>] [-BlockSize <Int>] [-BufferCount <Int>] [-EnableException <Switch>] [-AzureCredential <String>]
[-GetBackupInformation <String>] [-StopAfterGetBackupInformation <Switch>] [-SelectBackupInformation <String>]
[-StopAfterSelectBackupInformation <Switch>] [-FormatBackupInformation <String>]
[-StopAfterFormatBackupInformation <Switch>] [-TestBackupInformation <String>] [-StopAfterTestBackupInformation
<Switch>] -PageRestore <System.Object> -PageRestoreTailFolder <String> [-StatementTimeout <Int>]
[<CommonParameters>]
Restore-DbaDatabase -SqlInstance <DbaInstanceParameter> [-SqlCredential <Pscredential>] [-DatabaseName
<System.Object[]>] [-OutputScriptOnly <Switch>] [-EnableException <Switch>] [-AzureCredential <String>] [-Recover
<Switch>] [-GetBackupInformation <String>] [-StopAfterGetBackupInformation <Switch>] [-SelectBackupInformation
<String>] [-StopAfterSelectBackupInformation <Switch>] [-FormatBackupInformation <String>]
[-StopAfterFormatBackupInformation <Switch>] [-TestBackupInformation <String>] [-StopAfterTestBackupInformation
<Switch>] [-StatementTimeout <Int>] [<CommonParameters>]
DESCRIPTION
Upon being passed a list of potential backups files this command will scan the files, select those that contain
SQL Server
backup sets. It will then filter those files down to a set that can perform the requested restore, checking that
we have a
full restore chain to the point in time requested by the caller.
The function defaults to working on a remote instance. This means that all paths passed in must be relative to the
remote instance.
XpDirTree will be used to perform the file scans
Various means can be used to pass in a list of files to be considered. The default is to non recursively scan the
folder
passed in.
PARAMETERS
-AzureCredential [<String>]
The name of the SQL Server credential to be used if restoring from an Azure hosted backup using Storage Access
Keys
If a backup path beginning http is passed in and this parameter is not specified then if a credential with a
name matching the URL
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BlockSize [<Int>]
Specifies the block size to use. Must be one of 0.5kb,1kb,2kb,4kb,8kb,16kb,32kb or 64kb
Can be specified in bytes
Refer to https://msdn.microsoft.com/en-us/library/ms178615.aspx for more detail
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-BufferCount [<Int>]
Number of I/O buffers to use to perform the operation.
Refer to https://msdn.microsoft.com/en-us/library/ms178615.aspx for more detail
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Continue [<Switch>]
If specified we will to attempt to recover more transaction log backups onto database(s) in Recovering or
Standby states
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DatabaseName [<System.Object[]>]
Name to restore the database under.
Only works with a single database restore. If multiple database are found in the provided paths then we will
exit
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationDataDirectory [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationFilePrefix [<String>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationFileStreamDirectory [<String>]
Path to restore FileStream data to
This parameter can only be specified alongside DestinationDataDirectory
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationFileSuffix [<String>]
This value will be suffixed to ALL restored files (log and data). This is just a simple string suffix. 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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DestinationLogDirectory [<String>]
Path to restore the database log files to.
This parameter can only be specified alongside DestinationDataDirectory.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DirectoryRecurse [<Switch>]
If specified the specified directory will be recursed into
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FileMapping [<Hashtable>]
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 restored to their original location
This Parameter is exclusive with DestinationDataDirectory
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-FormatBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Format-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GetBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Get-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-IgnoreLogBackup [<Switch>]
This switch tells the function to ignore transaction log backups. The process will restore to the latest full
or differential backup point only
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KeepCDC [<Switch>]
Indicates whether CDC information should be restored as part of the database
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KeepReplication [<Switch>]
Indicates whether replication configuration should be restored as part of the database restore operation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaintenanceSolutionBackup [<Switch>]
Switch to indicate the backup files are in a folder structure as created by Ola Hallengreen's maintenance
scripts.
This switch 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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MaxTransferSize [<Int>]
Parameter to set the unit of transfer. Values must be a multiple by 64kb
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoRecovery [<Switch>]
Indicates if the databases should be recovered after last restore. Default is to recover
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputScriptOnly [<Switch>]
Switch indicates that ONLY T-SQL scripts should be generated, no restore takes place
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PageRestore [<System.Object>]
Passes in an object from Get-DbaSuspectPages containing suspect pages from a single database.
Setting this Parameter will cause an Online Page restore if the target Instance is Enterprise Edition, or
offline if not.
This will involve taking a tail log backup, so you must check your restore chain once it has completed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PageRestoreTailFolder [<String>]
This parameter passes in a location for the tail log backup required for page level restore
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<System.Object[]>]
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 separated 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 file structures as needed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Recover [<Switch>]
If set will perform recovery on the indicated database
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ReplaceDbNameInFile [<Switch>]
If switch set and occurrence of the original database's name in a data or log file will be replace with the
name specified in the DatabaseName parameter
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RestoredDatabaseNamePrefix [<String>]
A string which will be prefixed to the start of the restore Database's Name
Useful if restoring a copy to the same sql server for testing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RestoreTime [<Datetime>]
Specify a DateTime object to which you want the database restored to. Default is to the latest point
available in the specified backups
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ReuseSourceFolderStructure [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SelectBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Select-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<DbaInstanceParameter>]
The target SQL Server instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StandbyDirectory [<String>]
If a directory is specified the database(s) will be restored into a standby state, with the standby file
placed into this directory (which must exist, and be writable by the target Sql Server instance)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StatementTimeout [<Int>]
Timeout in minutes. Defaults to infinity (restores can take a while.)
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterFormatBackupInformation [<Switch>]
Switch which will cause the function to exit after returning FormatBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterGetBackupInformation [<Switch>]
Switch which will cause the function to exit after returning GetBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterSelectBackupInformation [<Switch>]
Switch which will cause the function to exit after returning SelectBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-StopAfterTestBackupInformation [<Switch>]
Switch which will cause the function to exit after returning TestBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-TestBackupInformation [<String>]
Passing a string value into this parameter will cause a global variable to be created holding the output of
Test-DbaBackupInformation
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-TrustDbBackupHistory [<Switch>]
This switch can be used when piping the output of Get-DbaDbBackupHistory or Backup-DbaDatabase into this
command.
It allows the user to say that they trust that the output from those commands is correct, and skips the file
header read portion of the process. This means a faster process, but at the risk of not knowing till halfway
through the restore that something is wrong with a file.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-UseDestinationDefaultDirectories [<Switch>]
Switch that tells the restore to use the default Data and Log locations on the target server. If they don't
exist, the function will try to create them
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-VerifyOnly [<Switch>]
Switch indicate that restore should be verified
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-WithReplace [<Switch>]
Switch indicated is the restore is allowed to replace an existing database.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-XpDirTree [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: DisasterRecovery, Backup, Restore
Author: Stuart Moore (@napalmgram), stuart-moore.com
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups
Scans all the backup files in \\\\server2\\backups, filters them and restores the database to server1\\instance1
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups -MaintenanceSolutionBackup
-DestinationDataDirectory c:\\restores
Scans all the backup files in \\\\server2\\backups$ stored in an Ola Hallengren style folder structure,
filters them and restores the database to the c:\\restores folder on server1\\instance1
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-ChildItem c:\\SQLbackups1\\, \\\\server\\sqlbackups2 | Restore-DbaDatabase -SqlInstance server1\\instance1
Takes the provided files from multiple directories and restores them on server1\\instance1
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>$RestoreTime = Get-Date('11:19 23/12/2016')
PS C:\\> Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups -MaintenanceSolutionBackup
-DestinationDataDirectory c:\\restores -RestoreTime $RestoreTime
Scans all the backup files in \\\\server2\\backups stored in an Ola Hallengren style folder structure,
filters them and restores the database to the c:\\restores folder on server1\\instance1 up to 11:19 23/12/2016
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>$result = Restore-DbaDatabase -SqlInstance server1\\instance1 -Path \\\\server2\\backups
-DestinationDataDirectory c:\\restores -OutputScriptOnly
PS C:\\> $result | Out-File -Filepath c:\\scripts\\restore.sql
Scans all the backup files in \\\\server2\\backups, filters them and generate the T-SQL Scripts to restore the
database to the latest point in time, and then stores the output in a file for later retrieval
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path c:\\backups -DestinationDataDirectory c:\\DataFiles
-DestinationLogDirectory c:\\LogFile
Scans all the files in c:\\backups and then restores them onto the SQL Server Instance server1\\instance1, placing
data files
c:\\DataFiles and all the log files into c:\\LogFiles
-------------------------- EXAMPLE 7 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path
http://demo.blob.core.windows.net/backups/dbbackup.bak -AzureCredential MyAzureCredential
Will restore the backup held at http://demo.blob.core.windows.net/backups/dbbackup.bak to server1\\instance1. The
connection to Azure will be made using the
credential MyAzureCredential held on instance Server1\\instance1
-------------------------- EXAMPLE 8 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path
http://demo.blob.core.windows.net/backups/dbbackup.bak
Will attempt to restore the backups from http://demo.blob.core.windows.net/backups/dbbackup.bak if a SAS
credential with the name http://demo.blob.core.windows.net/backups exists on server1\\instance1
-------------------------- EXAMPLE 9 --------------------------
PS C:\\>$File = Get-ChildItem c:\\backups, \\\\server1\\backups -recurse
PS C:\\> $File | Restore-DbaDatabase -SqlInstance Server1\\Instance -UseDestinationDefaultDirectories
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
Server
folder for those file types as defined on the target instance.
-------------------------- EXAMPLE 10 --------------------------
PS C:\\>$files = Get-ChildItem C:\\dbatools\\db1
PS C:\\> $params = @{
>> SqlInstance = 'server\\instance1'
>> DestinationFilePrefix = 'prefix'
>> DatabaseName ='Restored'
>> RestoreTime = (get-date "14:58:30 22/05/2017")
>> NoRecovery = $true
>> WithReplace = $true
>> StandbyDirectory = 'C:\\dbatools\\standby'
>> }
>>
PS C:\\> $files | Restore-DbaDatabase @params
PS C:\\> Invoke-DbaQuery -SQLInstance server\\instance1 -Query "select top 1 * from Restored.dbo.steps order by dt
desc"
PS C:\\> $params.RestoredTime = (get-date "15:09:30 22/05/2017")
PS C:\\> $params.NoRecovery = $false
PS C:\\> $params.Add("Continue",$true)
PS C:\\> $files | Restore-DbaDatabase @params
PS C:\\> Invoke-DbaQuery -SQLInstance server\\instance1 -Query "select top 1 * from restored.dbo.steps order by dt
desc"
PS C:\\> Restore-DbaDatabase -SqlInstance server\\instance1 -DestinationFilePrefix prefix -DatabaseName Restored
-Continue -WithReplace
In this example we step through the backup files held in c:\\dbatools\\db1 folder.
First we restore the database to a point in time in standby mode. This means we can check some details in the
databases
We then roll it on a further 9 minutes to perform some more checks
And finally we continue by rolling it all the way forward to the latest point in the backup.
At each step, only the log files needed to roll the database forward are restored.
-------------------------- EXAMPLE 11 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server\\instance1 -Path c:\\backups -DatabaseName example1 -NoRecovery
PS C:\\> Restore-DbaDatabase -SqlInstance server\\instance1 -Recover -DatabaseName example1
In this example we restore example1 database with no recovery, and then the second call is to set the database to
recovery.
-------------------------- EXAMPLE 12 --------------------------
PS C:\\>Get-DbaDbBackupHistory - SqlInstance server\\instance1 -Database ProdFinance -Last | Restore-DbaDatabase
-PageRestore
PS C:\\> $SuspectPage -PageRestoreTailFolder c:\\temp -TrustDbBackupHistory
Gets a list of Suspect Pages using Get-DbaSuspectPage. The uses Get-DbaDbBackupHistory and Restore-DbaDatabase to
perform a restore of the suspect pages and bring them up to date
If server\\instance1 is Enterprise edition this will be done online, if not it will be performed offline
-------------------------- EXAMPLE 13 --------------------------
PS C:\\>$BackupHistory = Get-DbaBackupInformation -SqlInstance sql2005 -Path \\\\backups\\sql2000\\ProdDb
PS C:\\> $BackupHistory | Restore-DbaDatabase -SqlInstance sql2000 -TrustDbBackupHistory
Due to SQL Server 2000 not returning all the backup headers we cannot restore directly. As this is an issues with
the SQL engine all we can offer is the following workaround
This will use a SQL Server instance > 2000 to read the headers, and then pass them in to Restore-DbaDatabase as a
BackupHistory object.
-------------------------- EXAMPLE 14 --------------------------
PS C:\\>Restore-DbaDatabase -SqlInstance server1\\instance1 -Path "C:\\Temp\\devops_prod_full.bak" -DatabaseName
"DevOps_DEV" -ReplaceDbNameInFile
PS C:\\> Rename-DbaDatabase -SqlInstance server1\\instance1 -Database "DevOps_DEV" -LogicalName "<DBN>_<FT>"
This will restore the database from the "C:\\Temp\\devops_prod_full.bak" file, with the new name "DevOps_DEV" and
store the different physical files with the new name. It will use the system default configured data and log
locations.
After the restore the logical names of the database files will be renamed with the "DevOps_DEV_ROWS" for MDF/NDF
and "DevOps_DEV_LOG" for LDF
-------------------------- EXAMPLE 15 --------------------------
PS C:\\>$FileStructure = @{
>> 'database_data' = 'C:\\Data\\database_data.mdf'
>> 'database_log' = 'C:\\Log\\database_log.ldf'
>> }
>>
PS C:\\> Restore-DbaDatabase -SqlInstance server1 -Path \\\\ServerName\\ShareName\\File -DatabaseName database
-FileMapping $FileStructure
Restores 'database' to 'server1' and moves the files to new locations. The format for the $FileStructure HashTable
is the file logical name as the Key, and the new location as the Value.
RELATED LINKS
https://dbatools.io/Restore-DbaDatabase