Move SQL Server database files to different folders with PowerShell

In my previous post, I explained how to move SQL Server databases files programmatically by leveraging the high availability of an Availability Group (AG).
But when you can afford to have a database unavailable for a short time, and if it is of a reasonable size, then an offline approach may be desirable. This will have the advantage of also working for standalone databases, but also of not needing to reapply the manipulation on the primary after a fail-over to a secondary.

In this blog, I will again use dbatools module in my PowerShell script. For automation in SQL Server, this is now a must-have language.

In the following script, it needs to be run on the Primary server because I am using local paths to move database files.
For a remote approach, you can either use PowerShell remote sessions or remote paths to achieve this goal.

The script will also remove the database from AG during the detach/attach operation, and will add back the database in the AG with the synchronization.

# IMPORTANT: this script has to be run on the Primary only
# dbatools module is required

$Primary = '<MyPrimary>';
$Secondary = '<MySecondary>';

$Database = '<MyDatabase>';
$AvailabilityGroup = '<MyAG>';

$NewDataFolder = '<MyNewDataFolder>';
$NewLogsFolder = '<MyNewLogFolder>';


Try
{
	Import-Module -Name dbatools;
	
	$dbFiles = Get-DbaDbFile -SqlInstance $Primary -Database $Database;
	$dbFiles | Format-Table ComputerName, InstanceName, Database, PhysicalName, LogicalName, TypeDescription, Size;
	
	$title   = 'Moving database to another folder (Offline)'
	$msg     = "Do you want to Move data file(s) to $NewDataFolder and logs file to $NewLogsFolder ?"
	$options = '&Yes', '&No'
	$default = 1  # 0=Yes, 1=No

	$Continue = $False;
	
	do {
		$response = $Host.UI.PromptForChoice($title, $msg, $options, $default)
		if ($response -eq 0) {
			$Continue = $True;
			$response = 1;
		}
	} until ($response -eq 1)
	
	If (-not $Continue){
		Write-Warning -Message "Aborted by user...";
		Return;
	}
	
	# Test path New data folder
	Write-Output -Message "Testing New Data folder $NewDataFolder";
	If (-not (Test-Path -Path $NewDataFolder)){
		Write-Warning -Message "Target data folder $NewDataFolder does not exist. Aborting...";
		Return;
	};
    Write-Output '...ok';
	
	# Test path New logs folder
	Write-Output -Message "Testing New Logs folder $NewLogsFolder";
	If (-not (Test-Path -Path $NewLogsFolder)){
		Write-Warning -Message "Target logs folder $NewLogsFolder does not exist. Aborting...";
		Return;
	};
    Write-Output '...ok';
	
	# Check if AG is configured
	If ($AvailabilityGroup){
		# Remove database from AG
		Write-Output "Start removing database $Database from AG $AvailabilityGroup on instance $Primary";
		Remove-DbaAgDatabase -SqlInstance $Primary -Database $Database -AvailabilityGroup $AvailabilityGroup -EnableException;
        Write-Output '...ok';
	}

	# Detach database on Primary
	Write-Output "Detaching database $Database on instance $Primary";
	Dismount-DbaDatabase -SqlInstance $Primary -Database $Database -EnableException;
    Write-Output '...ok';

	
	# Move data files
	Write-Output "Moving data file(s) of $Database on instance $Primary";
	($dbFiles | Where-Object Type -eq 0).PhysicalName | ForEach-Object -Process {Move-Item -Path $_ -Destination $NewDataFolder;}
    Write-Output '...ok';
	
	# Move logs files
    Write-Output "Moving logs file of database $Database on instance $Primary";
	($dbFiles | Where-Object Type -eq 1).PhysicalName | ForEach-Object -Process {Move-Item -Path $_ -Destination $NewLogsFolder;}
    Write-Output '...ok';
	
	# Create new file structure for database to attach
	$newdbFiles = @(Get-ChildItem $NewDataFolder) + @(Get-ChildItem $NewLogsFolder);
	$fs = New-Object System.Collections.Specialized.StringCollection; 
	@($newdbFiles) | ForEach-Object -Process { `
		$Null = $fs.Add($_.FullName); `	
	}; 
	
	# Attach database on Primary
    Write-Output "Attaching database $database on instance $Primary"
	Mount-DbaDatabase -SqlInstance $Primary -Database $Database -FileStructure $fs;
    Write-Output "...ok";
	

	# Add database to AG
	If ($Secondary -and $AvailabilityGroup){
		
        Write-Output "Removing database $database on secondary $Secondary"; 
		Remove-dbaDatabase -SqlInstance $Secondary -Database $Database;
        Write-Output "...ok";
		
        Write-Output "Start adding database $Database from AG $AvailabilityGroup on instance $Primary to secondary $Secondary";
		Add-DbaAgDatabase -SqlInstance $Primary -Database $Database -AvailabilityGroup $AvailabilityGroup -Secondary $Secondary -SeedingMode Automatic;
        Write-Output "...ok";
	}

}
Catch
{
	Write-Error $_.Exception.toString();
}

Running this script on a production environment, I have seen a few seconds of downtime in the worst case with <500GB databases.
But this will depend mainly on your infrastructure and whether the database files are transferred to another physical drive. Another thing to consider is the database cache will be reset with this approach.

Enjoy automation!