Sometimes you need a restore script. You may be sending a copy of a backup somewhere and need the restore script to go with it. Maybe you have a requirement to document any restores done. I know I like to see what the restore is going to do before I run it. A -WhatIf for the restore if you will. Of course the tools I’ll be showing you are part of the dbatools module.

We’ll start by using Get-DbaDbBackupHistory. This cmmdlet make life sooooo much easier when it comes to restoring a database. Let’s run it against a database with just the instance and database name.

    
        
Get-DbaDbBackupHistory -SqlInstance SQL01 -Database CustomerDB
    
 
     
        
SqlInstance Database  Type          TotalSize DeviceType Start                   Duration End                    
----------- --------  ----          --------- ---------- -----                   -------- ---                    
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-14 19:11:10.000 00:00:00 2021-06-14 19:11:10.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-13 19:09:47.000 00:00:00 2021-06-13 19:09:47.000
SQL01       CustomerDB Full         766.08 MB Disk       2021-06-12 09:35:12.000 00:00:01 2021-06-12 09:35:13.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-11 19:15:07.000 00:00:00 2021-06-11 19:15:07.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-10 19:14:35.000 00:00:00 2021-06-10 19:14:35.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-09 19:16:58.000 00:00:00 2021-06-09 19:16:58.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-06-08 19:16:22.000 00:00:00 2021-06-08 19:16:22.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-06-07 19:17:06.000 00:00:01 2021-06-07 19:17:07.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-06-06 19:10:40.000 00:00:00 2021-06-06 19:10:40.000
SQL01       CustomerDB Full         766.08 MB Disk       2021-06-05 09:31:02.000 00:00:02 2021-06-05 09:31:04.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-04 19:18:25.000 00:00:00 2021-06-04 19:18:25.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-03 19:20:07.000 00:00:01 2021-06-03 19:20:08.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-06-02 19:14:31.000 00:00:00 2021-06-02 19:14:31.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-06-01 19:16:22.000 00:00:02 2021-06-01 19:16:24.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-05-31 19:10:59.000 00:00:01 2021-05-31 19:11:00.000
SQL01       CustomerDB Differential 1.08 MB   Disk       2021-05-30 19:10:16.000 00:00:00 2021-05-30 19:10:16.000
...
 
    
You get back all the available backups in the backup history. This information is pulled from the msdb.backupset table. The real beauty of this tools is the -Last switch. This will output the chain of backups needed to get to current. My example database is in Simple Recovery so there are no transaction logs shown. Let’s take a look.

    
        
Get-DbaDbBackupHistory -SqlInstance SQL01 -Database CustomerDB -Last
    

 
     
        
SqlInstance Database   Type         TotalSize DeviceType Start                   Duration End                    
----------- --------   ----         --------- ---------- -----                   -------- ---                    
SQL01       CustomerDB Full         766.08 MB Disk       2021-06-12 09:35:12.000 00:00:01 2021-06-12 09:35:13.000
SQL01       CustomerDB Differential 2.08 MB   Disk       2021-06-14 19:11:10.000 00:00:00 2021-06-14 19:11:10.000
 
    
When I ran this on June 15th, the closest to current I could get was the full from the 12th followed by a differential from the 14th. This is all well and good Jeff, but we’re here for the restore script.

The next piece of the puzzle it the Restore-DbaDatabase cmdlet and the -OutputScriptOnly switch.

    
        
Get-DbaDbBackupHistory -SqlInstance SQL01 -Database CustomerDB -Last | Restore-DbaDatabase -SqlInstance SQL01 -DatabaseName CustomerDB -WithReplace -OutputScriptOnly
    

    
        
RESTORE DATABASE [CustomerDB] FROM  DISK = N'D:\MountPoints\Mp_Backup_1\SQLBackup\UserDBs\Full\SQL01_Full_CustomerDB_20210612093510_1.bak' WITH  FILE = 1,  MOVE N'CustomerDB' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\CustomerDB.mdf',  MOVE N'CustomerDB_log' TO N'C:\ClusterStorage\20850_3_MP_Log_1\SQLLog\CustomerDB.ldf',  MOVE N'fCustomerDBdef1' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef1.ndf',  MOVE N'fCustomerDBdef2' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef2.ndf',  MOVE N'fCustomerDBdef3' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef3.ndf',  MOVE N'fCustomerDBdef4' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef4.ndf',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
RESTORE DATABASE [CustomerDB] FROM  DISK = N'D:\MountPoints\Mp_Backup_4\SQLBackup\UserDBs\Diff\SQL01_Diff_CustomerDB_20210614191109_1.bak' WITH  FILE = 1,  MOVE N'CustomerDB' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\CustomerDB.mdf',  MOVE N'CustomerDB_log' TO N'C:\ClusterStorage\20850_3_MP_Log_1\SQLLog\CustomerDB.ldf',  MOVE N'fCustomerDBdef1' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef1.ndf',  MOVE N'fCustomerDBdef2' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef2.ndf',  MOVE N'fCustomerDBdef3' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef3.ndf',  MOVE N'fCustomerDBdef4' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef4.ndf',  NOUNLOAD,  REPLACE,  STATS = 10
    
