Move SQL User Databases Using PowerShell

moveUserDatabases.ps1

Import-Module SQLPS -DisableNameChecking -EA 0

$dbName = "deleteme"
$mdfNewLocation = "G:\Data"
$ldfNewLocation = "H:\Logs"
$sqlSA = "sqlSA"

$domain = $env:USERDOMAIN
$sqlSA = $domain + "\" + $sqlSA
$location = Get-Location

#region Get Database Info
	$query1 = "SELECT name, physical_name AS CurrentLocation, state_desc
			   FROM sys.master_files
			   WHERE database_id = DB_ID(N'" + $dbName + "');"
        Write-Verbose "Getting [$dbName] information..." -Verbose
	$items = Invoke-Sqlcmd -Query $query1
        Write-Output $items
#endregion

#region Take DB Offline
	# Set DB OFFLINE
	$query2 = "ALTER DATABASE [" + $dbName + "] SET OFFLINE WITH ROLLBACK IMMEDIATE;"
        Write-Verbose "Taking [$dbName] Offline..." -Verbose
	Invoke-Sqlcmd -Query $query2
	# Get DB Status
	function getDBStatus ($dbName) {
		$query3 = "SELECT DATABASEPROPERTYEX('" + $dbName + "', 'Status') AS dbStatus"
		$dbStatus = Invoke-Sqlcmd -Query $query3
		return $dbStatus
	}
	# Wait until DB goes OFFLINE
        Write-Verbose "Getting [$dbName] status..." -Verbose
	$status = getDBStatus -dbName $dbName
        Write-Output $status
	$time = 0
	while(-not ($status.dbStatus -eq "OFFLINE")){
   		sleep 2;
		$time = $time + 2
		Write-Verbose "Still waiting on DB to go OFFLINE... ($time seconds elapsed)" -Verbose
		$status = getDBStatus -dbName $dbname    
		if ($time -gt 28) {
			break
		}
	 }
#endregion

#region Copy Files, Set ACLs, and Alter Database
	foreach ($item in $items) {
        function copyItem ($newLocation, $file, $currentItem) {
        	$destination = $newLocation + "\" + $file
                Write-Verbose "Moving $file to $destination..." -Verbose
	        Copy-Item -Path $currentItem -Destination $destination
                return $destination
        }
		$currentItem = $item.CurrentLocation
		$itemExtension = [System.IO.Path]::GetExtension($currentItem)
		$file = [System.IO.Path]::GetFileName($currentItem)
		$name = $item.name
        switch ($itemExtension) { 
            ".mdf" {
                $destination = copyItem -newLocation $mdfNewLocation -file $file -currentItem $currentItem
                $folderLocation = $mdfNewLocation
            } 
            ".ldf" {
                $destination = copyItem -newLocation $ldfNewLocation -file $file -currentItem $currentItem
                $folderLocation = $ldfNewLocation
            }  
            default {
                Write-Verbose "The file extension is not supported..." -Verbose
                break
            }
        }

    	# Set ACLs
		# Change to UTC Path Format
		$destDrive = Split-Path -qualifier $destination
		$utcPath = "\\$env:computername\" + $destDrive.Replace(":","$")
		$destination = $destination.Replace($destDrive, $utcPath)
                Set-Location $destDrive
                Write-Verbose "Setting ACL on $destination..." -Verbose
		# Set db to Inherit Permissions
		$acl =  Get-Acl $destination
                Write-Verbose "Setting to Inherit Permissions..." -Verbose
		$acl.SetAccessRuleProtection($false,$false)
		# Set Owner of DB to SA Account
		$owner = New-Object System.Security.Principal.NTAccount($sqlSA)
		Write-Verbose "Updating Item Owner to $owner..." -Verbose
                $acl.SetOwner($owner)
		$acl | Set-Acl
		# Update Database
		$query4 = "ALTER DATABASE [" + $dbName + "] MODIFY FILE ( NAME = " + $name + ", FILENAME = '" + $destination + "' );"
                Write-Verbose "Updating Database with new file location..." -Verbose
		Invoke-Sqlcmd -Query $query4
	}
        Set-Location $location
#endregion

#region Bring DBs Online
	$query5 = "ALTER DATABASE [" + $dbName + "] SET ONLINE;"
        Write-Output "Bring $dbName ONLINE..."
	Invoke-Sqlcmd -Query $query5
#endregion

#region wait for 10 then Delete Old Files
	# wait for 10
	for ($i = 10; $i -gt 0; $i--) { 
		Write-Host("Deleting old files in $i seconds") 
		Start-Sleep -Seconds 1  
	} 
	# delete old files
	foreach ($item in $items) {
		Remove-Item -Path $item.CurrentLocation -Force
	}             
#endregion

Great script! realy helpfull. How can I move multiply databases? or even better all of them?
Stig