EitanBlumin · January 14, 2021 14:38 · Apr 5, 2020 · Apr 5, 2020
diff --git a/Relocate AG Database Files.ps1 b/Relocate AG Database Files.ps1
@@ -14,7 +14,7 @@ $Sourcefolder = 'L:\LOG\'
 $LogFilePattern = $DBNamePattern + '.ldf'
 $DataFilePattern = $DBNamePattern + '.mdf'
 
-# Yes, this is in a console and doing evil console output stuff..... if this is made into a "real" function this would be removed
+# Yes, this is in a console and doing console output stuff..... if this is made into a "real" function this would be removed
 Clear-Host
 
 ############## Work actually begins here ##############

diff --git a/Relocate AG Database Files.ps1 b/Relocate AG Database Files.ps1
@@ -0,0 +1,123 @@
+############## Setup ##############
+
+$AGName = ''
+$AGPrimary = ''
+$AGSecondary = ''
+
+# This allows you to process just a subset of databases using the name (wildcards are possible)
+$DBNamePattern = 'AdventureW*'
+
+$TargetFolder = 'K:\Data\'
+$DBDataFolder = 'D:\Data\'
+$DBLogFolder = 'L:\LOG\'
+$Sourcefolder = 'L:\LOG\'
+$LogFilePattern = $DBNamePattern + '.ldf'
+$DataFilePattern = $DBNamePattern + '.mdf'
+
+# Yes, this is in a console and doing evil console output stuff..... if this is made into a "real" function this would be removed
+Clear-Host
+
+############## Work actually begins here ##############
+
+# We build a list of databases using the folder location, because we only want to move databases that are actually on the old storage. The filenames are also the names of the databases.
+$dbs = (Get-ChildItem -Path $SourceFolder* -Include $LogFilePattern ).BaseName
+
+# Tons of colourful output, because I like it when it runs for hours and I see something happening
+Write-Host "Beginning move of files for '$DBNamePattern' databases on $AGSecondary ($($dbs.Count) databases) " -ForegroundColor Green
+
+# Make sure the TargetFolder is there (using robocopy to copy folder permissions, becuase I don't know a concise way to do this in PoSh)
+if (!(Test-Path -PathType Container -Path $TargetFolder)) {
+
+	Write-Host "$Targetfolder missing, creating folder with correct permissions" -ForegroundColor Yellow
+    robocopy "$DBDataFolder" "$TargetFolder" /e /copyall /xf *
+}
+
+# Make sure AGSecondary can be read from (normally turned off, but we need to read from it for the move)
+Write-Host "Turning on readable secondary for AG '$AGName'" -ForegroundColor Blue
+Set-DbaAgReplica -SqlInstance $AGPrimary -AvailabilityGroup $AGname -ConnectionModeInSecondaryRole Yes -Replica $AGSecondary | Out-Null
+
+foreach ($DatabaseName in $dbs) {
+
+	Write-Host "Begin Database File Relocation for $DatabaseName in AG '$AGName'" -ForegroundColor Blue
+
+	# Read the locations of the Data and Log files on the secondary to see if we have to move anything
+    $DatabaseFiles = Get-DbaDbFile -SqlInstance $AGSecondary -Database $DatabaseName
+    $DatabaseLogFile = Get-DbaDbFile -SqlInstance $AGSecondary -Database $DatabaseName | Where-Object TypeDescription -EQ "LOG"
+    $SourceFolder = $DatabaseLogFile.PhysicalName.Substring(0, ($DatabaseLogFile.PhysicalName.LastIndexOf('\') + 1))
+
+    # Do we have work to do?
+    if ($SourceFolder -ne $TargetFolder) {
+
+		# Pause the AG for this DB 
+        Write-Host "Pausing data movement in AG '$AGName' for $DatabaseName" -ForegroundColor Blue
+        Suspend-DbaAgDbDataMovement -SqlInstance $AGSecondary -AvailabilityGroup $AGName -Database $DatabaseName -Confirm:$false | Out-Null
+
+        # Remove the DB from the AG on the secondary so we can modify file paths
+		# I know, "SET HADR OFF" scared me too, but it doesn't break the AG, I promise!
+		# This might be replaced by Remove-DbaAgReplica, but not sure about that
+        Write-Host "Removing database $DatabaseName from AG '$AGName'" -ForegroundColor Blue
+        Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] SET HADR OFF;"
+
+        # Update the metadata for the database file locations
+        Write-Host "Updating Database Metadata for $DatabaseName" -ForegroundColor Blue
+        foreach ($DatabaseFile in $DatabaseFiles) {
+            $OldLocation = $DatabaseFile.PhysicalName
+
+            $NewLocation = (Join-Path $TargetFolder ($DatabaseFile.PhysicalName -replace '.*\\'))
+
+            if ($OldLocation -ne $NewLocation) {
+                Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query "ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = $($DatabaseFile.LogicalName), FILENAME = '$NewLocation');"
+            }
+        }
+    }
+}
+
+# Offline the SQL Server (to release the file locks in the filesystem)
+Write-Host "Taking $AGSecondary offline" -ForegroundColor Blue
+Stop-DbaService $AGSecondary
+
+# Move the data files to the new location
+$dbdatafiles = Get-ChildItem -Path $DBDataFolder* -Include $DataFilePattern
+foreach ($file in $dbdatafiles) {
+
+	Write-Host "Moving $file" -ForegroundColor Blue
+
+    $OldLocation = Join-Path $DBDataFolder $file.Name
+    $NewLocation = Join-Path $TargetFolder $file.Name
+
+    if ($OldLocation -ne $NewLocation) {
+        Move-Item -Path $OldLocation -Destination $NewLocation
+    }
+}
+
+# Move the log files to the new location
+$dblogfiles = Get-ChildItem -Path $DBLogFolder* -Include $LogFilePattern
+foreach ($file in $dblogfiles) {
+
+    Write-Host "Moving $file" -ForegroundColor Blue
+
+    $OldLocation = Join-Path $DBLogFolder $file.Name
+    $NewLocation = Join-Path $TargetFolder $file.Name
+
+    if ($OldLocation -ne $NewLocation) {
+        Move-Item -Path $OldLocation -Destination $NewLocation
+    }
+}
+
+# Start SQL Server again
+Start-DbaService $AGSecondary
+
+# Give SQL Server a moment to start up / recover the databases
+Start-Sleep 10 
+
+# Add the databases back into the availability group
+foreach ($DatabaseName in $dbs) {
+
+    Write-Host "Bringing $DatabaseName back into Availability Group $AGName" -ForegroundColor Blue
+    Invoke-DbaQuery -SqlInstance $AGSecondary -Database master -Query  "ALTER DATABASE [$DatabaseName] SET HADR AVAILABILITY GROUP = [$AGName];"
+}
+
+Write-Host "Turning off readable secondary for AG '$AGName'" -ForegroundColor Blue
+Set-DbaAgReplica -SqlInstance $AGPrimary -AvailabilityGroup $AGname -ConnectionModeInSecondaryRole No -Replica $AGSecondary | Out-Null
+
+Write-Host "Completed move of database files for $DatabaseName on AG '$AGName'" -ForegroundColor Green
No results found