7 minutes
Generate Restore Script with Dbatools

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!

Comments powered by Talkyard.