This is a valid script, assuming your SQL instance has access to the D: drive, but it’s not very human readable. Let’s assign the results to a variable so we can clean it up easier.

    
        
$script = Get-DbaDbBackupHistory -SqlInstance SQL01 -Database CustomerDB -Last | Restore-DbaDatabase -SqlInstance SQL01 -DatabaseName CustomerDB -WithReplace -OutputScriptOnly
$script.replace("FROM","`nFROM").replace("WITH","`nWITH").replace(",","`n,")
    
Multiple .replace() methods can be used on the object. The `n is a PowerShell command for the special character “newline”. Check out about Special Characters for more information.

    
        
RESTORE DATABASE [CustomerDB]
FROM  DISK = N'D:\MountPoints\Mp_Backup_1\SQLBackup\UserDBs\Full\SQL01_Full_CustomerDB_20210612093510_1.bak'
WITH  FILE = 1
,  MOVE N'CustomerDB' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\CustomerDB.mdf'
,  MOVE N'CustomerDB_log' TO N'C:\ClusterStorage\20850_3_MP_Log_1\SQLLog\CustomerDB.ldf'
,  MOVE N'fCustomerDBdef1' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef1.ndf'
,  MOVE N'fCustomerDBdef2' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef2.ndf'
,  MOVE N'fCustomerDBdef3' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef3.ndf'
,  MOVE N'fCustomerDBdef4' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef4.ndf'
,  NORECOVERY
,  NOUNLOAD
,  REPLACE
,  STATS = 10
RESTORE DATABASE [CustomerDB]
FROM  DISK = N'D:\MountPoints\Mp_Backup_4\SQLBackup\UserDBs\Diff\SQL01_Diff_CustomerDB_20210614191109_1.bak'
WITH  FILE = 1
,  MOVE N'CustomerDB' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\CustomerDB.mdf'
,  MOVE N'CustomerDB_log' TO N'C:\ClusterStorage\20850_3_MP_Log_1\SQLLog\CustomerDB.ldf'
,  MOVE N'fCustomerDBdef1' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef1.ndf'
,  MOVE N'fCustomerDBdef2' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef2.ndf'
,  MOVE N'fCustomerDBdef3' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef3.ndf'
,  MOVE N'fCustomerDBdef4' TO N'C:\ClusterStorage\20850_3_MP_Ptn_A1\SQLData\fCustomerDBdef4.ndf'
,  NOUNLOAD
,  REPLACE
,  STATS = 10
    
Now we have a very readable restore script. Notice line 10 where NORECOVERY was automatically added so our differential could be added next. All handled by the Restore-DbaDatabase cmdlet.

This is all based on having the database being backed up somewhere in your environment. If you are given a SQL backup file and need to create a script to restore it you have to take a slightly different approach. Where Get-DbaDbBackupHistory gets information from msdb, the Get-DbaBackupInformation gets information from the backup file itself. It can also traverse a directory recursively for backup files. Very powerful tool.

Using the full backup file from our last restore example, we’ll look at it with Get-DbaBackupInformation. This cmdlet needs a SQL instance to process the file. It doesn’t have to be the instance you plan on using for the restore.

 
     
        
Get-DbaBackupInformation -Path 'D:\Mountpoints\Mp_Backup_1\SQLBackup\UserDBs\Full\SQL01_Full_CustomerDB_20210612093510_1.bak' -SqlInstance SQL01

SqlInstance Database   Type     TotalSize DeviceType Start                   Duration End
----------- --------   ----     --------- ---------- -----                   -------- ---
SQL01       CustomerDB Database 766.08 MB Disk       2021-06-12 09:35:12.000 00:00:01 2021-06-12 09:35:13.000
 
    

Let’s assign the results to a variable and see what is available to us with Get-Member.

 
     
        
$bakfile = Get-DbaBackupInformation -Path 'D:\Mountpoints\Mp_Backup_1\SQLBackup\UserDBs\Full\SQL01_Full_CustomerDB_20210612093510_1.bak' -SqlInstance SQL01

$bakfile | Get-Member

   TypeName: Sqlcollaborative.Dbatools.Database.BackupHistory

Name                  MemberType Definition
----                  ---------- ----------
Equals                Method     bool Equals(System.Object obj)
GetHashCode           Method     int GetHashCode()
GetType               Method     type GetType()
ToString              Method     string ToString()
AvailabilityGroupName Property   string AvailabilityGroupName {get;set;}
BackupSetId           Property   string BackupSetId {get;set;}
CheckpointLsn         Property   bigint CheckpointLsn {get;set;}
CompressedBackupSize  Property   Sqlcollaborative.Dbatools.Utility.Size CompressedBackupSize {get;set;}
CompressionRatio      Property   double CompressionRatio {get;set;}
ComputerName          Property   string ComputerName {get;set;}
Database              Property   string Database {get;set;}
DatabaseBackupLsn     Property   bigint DatabaseBackupLsn {get;set;}
DeviceType            Property   string DeviceType {get;set;}
Duration              Property   Sqlcollaborative.Dbatools.Utility.DbaTimeSpan Duration {get;set;}
EncryptorThumbprint   Property   string EncryptorThumbprint {get;set;}
EncryptorType         Property   string EncryptorType {get;set;}
End                   Property   datetime End {get;set;}
FileList              Property   System.Object FileList {get;set;}
FirstLsn              Property   bigint FirstLsn {get;set;}
FullName              Property   string[] FullName {get;set;}
InstanceName          Property   string InstanceName {get;set;}
IsCopyOnly            Property   bool IsCopyOnly {get;set;}
KeyAlgorithm          Property   string KeyAlgorithm {get;set;}
LastLsn               Property   bigint LastLsn {get;set;}
LastRecoveryForkGUID  Property   guid LastRecoveryForkGUID {get;set;}
Path                  Property   string[] Path {get;set;}
Position              Property   int Position {get;set;}
RecoveryModel         Property   string RecoveryModel {get;set;}
Software              Property   string Software {get;set;}
SoftwareVersionMajor  Property   int SoftwareVersionMajor {get;set;}
SqlInstance           Property   string SqlInstance {get;set;}
Start                 Property   datetime Start {get;set;}
TotalSize             Property   Sqlcollaborative.Dbatools.Utility.Size TotalSize {get;set;}
Type                  Property   string Type {get;set;}
UserName              Property   string UserName {get;set;}
 
    
For this restore, I want to put the files somewhere else on the new server. The BackupHistory object has the FileList property that we will make use of for this.
 
     
        
$bakfile.FileList

Type LogicalName    PhysicalName
---- -----------    ------------
D    CustomerDB      C:\ClusterStorage\20850_3_Mp_Ptn_C1\SQLData\CustomerDB.mdf
D    fCustomerDBdef1 C:\ClusterStorage\20850_3_Mp_Ptn_C1\SQLData\fCustomerDBdef1.ndf
D    fCustomerDBdef2 C:\ClusterStorage\20850_3_Mp_Ptn_C2\SQLData\fCustomerDBdef2.ndf
D    fCustomerDBdef3 C:\ClusterStorage\20850_3_Mp_Ptn_C3\SQLData\fCustomerDBdef3.ndf
D    fCustomerDBdef4 C:\ClusterStorage\20850_3_Mp_Ptn_C4\SQLData\fCustomerDBdef4.ndf
L    CustomerDB_log  C:\ClusterStorage\20850_3_Mp_Log_2\SQLLog\CustomerDB.ldf
 
    
The Restore-DbaDatabase cmdlet can take a hash of files as a parameter. We’ll move all the files to a different drive and folder.
    
        
$filemap = @{
	'CustomerDB'='E:\dbs\CustomerDB.MDF';
	'CustomerDB_log'='F:\dbs\CustomerDB.ldf';
	'fCustomerDBdef1'='G:\dbs\fCustomerDBdef1.ndf';
	'fCustomerDBdef2'='H:\dbs\fCustomerDBdef2.ndf';
	'fCustomerDBdef3'='I:\dbs\fCustomerDBdef3.ndf';
	'fCustomerDBdef4'='J:\dbs\fCustomerDBdef4.ndf'
}
    
Now that I have my hash map, let’s see what that gives us.
    
        
# Using the same Restore-DbaDatabase syntax as before but with -FileMapping
# Notice this time we also piped the backup object as a variable instead of the 
# output of the command.
$RestoreScript = $bakfile | Restore-DbaDatabase -SqlInstance SQL01 -WithReplace -OutputScriptOnly -FileMapping $filemap
# Use .Replace() to clean up our output again.
$RestoreScript.Replace("FROM","`nFROM").Replace("WITH","`nWITH").Replace(",","`n,")
    
    
        
RESTORE DATABASE [CustomerDB]
FROM  DISK = N'D:\Mountpoints\Mp_Backup_1\SQLBackup\UserDBs\Full\SQL01_Full_CustomerDB_20210612093510_1.bak'
WITH  FILE = 1
,  MOVE N'CustomerDB' TO N'E:\dbs\CustomerDB.MDF'
,  MOVE N'fCustomerDBdef1' TO N'G:\dbs\fCustomerDBdef1.ndf'
,  MOVE N'fCustomerDBdef2' TO N'H:\dbs\fCustomerDBdef2.ndf'
,  MOVE N'fCustomerDBdef3' TO N'I:\dbs\fCustomerDBdef3.ndf'
,  MOVE N'fCustomerDBdef4' TO N'J:\dbs\fCustomerDBdef4.ndf'
,  MOVE N'CustomerDB_log' TO N'F:\dbs\CustomerDB.ldf'
,  NOUNLOAD
,  REPLACE
,  STATS = 10
    
There we have it. Restore from a file with moving all the files to their own drives. Be careful with the Restore-DbaDatabase cmdlet. If you do not add the -OutputScriptOnly switch, it will happily restore the database.

Happy scripting!