Powershell - Restore Database with Multiple Backup Files

  • Hello,

    I have a requirement to restore database using powershell with multiple backup files.

    I think the below query prints out what needs to be executed but i am unsure of how to use invoke-sqlcmd and execute everything together.

    Appreciate your help

    $backupRoot = Get-ChildItem -Path "\\<network shared drive\"

    $dbname = 'Database1'

    $server = 'server1'

    $query0 = "USE [master] RESTORE DATABASE $dbname"

    write-host $query0

    foreach($folder in $backupRoot)


    # Get the most recent .bak files for all databases...

    $backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*$dbname*.bak" -Recurse | Sort-Object -Property CreationTime | Select-Object -First 1

    # For each .bak file...

    foreach ($backupFile in $backupFiles)


    $query1 = "FROM DISK = N'"+$backupFile.FullName+"'"

    write-host $query1

    #Invoke-Sqlcmd -ServerInstance $server -Query $query



    $query2 = "WITH FILE = 1,

    MOVE N'INR_Data' TO N'G:\Database\$dbname.mdf', MOVE N'INR_Log' TO N'L:\Log\$dbname.ldf',



    write-host $query2

  • In this day and age I wouldn't even consider writing my own routines for SQL in PowerShell, I'd just immediately go for DBATools instead, as there is no point re-inventing the wheel for something which already does nearly everything you could want.

    dbatools docs | Restore-DbaDatabase

    Restore-DbaDatabase -SqlInstance server\instance1 -Path c:\backups -DatabaseName example1
  • indeed, nowadays DBATOOLS is the way to go!



    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply