Restoring SQL databases with multiple data files fails

Welcome Forums General PowerShell Q&A Restoring SQL databases with multiple data files fails

This topic contains 1 reply, has 2 voices, and was last updated by

2 years, 2 months ago.

  • Author
  • #60273

    Points: 0
    Rank: Member

    I have a process in Powershell to restore SQL databases from BAK files. Everything is working great, with one exception. If the source database has more than 1 data file the restore fails.

    I know it's the way the restore code is written, but I don't know how to correct it to allow for multiple data (MDF, NDF) files.

    Any suggestions would be appreciated! 🙂

    Here's the relevant code...

    # Build the physical file names for the database copy
    $dbfile = $fileloc + '\'+ $dbname + '_Data.mdf'
    $logfile = $logloc + '\'+ $dbname + '_Log.ldf'
    # Use the backup file name to create the backup device
    $bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($bckfile, 'File')
    # Create the new restore object, set the database name and add the backup device
    $rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')
    $rs.Database = $dbname
    #settings for restore
    $rs.NoRecovery = $false
    $rs.ReplaceDatabase = $true
    # Get the file list info from the backup file
    $fl = $rs.ReadFileList($srv)
    foreach ($fil in $fl) {
        $rsfile = new-object('Microsoft.SqlServer.Management.Smo.RelocateFile')
        $rsfile.LogicalFileName = $fil.LogicalName
        if ($fil.Type -eq 'D'){
            $rsfile.PhysicalFileName = $dbfile
        else {
            $rsfile.PhysicalFileName = $logfile
    # Restore the database
  • #60286

    Points: 41
    Rank: Member

    I can't answer your specific question but with SQL queries (no pun intended) I usually head straight for Chrissy Le Maire's blog.

    I found a function that's part of the project that will point you in the right direction:
    Restore-Database.ps1 on

The topic ‘Restoring SQL databases with multiple data files fails’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort