SQL Server 2016 PowerShell – Restore-SqlDatabase with ReplaceDatabase and RelocateFile

powershellrestoresql servert-sql

We have a several-node SQL Server 2016 AlwaysOn cluster, and have a need to move databases from one node/Availability Group to another quite often, so I'm trying to automate the process.

To add database to secondary replica, I need to prepare it first, i.e. backup primary, create new one on secondary, restore backup to secondary. The problem is that often the paths (local drives) do not match, for instance:

  • PRIMARY: D:\Databases\db.mdf, D:\Logs\db_log.ldf
  • SECONDARY: E:\Databases\db.mdf, E:\Logs\db_log.ldf

I'm trying to write a PoSh script, assuming that I have to create new database on SECONDARY server first, and came up with the following:

$SQLQuery = "CREATE DATABASE [$Database] ON PRIMARY ( NAME = N'$Database', FILENAME = N'$DestinationDatabaseFile' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB ) LOG ON ( NAME = N'$LogName', FILENAME = N'$DestinationLogFile' , SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB )"
Invoke-Sqlcmd2 -ServerInstance $DestinationServer -Database "master" -Query $SQLQuery

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, $DestinationDatabaseFile)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogName, $DestinationLogFile)

Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $LogBackupFile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData,$RelocateLog)

As far as I understand on restore I need to relocate the files ( -RelocateFile ), and also replace the database ( -ReplaceDatabase ). The error I'm getting is:

Restore-SqlDatabase : System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'db' database.

Servers are of the same version, databases of same compatibility levels.

Perhaps somebody could suggest a way around?

Thanks!

Best Answer

The following worked:

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, $DestinationDatabaseFile)
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($LogName, $DestinationLogFile)
Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $DatabaseBackupFile -ReplaceDatabase -NoRecovery -RelocateFile @($RelocateData,$RelocateLog)
Restore-SqlDatabase -ServerInstance $DestinationServer -Database $Database -BackupFile $LogBackupFile -RestoreAction 'Log' -NoRecovery