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!

Improve this question
6
  • 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, 2017 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, 2017 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, 2017 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, 2017 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, 2017 at 18:13

1 Answer 1

Reset to default
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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