Auto restore multiple databases in One shot using powershell

Below script helps restore several databases on to a SQL Server instance in one go. We just have to place all the .bak files in a folder and execute the script. before running the script, its required to import SQLPS in the powershell prompt as below.

Save below script as restore_all.ps1

Execute the script like in below example.

Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

4 thoughts on “Auto restore multiple databases in One shot using powershell”

  1. Hi Sherbaz,
    Am getting the beolw error
    PS C:\Users\dkolad> Import-Module SQLPS -DisableNameChecking
    Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be
    loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more detai
    At line:1 char:14
    + Import-Module <<<< SQLPS -DisableNameChecking
    + CategoryInfo : NotSpecified: (:) [Import-Module], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException,Microsoft.PowerShell.Commands.ImportModuleCommand

  2. Thanks Sherbaz,
    But now am getting the below error
    Restore-SqlDatabase : Cannot bind parameter ‘RelocateFile’. Cannot convert the “Microsoft.SqlServer.Management.Smo.Relo
    cateFile” value of type “Microsoft.SqlServer.Management.Smo.RelocateFile” to type “Microsoft.SqlServer.Management.Smo.R
    At C:\Users\dkolad\Desktop\restore_all.ps1:156 char:103
    + Restore-SqlDatabase -ServerInstance $sqlserver -Database $dbname -BackupFile $bkfilepath -RelocateFile <<<< $rfl -No
    + CategoryInfo : InvalidArgument: (:) [Restore-SqlDatabase], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatab

Leave a Reply