Migrate file share witness in cluster

 

• Create the required folders on new file share server and grant the permissions
• On the primary node Right click on the PowerShell in the programs folder and right click to select run as administrator. And run the below commands
  Import-Module FailoverClusters
• Set-ClusterQuorum -NodeMajority
• Verify the cluster shares were removed then set the share back to the new location.
• Run the below command to set the new file share witness to the cluster.
• Set-ClusterQuorum-NodeAndFileShareMajority\\fileserver\fsw
  Ex : Set-ClusterQuorum - NodeAndFileShareMajority\\oduhu\sq|2012clsprdfsws

PowerShell script to restore all databases at a time

 $backupRoot = Get-ChildItem -Path "F:\F_SQLBackup1\SQLBackup\CTASK0046007" 

$datafilesDest = "F:\F_SQLData1\SQLData" 

$logfilesDest = "F:\F_SQLLog1\SQLLog" 

$FilestreamDest = "F:\F_SQLData1\SQLData" 

$server = "USPILVMUMCSSQ01\INST1" 

# Create some “Relocate” file objects to pass to the Restore-SqlDatabase cmdlet…

## For each folder in the backup root directory...

#

foreach($folder in $backupRoot)

{   

    # Get the most recent .bak files for all databases...

    $backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*.bak" -Recurse | Sort-Object -Property CreationTime -Descending | Select-Object -First 1

    

    # For each .bak file...

    foreach ($backupFile in $backupFiles)

    {

        # Restore the header to get the database name...

        $query = "RESTORE HEADERONLY FROM DISK = N'"+$backupFile.FullName+"'"

        $headerInfo = Invoke-Sqlcmd -ServerInstance $server -Query $query

        $databaseName = $headerInfo.DatabaseName

        # Restore the file list to get the logical filenames of the database files...

        $query = "RESTORE FILELISTONLY FROM DISK = N'"+$backupFile.FullName+"'"

        $files = Invoke-Sqlcmd -ServerInstance $server -Query $query

        # Differentiate data files from log files...

        $dataFile = $files | Where-Object -Property Type -EQ "D"

        $logFile = $files | Where-Object -Property Type -EQ "L"

$Filestream = $files | Where-Object -Property Type -EQ "S"

        # Set some variables...

        $dataFileName = $dataFile.LogicalName

        $logFileName = $logFile.LogicalName

$FilestreamName = $Filestream.LogicalName

        # Set the destination of the restored files…

$relocate=@()

foreach ($dataFileName_one in $datafilename){

$dataFileFullPath = $datafilesDest+”\”+$dataFileName_one+”.mdf”

$obj= New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dataFileName_one,$dataFileFullPath)

$Relocate+=$obj

}

foreach ($logFileName_one in $logFileName){

$logFileFullPath = $logfilesDest+”\”+$logFileName_one+”.ldf”

$obj= New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logFileName_one,$logFileFullPath)

$Relocate+=$obj

}

foreach ($Filestream_one in $FilestreamName){

$FilestreamPath = $FilestreamDest+”\”+$Filestream_one

$obj= New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Filestream_one,$FilestreamPath)

$Relocate+=$obj

}

        # Create some "Relocate" file objects to pass to the Restore-SqlDatabase cmdlet...

        $RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $dataFileName, $dataFileFullPath

        $RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $logFileName, $logFileFullPath

        # Perform the database restore... and then go around the loop.

        Restore-SqlDatabase -ServerInstance $server -Database $databaseName -BackupFile $backupFile.FullName -RelocateFile $relocate -ReplaceDatabase 

    }

}