Add-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

function Move-ContentDatabaseToNewDrive
{
    [cmdletbinding()]
    param
    (
        [parameter(Mandatory=$true)][Microsoft.SharePoint.Administration.SPContentDatabase]$Database,
        [parameter(Mandatory=$true)][string]$DataPath,
        [parameter(Mandatory=$true)][string]$LogPath,
        [parameter(Mandatory=$true)][string]$BackupPath
    )

    begin
    {
        $maximumSiteCount       = $Database.MaximumSiteCount
        $warningSiteCount       = $Database.WarningSiteCount
        $availabilityGroup      = $Database.AvailabilityGroup
        $availabilityGroupName  = $availabilityGroup.Name
        $availabilityGroupShare = $null
        $databaseName           = $Database.Name
        $databaseServer         = $Database.Server
        $webApplication         = $Database.WebApplication
        $backupFullFilePath     = Join-Path -Path $BackupPath -ChildPath "$databaseName.bak"
        $fileShare              = $null

        $backupTSQLCommand  = "IF DB_ID('{0}') IS NOT NULL
                               BEGIN
                                   BACKUP DATABASE [{0}] TO DISK = N'{1}' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION
                               END"

        $dropTSQLCommand    = "IF DB_ID('{0}') IS NOT NULL
                               BEGIN
                                   EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{0}'
                                   ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
                                   DROP DATABASE [{0}]
                               END"

        $restoreTSQLCommand = "IF DB_ID('{0}') IS NULL
                               BEGIN
                                   RESTORE DATABASE [{0}] FROM DISK = N'{1}' WITH FILE = 1, MOVE '{2}' TO '{3}', MOVE '{4}' TO '{5}', NOUNLOAD
                               END"

        $logicalNameTSQLCommand = "SELECT [type_desc], [name], [physical_name] FROM sys.[master_files] WHERE [database_id] = DB_ID('{0}')"

        $inAvailabilityGroupTSQLCommand  = "SELECT COUNT(*) AS 'Count' FROM sys.availability_groups g JOIN sys.availability_databases_cluster c ON g.group_id = c.group_id WHERE database_name = '{0}'"

        $inMirroringGroupTSQLCommand = "SELECT COUNT(*) AS 'Count' FROM sys.database_mirroring_witnesses WHERE database_name = '{0}'"
    }
    process
    {
        # get existing database properties before doing anything invasive

            try
            {
                Write-Verbose -Message "Querying for content database properties"

                $tsql = $logicalNameTSQLCommand -f $databaseName
                $logicalFileInfo = Get-DataTable -DatabaseServer $databaseServer -DatabaseName "master" -Query $tsql

                # get the logical name for the data and log file
                $logicalDataFileName = $logicalFileInfo | ? type_desc -eq "ROWS" | SELECT -First 1 -ExpandProperty Name
                $logicalLogFileName  = $logicalFileInfo | ? type_desc -eq "LOG"  | SELECT -First 1 -ExpandProperty Name

                # get the full path for the data and log file
                $physicalDataFileFullPath = $logicalFileInfo | ? type_desc -eq "ROWS" | SELECT -First 1 -ExpandProperty physical_name
                $physicalLogFileFullPath  = $logicalFileInfo | ? type_desc -eq "LOG"  | SELECT -First 1 -ExpandProperty physical_name

                # pull out the data and log file names
                $physicalDataFileName  = (New-Object System.IO.FileInfo($physicalDataFileFullPath)).Name
                $physicalLogFileName   = (New-Object System.IO.FileInfo($physicalLogFileFullPath)).Name

                # build new path to data and log files
                $newPhysicalDataFileFullPath = [System.IO.Path]::Combine($DataPath, $physicalDataFileName)
                $newPhysicalLogFileFullPath  = [System.IO.Path]::Combine($LogPath, $physicalLogFileName)

                if( -not $logicalDataFileName -or -not $logicalLogFileName -or -not $physicalDataFileFullPath -or -not $physicalLogFileFullPath )
                {
                    throw "The required database properties name where not found."
                }
            }
            catch
            {
                Write-Error "Failed to discover database properties. Exception: $($_.Exception)"
                return
            }

            if( $VerbosePreference -ne "SilentlyContinue" )
            {
                [PSCustomObject] @{
                    BackupFilePath      = $backupFullFilePath
                    LogicalDataFileName = $logicalDataFileName
                    LogicalLogFileName  = $logicalLogFileName
                    NewLogFilePath      = $newPhysicalLogFileFullPath
                    NewDataFilePath     = $newPhysicalDataFileFullPath
                    CurrentDataFilePath = $physicalDataFileFullPath
                    CurrentLogFilePath  = $physicalLogFileFullPath
                }
            }


        # remove from AG

            if( $availabilityGroup )
            {
                try
                {
                    # get the file share location for this availalbity group
                    $availabilityGroupShare = $availabilityGroup.GetType().GetProperty("FileShare", "NonPublic,Instance").GetValue($availabilityGroup)

                    if( -not $availabilityGroupShare -or -not (Test-Path -Path $availabilityGroupShare))
                    {
                        throw "The Availability Group file share path '$availabilityGroupShare' was not found."
                    }

                    Write-Verbose -Message "Removing database from Availability Group"
                    Remove-DatabaseFromAvailabilityGroup -DatabaseName $Database.Name -AGName $availabilityGroup.Name

                    # get the instance name not that the database is not in the AG
                    $Database = Get-SPContentDatabase -Identity $Database.Name
                    $databaseServer = $Database.Server
                }
                catch
                {
                    Write-Error "Failed to remove database from Availability Group. Exception: $($_.Exception)"
                    return
                }
            }
            else
            {
                # verify the database is not in a Availability Group or using SQL Mirroring not registered with SharePoint

                try
                {
                    Write-Verbose -Message "Verifying Content Database is not a member of other HADR solutions"

                    $tsql = $inAvailabilityGroupTSQLCommand -f $databaseName
                    if( (Get-DataTable -DatabaseServer $databaseServer -DatabaseName "master" -Query $tsql).Count -ne 0 )
                    {
                        Write-Error "Content database is in a SQL Availability Group but it is not configured in SharePoint"
                        return
                    }

                    $tsql = $inMirroringGroupTSQLCommand -f $databaseName
                    if( (Get-DataTable -DatabaseServer $databaseServer -DatabaseName "master" -Query $tsql).Count -ne 0 )
                    {
                        Write-Error "Content database is using SQL mirroring."
                        return
                    }
                }
                catch
                {
                    Write-Error "Failed to validate database is not in an Availability Group or Mirroring Group. Exception: $($_.Exception)"
                    return
                }
            }


        # dismount from SharePoint farm

            try
            {
                Write-Verbose -Message "Dismounting database from SharePoint farm"
                $Database | Dismount-SPContentDatabase -Confirm:$false
            }
            catch
            {
                Write-Error "Failed to remove database from SharePoint farm. Exception: $($_.Exception)"
                return
            }


        # backup database

            try
            {
                $tsql = $backupTSQLCommand -f $databaseName, $backupFullFilePath

                Write-Verbose -Message "Backing up content database to $BackupPath"
                Invoke-NonQuery -DatabaseName $databaseName -DatabaseServer $databaseServer -Query $tsql
            }
            catch
            {
                Write-Error "Failed to backup database to $backupFullFilePath. Exception: $($_.Exception)"
                return
            }


        # drop database

            try
            {
                $tsql = $dropTSQLCommand -f $databaseName

                Write-Verbose -Message "Dropping content database"
                Invoke-NonQuery -DatabaseName "master" -DatabaseServer $databaseServer -Query $tsql
            }
            catch
            {
                Write-Error "Failed to drop database. Exception: $($_.Exception)"
                return
            }


        # restore database

            try
            {
                $tsql = $restoreTSQLCommand -f $databaseName, $backupFullFilePath, $logicalDataFileName, $newPhysicalDataFileFullPath, $logicalLogFileName, $newPhysicalLogFileFullPath
                Write-Verbose -Message "Restoring content database from $backupFullFilePath"
                Invoke-NonQuery -DatabaseName "master" -DatabaseServer $databaseServer -Query $tsql
            }
            catch
            {
                Write-Error "Failed to restore database. Exception: $($_.Exception)"
                return
            }



        # mount to SharePoint

            try
            {
                Write-Verbose -Message "Mounting database to SharePoint farm"
                Mount-SPContentDatabase -Name $databaseName -DatabaseServer $databaseServer -WebApplication $webApplication -SkipSiteUpgrade -MaxSiteCount $maximumSiteCount -WarningSiteCount $warningSiteCount | Out-Null
            }
            catch
            {
                Write-Error "Failed to add database to SharePoint farm. Exception: $($_.Exception)"
                return
            }



        # add to AG

            if( $availabilityGroupShare )
            {
                try
                {
                    Write-Verbose -Message "Adding database to Availability Group"
                    Add-DatabaseToAvailabilityGroup -AGName $availabilityGroupName -DatabaseName $databaseName -FileShare $availabilityGroupShare
                }
                catch
                {
                    Write-Error "Failed to add database to Availability Group. Exception: $($_.Exception)"
                    return
                }
            }
    }
    end
    {
    }
}


#$database   = Get-SPContentDatabase -Identity "SP2016_CONTENT_002"
$dataPath   = "E:\Program Files\Microsoft SQL Server\MSSQL13.SHAREPOINT\MSSQL\DATA"
$logPath    = "F:\Program Files\Microsoft SQL Server\MSSQL13.SHAREPOINT\MSSQL\DATA"
$backupPath = "E:\backups"

Move-ContentDatabaseToNewDrive -Database $database -DataPath $dataPath -LogPath $logPath -BackupPath $backupPath -Verbose