Restoring SQL databases with multiple data files fails

PowerShell Help
#1

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! :slight_smile:

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
$rs.Devices.Add($bdi)

#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
        }
    $rs.RelocateFiles.Add($rsfile)
    }

# Restore the database
$rs.SqlRestore($srv)
#2

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 dbatools.io project that will point you in the right direction:
Restore-Database.ps1 on dbatools.io