T-SQL Tuesday 143 – Short Powershell code to move DB files in AlwaysOn

This month’s #tsql2sday is hosted by John McCormack who asks us to share some useful snippets of code that can help us in our everyday jobs.

Well, honestly, this is more or less what my blog is mostly about anyway: sharing insights, scripts, and code snippets that I found to be helpful in my day-to-day job as a SQL Server consultant.

Link back to the invitation post

First, I must apologize for the late publication and for my lack of activity during the past couple of months. September was pretty much full of holidays over here in Israel and we were super busy, so that barely left me any time for my blog. Just so you’d know to appreciate this, I’m writing this post at the cost of sleeping hours! I’m doing this for you, my friend!

Ice Age GIF - Ice Age Ice Age GIFs

Anyways, back to the topic at hand.

I can reference back to lots of very useful scripts and snippets that I find to be invaluable, and I’m using them quite frequently. For example:

I could go on and on, but you can just go visit my blog archives and see for yourself if there’s anything there that could interest you.

Also, we at Madeira Data Solutions have an amazing collection of useful scripts and snippets in our open-source GitHub repository called the “Madeira Toolbox”. I’m sure you’ll find there lots of things you’d like. Be sure to watch/subscribe to that repository too, as we continuously add new stuff there and keep our scripts up-to-date.

Something New for Today

But I’m not here just to re-hash old content. I’ve been doing interesting work this past month, and it would be a waste not to share an interesting script with you as a result, right?

Today, I’m sharing with you a cool Powershell script that basically implements the methodology necessary to move database files to a new location in AlwaysOn Availability Groups, without breaking HADR.

It’s based on a few very useful step-by-step guides on the topic such as this one and this one and this one. But it takes it a step further by being a single cohesive Powershell script that does everything end-to-end.

Well… Almost everything… The only thing it’s missing is somehow disabling any SQL Agent jobs that may be performing backups. I still haven’t figured out how to possibly automate such a thing, so you’d have to do that manually on your own.

But other than that, the script does the following:

  • Makes sure the destination folder(s) exist.
  • Suspends and removes the specified database from its Availability Group.
  • Executes ALTER DATABASE .. MODIFY FILE .. to change the database file paths.
  • If connected to the PRIMARY replica: Takes the database offline.
  • If connected to the SECONDARY replica: Takes the whole MSSQLSERVER service down.
  • Actually moves the files to their new destination, while retaining the file permissions and ownership (so that the SQL Server won’t get an “Access Denied” error).
  • Brings the database / service back online.
  • Adds the database back to the Availability Group.

I know, it’s (ahem) a bit more than a “snippet”, but you gotta admit it’s pretty cool and useful, right?

A few remarks:

  • Note the parameters for the script. Be sure you’re providing the proper values as input.
  • The script was not tested with database files that aren’t mdf/ndf/ldf (such as FILESTREAM, Full-Text Catalogs, and In-Memory). So, I cannot guarantee that those will work. But if you’re feeling adventurous, you can use the switch parameter “AllowNonDataOrLogFileTypes” to try it out anyway.
  • If you’re moving large files, remember that it would take a while to finish, during which time the database will not be available.
  • Don’t forget to disable backup jobs! The script doesn’t do that for you.
  • The script is NOT idempotent. If it fails in the middle of execution, you may have trouble running it again.
  • As always with such things: Test, test, test! I recommend adding a new, small database to your Availability Group and test the script on this database, before trying it out on your larger production databases.
  • The script utilizes the same template I used in my post One handy Powershell script template to rule them all. So, it also has some useful logging.

Anyways, without further ado, you can find this script in our Madeira Toolbox here, or in my GitHub Gists below:

