Restoring SQL databases with multiple data files fails

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Matt Bloomfield Matt Bloomfield 3 months, 1 week ago.

  • Author
    Posts
  • #60273
    Profile photo of Mark Dickinson
    Mark Dickinson
    Participant

    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
    $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)
  • #60286
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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

You must be logged in to reply to this topic.