How to Filter and Restore Specific SQL Database Backups Using dbatools in PowerShell
1
vote
1
answer
285
views
I'm working with a directory of mixed SQL Server backup files and need to restore a specific database using dbatools in PowerShell. Below is the directory structure of my backup folder:
PS C:\Users\administrator> tree \\dc1\backup /F
Folder PATH listing
\\DC1\BACKUP.
│ Hadr_endpoint_cert.cer
│ Hadr_endpoint_cert.pvk
│
└───NODE1
└───MSSQLSERVER
├───AdventureWorks2016
│ ├───Full
│ │ Full_AdventureWorks2016_20240219011641.bak
│ │
│ └───Log
│ Log_AdventureWorks2016_20240219011647.trn
│
└───Credit
├───Full
│ Full_Credit_20240219012730.bak
│
└───Log
Log_Credit_20240219012735.trn
PS C:\Users\administrator>
When attempting to restore the Credit database onto node2
with the following command, I encounter issues:
$files = Get-ChildItem -Path \\dc1\backup -Recurse -Include *.trn,*.bak
Restore-DbaDatabase -SqlInstance node2 -Path $files -DatabaseName Credit -NoRecovery -ExecuteAs sa
This results in the error:
WARNING: [01:27:36][Restore-DbaDatabase] Multiple Databases' backups passed in, but only 1 name to restore them under. Stopping as cannot work out how to proceed.
Using this command instead:
Restore-DbaDatabase -SqlInstance node2 -Path \\dc1\backup -DirectoryRecurse -DatabaseName Credit
Leads to a different error:
WARNING: [01:32:00][Get-DbaBackupInformation] Failure on node2 | The media family on device '\\dc1\backup\Hadr_endpoint_cert.cer' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally.
I understand that I can manually select files that are related to the Credit database by their naming convention, but what if the backup files do not have the database name in their filename? What would be the best approach to automate this restoration while filtering out unrelated files?
Any guidance or tips on the proper use of dbatools for this scenario would be greatly appreciated! Thank you in advance.
Asked by Just a learner
(2082 rep)
Feb 18, 2024, 05:50 PM
Last activity: Feb 19, 2024, 12:11 PM
Last activity: Feb 19, 2024, 12:11 PM