# Copyright 2021 Eitan Blumin <@EitanBlumin, https://www.eitanblumin.com&gt;
# while at Madeira Data Solutions <https://www.madeiradata.com&gt;
#
# Licensed under the MIT License (the "License");
#
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
<#
.SYNOPSIS
Moves files to a new location for a SQL Server database in an AlwaysOn Availability Group.
Author: Eitan Blumin (@EitanBlumin) | Madeira Data Solutions (@Madeira_Data)
License: MIT License
.DESCRIPTION
Moves files to a new location for a SQL Server database in an AlwaysOn Availability Group.
The script performs the following operations:
– Makes sure the destination folder(s) exist.
– Suspends and removes the specified database from its Availability Group.
– Executes ALTER DATABASE .. MODIFY FILE .. to change the database file paths.
– If connected to the PRIMARY replica: Takes the database offline.
– If connected to the SECONDARY replica: Takes the whole MSSQLSERVER service down.
– Actually moves the files to their new destination, while retaining the file permissions and ownership (so that the SQL Server won't get an "Access Denied" error).
– Brings the database / service back online.
– Adds the database back to the Availability Group.
.EXAMPLE
C:\PS> .\Move-SqlFilesAlwaysOn.ps1 -DatabaseName "TestDB" -NewDataFolderPath "D:\MSSQL\Data\" -NewLogFolderPath "L:\MSSQL\Log\"
.NOTES
This is an open-source project developed by Eitan Blumin while an employee at Madeira Data Solutions, Madeira Ltd.
A few remarks:
– Note the parameters for the script. Be sure you're providing the proper values as input.
– The script was not tested with database files that aren't mdf/ndf/ldf (such as FILESTREAM, Full-Text Catalogs, and In-Memory). So, I cannot guarantee that those will work. But if you're feeling adventurous, you can use the switch parameter "AllowNonDataOrLogFileTypes" to try it out anyway.
– If you're moving large files, remember that it would take a while to finish, during which time the database will not be available.
– Don't forget to disable backup jobs! The script doesn't do that for you.
– The script is NOT idempotent. If it fails in the middle of execution, you may have trouble running it again.
– As always with such things: Test, test, test! I recommend adding a new, small database to your Availability Group and test the script on this database, before trying it out on your larger production databases.
.LINK
https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Move-SqlFilesAlwaysOn.ps1
.LINK
https://madeiradata.com
.LINK
https://eitanblumin.com
#>
param
(
[Parameter(Mandatory=$true, Position=0)][string]$DatabaseName = "TestDB",
[Parameter(Mandatory=$true, Position=1)][string]$NewDataFolderPath = "D:\MSSQL\Data\",
[Parameter(Mandatory=$false, Position=2)][string]$NewLogFolderPath = $NewDataFolderPath,
[string]$SqlInstanceConnectionString = "Data Source=.;Integrated Security=True;Application Name=Move Availability Group Files",
[string]$SqlServiceName = "MSSQLSERVER",
[switch]$SecondaryOnly,
[switch]$AllowNonDataOrLogFileTypes,
[switch]$SkipRunningJobsCheck,
[int32]$MaxRecoveryQueueMB = 1024,
[string]$logFileFolderPath = "C:\Madeira\log",
[string]$logFilePrefix = "move_ag_dbfiles_",
[string]$logFileDateFormat = "yyyyMMdd_HHmmss",
[int]$logFileRetentionDays = 7
)
Process {
#region initialization
function Get-TimeStamp {
Param(
[switch]$NoWrap,
[switch]$Utc
)
$dt = Get-Date
if ($Utc -eq $true) {
$dt = $dt.ToUniversalTime()
}
$str = "{0:MM/dd/yy} {0:HH:mm:ss}" -f $dt
if ($NoWrap -ne $true) {
$str = "[$str]"
}
return $str
}
if ($logFileFolderPath -ne "")
{
if (!(Test-Path PathType Container Path $logFileFolderPath)) {
Write-Output "$(Get-TimeStamp) Creating directory $logFileFolderPath" | Out-Null
New-Item ItemType Directory Force Path $logFileFolderPath | Out-Null
} else {
$DatetoDelete = $(Get-Date).AddDays($logFileRetentionDays)
Get-ChildItem $logFileFolderPath | Where-Object { $_.Name -like "*$logFilePrefix*" -and $_.LastWriteTime -lt $DatetoDelete } | Remove-Item | Out-Null
}
$logFilePath = $logFileFolderPath + "\$logFilePrefix" + (Get-Date Format $logFileDateFormat) + ".LOG"
# attempt to start the transcript log, but don't fail the script if unsuccessful:
try
{
Start-Transcript Path $logFilePath Append
}
catch [Exception]
{
Write-Warning "$(Get-TimeStamp) Unable to start Transcript: $($_.Exception.Message)"
$logFileFolderPath = ""
}
}
#endregion initialization
$ErrorActionPreference = "Stop"
#region main
# Get metadata for the given database, and do some basic validation
$AGName = $null
$IsSecondary = $false
$result = Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "SELECT ag.name,
replica_states.role_desc,
CONVERT(sysname, SERVERPROPERTY('ServerName')) AS ServerName
FROM sys.databases db
INNER JOIN sys.dm_hadr_availability_replica_states replica_states ON db.replica_id = replica_states.replica_id
INNER JOIN sys.availability_groups ag ON replica_states.group_id = ag.group_id
WHERE db.name = '$DatabaseName'" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop
if ($result)
{
$AGName = $result['name']
if ($result['ServerName'] -ne $env:COMPUTERNAME)
{
Write-Error "This script must be run locally from within one of the AG replica servers." ErrorAction Stop
}
if ($result['role_desc'] -eq 'SECONDARY') {
$IsSecondary = $true
} elseif ($SecondaryOnly) {
Write-Error "This script must be run locally from within the SECONDARY replica only." ErrorAction Stop
}
} else {
Write-Error "Database [$DatabaseName] is not part of an availability group." ErrorAction Stop
}
# Make sure this script is run as an Administrator
If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole( [Security.Principal.WindowsBuiltInRole] Administrator) )
{
Write-Error You do not have Administrator rights to run this script!`nPlease re-run this script as an Administrator! ErrorAction Stop
}
if ($IsSecondary) {
# Get the MSSQLSERVER service object
try {
$SqlService = Get-Service Name $SqlServiceName
if ($SqlService.Status -eq "Running" -and !$SqlService.CanStop) {
Write-Error "No access to shut down '$SqlServiceName' service of computer '$env:COMPUTERNAME'" ErrorAction Stop
}
} catch {
Write-Error "Error accessing '$SqlServiceName' service of computer '$env:COMPUTERNAME'" ErrorAction Stop
}
# Get all services dependent on the MSSQLSERVER service
$SqlDependentServices = Get-Service Name $SqlServiceName DependentServices ErrorAction Stop
# Check for high recovery queues
$recoveryQueues = Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "select counter_name, instance_name, cntr_value
from sys.dm_os_performance_counters
where object_name like '%Database Replica%'
and counter_name like 'Recovery Queue%'
and cntr_value > $MaxRecoveryQueueMB * 1024
order by cntr_value desc" Verbose AbortOnError ErrorAction Stop
if ($recoveryQueues) {
$recoveryQueues | Out-Host
Write-Error "Stopping execution because Availability Group Recovery Queue is too high. Stopping the SQL service at this state may put the Availability Group at risk." ErrorAction Stop
}
}
while (!$SkipRunningJobsCheck)
{
$runningJobs = Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "SELECT j.name, ja.start_execution_date, ja.stop_execution_date, ja.next_scheduled_run_date
FROM msdb..sysjobactivity as ja
inner join msdb..sysjobs as j on ja.job_id = j.job_id
WHERE session_id = (SELECT MAX(session_id) FROM msdb..sysjobactivity)
AND start_execution_date IS NOT NULL
AND (stop_execution_date is null or next_scheduled_Run_date between GETDATE() and DATEADD(minute, 5, GETDATE()))" Verbose AbortOnError ErrorAction Stop
if ($runningJobs)
{
$runningJobs | Out-Host
$inputResponse = Read-Host "There are jobs currently running or soon to be executed. Are you sure you want to proceed anyway? (C=Continue, R=Retry Check, S=Stop) "
if ($inputResponse.ToLower().StartsWith("c")) {
$SkipRunningJobsCheck = $true;
}
elseif ($inputResponse.ToLower().StartsWith("s")) {
Write-Error "Stopping execution because found currently running or soon to be run jobs." ErrorAction Stop
}
}
}
# Use T-SQL to make sure target folder(s) exist with proper permissions
@($NewDataFolderPath,$NewLogFolderPath) | ForEach {
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "set nocount on;
declare @path varchar(8000) = '$_';
create table #tmp (
[FILE_EXISTS] int not null,
[FILE_IS_DIRECTORY] int not null,
[PARENT_DIRECTORY_EXISTS] int not null)
insert into #tmp
exec xp_fileexist @path;
if exists ( select * from #tmp where FILE_EXISTS = 1 )
begin
raiserror(N'Existing path is not a directory: %s',16,1,@path);
end
else if exists (select * from #tmp where FILE_EXISTS = 0 AND FILE_IS_DIRECTORY = 0)
begin
raiserror(N'Creating new directory: %s', 0, 1, @path);
exec xp_create_subdir @path
end" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop
}
# Make sure the new folder paths are accessible
if (!$NewDataFolderPath -or !(Test-Path $NewDataFolderPath PathType Container)) {
Write-Error "Unable to access new data folder path: $NewDataFolderPath" ErrorAction Stop
}
if (!$NewLogFolderPath -or !(Test-Path $NewLogFolderPath PathType Container)) {
Write-Error "Unable to access new log folder path: $NewLogFolderPath" ErrorAction Stop
}
# Get metadata for all database files
$dbFiles = Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "SELECT name, type_desc, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('$DatabaseName');" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop
# Validate the path for each file and make sure it's either LOG or ROWS
# (I cannot guarantee at this point that this script will work with other file types such as FILESTREAM, Full-Text, In-Memory, etc.)
$dbFiles | ForEach {
if ((Test-Path $_.physical_name PathType Leaf IsValid ErrorAction Continue) -and ($_.type_desc -eq "ROWS" -or $_.type_desc -eq "LOG" -or $AllowNonDataOrLogFileTypes))
{
Write-Host "OK: $($_.name), type: $($_.type_desc), old path: $($_.physical_name)" ForegroundColor Green
} elseif ($_.type_desc -ne "ROWS" -and $_.type_desc -ne "LOG") {
Write-Error "UNSUPPORTED TYPE: $($_.name), type: $($_.type_desc), old path: $($_.physical_name)" ErrorAction Stop
} else {
Write-Error "INACCESSIBLE: $($_.name), type: $($_.type_desc), old path: $($_.physical_name)" ErrorAction Stop
}
}
# Suspend and remove the database from the Availability Group
Write-Output "$(Get-TimeStamp) Removing [$DatabaseName] from Availability Group [$AGName]"
$cmd = "USE [master];
ALTER DATABASE [$DatabaseName] SET HADR SUSPEND;"
if ($IsSecondary) {
$cmd = $cmd + "
ALTER DATABASE [$DatabaseName] SET HADR OFF;"
} else {
$cmd = $cmd + "
ALTER AVAILABILITY GROUP [$AGName] REMOVE DATABASE [$DatabaseName];"
}
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query $cmd Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop | Out-Null
# Update the database file paths with their new locations
$dbFiles | ForEach {
$currFileName = Split-Path $_.physical_name Leaf
if ($_.type_desc -eq "ROWS") {
$currNewPath = Join-Path Path $NewDataFolderPath ChildPath $currFileName
} else {
$currNewPath = Join-Path Path $NewLogFolderPath ChildPath $currFileName
}
Write-Output "$(Get-TimeStamp) Altering file [$($_.name)] with new path: $currNewPath"
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "USE [master];
ALTER DATABASE [$DatabaseName] MODIFY FILE (NAME = [$($_.name)], FILENAME = '$currNewPath');" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop | Out-Null
}
if (!$IsSecondary) {
# If connected to the PRIMARY replica, take the database offline
Write-Output "$(Get-TimeStamp) Taking [$DatabaseName] offline"
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "USE [master];
ALTER DATABASE [$DatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop | Out-Null
} elseif ($SqlService.Status -eq "Running") {
# If connected to the SECONDARY replica, take the MSSQLSERVER service offline
Write-Output "$(Get-TimeStamp) Stopping service '$SqlServiceName' in computer '$env:COMPUTERNAME'…"
$SqlService | Stop-Service Force Verbose
} else {
Write-Warning "$(Get-TimeStamp) Service status of '$SqlServiceName' in computer '$env:COMPUTERNAME' is: $($SqlService.Status)"
}
# Begin moving the files to their new locations
$dbFiles | ForEach {
$currFileName = Split-Path $_.physical_name Leaf
# Save the current file's permissions and ownership details
$currAcl = Get-Acl Path $_.physical_name
if ($_.type_desc -eq "LOG") {
$currNewPath = Join-Path Path $NewLogFolderPath ChildPath $currFileName
} else {
$currNewPath = Join-Path Path $NewDataFolderPath ChildPath $currFileName
}
Write-Output "$(Get-TimeStamp) Moving file [$($_.name)] from: $($_.physical_name) to: $currNewPath"
Move-Item Path $_.physical_name Destination $currNewPath Verbose ErrorAction Stop | Out-Null
# Apply the file-level permissions and ownership in their new location
$currAcl | Set-Acl Path $currNewPath Verbose
}
if (!$IsSecondary) {
# If connected to the PRIMARY replica, bring the database back online
Write-Output "$(Get-TimeStamp) Bringing [$DatabaseName] online"
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "USE [master];
ALTER DATABASE [$DatabaseName] SET ONLINE;" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop | Out-Null
} elseif ($SqlService.Status -eq "Stopped") {
# If connected to the SECONDARY replica, bring the MSSQLSERVER service and its dependent services back online
Write-Output "$(Get-TimeStamp) Starting service '$SqlServiceName' in computer '$env:COMPUTERNAME'…"
$SqlService | Start-Service Verbose
$SqlDependentServices | Start-Service Verbose
} else {
Write-Warning "$(Get-TimeStamp) Service status of '$SqlServiceName' in computer '$env:COMPUTERNAME' is: $($SqlService.Status)"
}
# Add the database back to the Availability Group
Write-Output "$(Get-TimeStamp) Joining database [$DatabaseName] back to Availability Group [$AGName]"
if ($IsSecondary) {
$cmd = "ALTER DATABASE [$DatabaseName] SET HADR AVAILABILITY GROUP = [$AGName];"
} else {
$cmd = "ALTER AVAILABILITY GROUP [$AGName] ADD DATABASE [$DatabaseName];"
}
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query $cmd Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop
# Output new file details
Invoke-Sqlcmd ConnectionString $SqlInstanceConnectionString Query "SELECT name, type_desc, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('$DatabaseName');" Verbose AbortOnError OutputSqlErrors $true ErrorAction Stop
Write-Output "$(Get-TimeStamp) Done"
#endregion main
#region finalization
if ($logFileFolderPath -ne "") { Stop-Transcript }
#endregion finalization
}

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.