0

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!

  • 1
    I'm just going to give a word of warning here - by not having the files in the same location on the secondary as they are on the primary, should you add a new file on that primary then the AG will break until such time as you perform another backup and restore. – Nic Apr 10 '17 at 16:18
  • Nic, thanks for your imput. Perhaps i failed to elaborate, but what i'm trying to achieve is adding a database to AG. And the problem is that the local paths (drives) differ, and can't be the same, unfortunately. – Arseny V. Apr 10 '17 at 16:28
  • 1
    I understand your need, I just want to ensure that you are aware of the risk involved in not having common file locations. – Nic Apr 10 '17 at 16:36
  • 1
    As regards the issue itself, I'm not sure why you are creating a database on the secondary at all. Just restore the database from the backup with the relocate file commands. – Nic Apr 10 '17 at 16:40
  • 1
    Don't reinvent the wheel. Look into DBATools.io as it's a great PowerShell cmdlet library that does this and more. – John Eisbrener Apr 10 '17 at 18:13
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.