0

I am trying to write a script which restores the database from remote machine to a new SQL Server. My old SQL Server has data drive as volume D: and new SQL Server has data drive as E: so can someone help me in restoring the database from PS to other drive location?

Backup is placed inside new SQL Server E:\migration folder from where I am trying to restore it. Below is what I have tried which works well with same folder structure.

# Defining the server Name

Write-Host " Initiating the SQL Server Install"
$Servers = Read-Host "Please specify DB Server Name"

Invoke-Command -ComputerName $Servers{

$INSTANCENAME = Read-Host "Please specify DB Server Instance Name , ex:- DBXXXXINST01"
$VOLNAME = Read-Host "Please specify DB Server InstanceVolume Name , ex:- XXXXINST01"

$ServerInstance = Read-Host "Please specify DB Server Instance Name , ex:- dbxx\DBXXXXINST01"

import-module SQLPS -DisableNameChecking

Write-Host " Initiating the DB creation/configuration"
Write-Host "Creating SQL folder"

$setupFolder = "E:\$VOLNAME\SQL"

New-Item -Path $setupFolder -type directory -Force 

$backupRoot = Get-ChildItem -Path "E:\migration\DB_Backup\1234inst01\"
$datafilesDest = "E:\$VOLNAME\SQL\"
$logfilesDest = "E:\$VOLNAME\SQL\"

## For each folder in the backup root directory...
#
foreach($folder in $backupRoot)
{   
    # Get the most recent .bak files for all databases...
    $backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*.bak" -Recurse | Sort-Object -Property CreationTime -Descending | Select-Object -First 1

    # For each .bak file...
    foreach ($backupFile in $backupFiles)
    {
        # Restore the header to get the database name...
        $query = "RESTORE HEADERONLY FROM DISK = N'"+$backupFile.FullName+"'"
        $headerInfo = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query
        $databaseName = $headerInfo.DatabaseName

        # Restore the file list to get the logical filenames of the database files...
        $query = "RESTORE FILELISTONLY FROM DISK = N'"+$backupFile.FullName+"'"
        $files = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query

        # Differentiate data files from log files...
        $dataFile = $files | Where-Object -Property Type -EQ "D"
        $logFile = $files | Where-Object -Property Type -EQ "L"

        # Set some variables...
        $dataFileName = $dataFile.LogicalName
        $logFileName = $logFile.LogicalName

        # Set the destination of the restored files...
        $dataFileFullPath = $datafilesDest+"\"+$dataFileName+".mdf"
        $logFileFullPath = $logfilesDest+"\"+$logFileName+".ldf"

        # Perform the database restore... and then go around the loop.
        Restore-SqlDatabase -ServerInstance $ServerInstance -Database $databaseName -BackupFile $backupFile.FullName -ReplaceDatabase
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Abhishek
  • 347
  • 1
  • 7
  • 21

1 Answers1

1

You should be able to use the RelocateFile option of Restore-SqlDatabase to achieve this.

I haven't tested this but if you are happy with the new physical file names your question code constructs this should work:

$relocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dataFile.LogicalName, $dataFileFullPath)
$relocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logFile.LogicalName, $logFileFullPath)

Restore-SqlDatabase -ServerInstance $ServerInstance -Database $databaseName -BackupFile $backupFile.FullName -ReplaceDatabase -RelocateFile @($relocateData, $relocateLog)

Edit:

Depending on your configuration of powershell sql modules, this might fail if powershell instantiates the RelocateFile objects as the wrong versions.

Fix is to force the versions to match by checking the assembly version that implements the Restore-SqlDatabase command.

Peter Wishart
  • 11,600
  • 1
  • 26
  • 45
  • I tried this and i am getting Cannot bind parameter 'RelocateFile'. Cannot convert the "$RelocateData" value of type "System.String" to type "Microsoft.SqlServer.Management.Smo.RelocateFile". + CategoryInfo : InvalidArgument: (:) [Restore-SqlDatabase], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand.. Not sure where i am missing with logic .. Since i am trying to do this remotely so it shld work in an ideal case.. – Abhishek May 27 '19 at 07:15
  • The error you get indicates a problem passing the `-RelocateFile` parameter properly. Try setting this to a variable, then check the variable's type: `$rf = @($relocateData, $RelocateLog); $rf | get-member` - should return `TypeName: Microsoft.SqlServer.Management.Smo.RelocateFile` – Peter Wishart May 27 '19 at 08:54
  • I already checked that and type name is same as what you mentioned.. even after placing it into variable i am getting same issue :- Cannot bind parameter 'RelocateFile'. Cannot convert the "Microsoft.SqlServer.Management.Smo.RelocateFile" value of type "Microsoft.SqlServer.Management.Smo.RelocateFile" to type "Microsoft.SqlServer.Management.Smo.RelocateFile". FYI.. this is sql 2016 – Abhishek May 27 '19 at 09:02
  • The answer code works in a SQL 2017 container. There's answer on dba stackexchange for 2016 that suggests [restoring files separately](https://dba.stackexchange.com/a/170707) – Peter Wishart May 27 '19 at 09:12
  • I also found a related problem about versioning of the `RelocateFile` objects, your latest error sounds more like that one. – Peter Wishart May 27 '19 at 09:26