functions/Move-SqlDatabaseFile.ps1
|
Function Move-SqlDatabaseFile { <# .SYNOPSIS Helps moving databases files to another location with safety. Is usefull when a new drive/lun is delivered or when we need to move files to another drive/lun to free space. .DESCRIPTION This function will perform the following steps: 1. Set database offline 2. Copy file(s) from source to destination 3. Alter database files location on database metadata (using ALTER DATABASE [db] MODIFY FILE command) 4. Bring database Online 5. Perform DBCC CHECKDB - You can skip this step if you want to execute it manually after check that database is online. By default the source files would not be deleted. But if you want, you can use -DeleteSourceFiles switch. Copy method: If running localy - Use Robocopy. If not exits use Start-BitsTransfer If run remotely - Check if user have access to UNC paths (\\) - if yes uses robocopy - If not, try Remote Session (PSSession) -> if not enabled on target machine you can enable by using the following command: Enable-PSRemoting -force uses robocopy on the machine if exists The -Databases parameter is autopopulated for command-line completion and can be used to copy only specific objects. .PARAMETER SqlServer The SQL Server instance. You must have sysadmin access and server version must be SQL Server version 2000 or higher. .PARAMETER Databases Will appear once you chose a -SqlServer that you have access. .PARAMETER SqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $scred = Get-Credential, then pass $scred object to the -SqlCredential parameter. Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials. To connect as a different Windows user, run PowerShell as that user. .PARAMETER ExportDatabaseStructure This switch with the -OutFile parameter will generate an CSV file with all database files. The CSV have a column named 'DestinationFolderPath' which must be filled with the destination path you want. You must remove all lines that have files you don't want to move. .PARAMETER OutFile This must be specified when using -ExportDatabaseStructure switch. This specifies the CSV file to write to. Must include the path. .PARAMETER MoveFromCSV This switch indicate that you will specify an CSV input file using the -InputFile parameter to say which files want to move. .PARAMETER InputFile, This must be specified when using -ExportDatabaseStructure switch. This specifies the CSV file to read from. Must include the path. .PARAMETER CheckFileHash This switch allows a validation using file's hashes. Generate hash for source and destination files and check if is the same. This may take a long time for bigger files. .PARAMETER NoDbccCheckDb If this switch is used the DBCC CHECK DB will be skipped. USE THIS WITH CARE! You may want to use this switch if your database is big. But you should execute the command after. .PARAMETER DeleteSourceFiles If this switch is used the source files will be deleted after database comes online with success. .PARAMETER Force This switch will continue to perform rest of the actions even if DBCC produces an error. .NOTES Original Author: Cláudio Silva (@ClaudioESSilva) dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. References: Copy-WithProgress using robocopy -> http://stackoverflow.com/questions/13883404/custom-robocopy-progress-bar-in-powershell Thanks to Trevor Sullivan's A excellent example behind most of this robocopy code. .LINK https://dbatools.io/Move-SqlDatabaseFile .EXAMPLE Move-SqlDatabaseFile -SqlServer sqlserver2014a -Databases db1 Will show a grid to select the file(s), then a treeview to select the destination path and perform the file copy .EXAMPLE Move-SqlDatabaseFile -SqlServer sqlserver2014a -Databases db1 -ExportDatabaseStructure -OutFile "C:\temp\files.csv" Will generate a files.csv files to C:\temp folder with the list of all files within database 'db1'. This file will have an empty column called 'DestinationFolderPath' that should be filled by user and run the command again passing this file. .EXAMPLE Move-SqlDatabaseFile -SqlServer sqlserver2014a -Databases db1 -FileType DATA Will show a treeview to select the destination path and perform the file copy of every file of DATA (ROWS) type .EXAMPLE Move-SqlDatabaseFile -SqlServer sqlserver2014a -Databases db1 -DeleteSourceFiles Will show a grid to select the file(s), then a treeview to select the destination path and perform the move (copy&paste&delete) every selected file .EXAMPLE Move-SqlDatabaseFile -SqlServer sqlserver2014a -Databases db1 -NoDbccCheckDb Will show a grid to select the file(s), then a treeview to select the destination path and perform the copy every selected file. Will NOT perform a DBCC CHECKDB! Usefull if you want to run it manually (for example, because database is big and will take too much time) .EXAMPLE Move-SqlDatabaseFile -SqlServer sqlserver2014a -Databases db1 -CheckFileHash Will show a grid to select the file(s), then a treeview to select the destination path and perform the copy every selected file. Will perform a file hash validation for each file after his copy. Will perform a DBCC CHECKDB! Usefull if you want to run it manually (for example, because database is big and will take too much time) #> [CmdletBinding(SupportsShouldProcess = $true, DefaultParameterSetName="Default")] Param ( [parameter(Mandatory = $true, ValueFromPipeline = $true)] [Alias("ServerInstance", "SqlInstance")] [object]$SqlServer, [object]$SqlCredential, [parameter(Mandatory = $true, ParameterSetName = "ExportDatabaseStructure")] [switch]$ExportDatabaseStructure, [parameter(Mandatory = $true, ParameterSetName = "ExportDatabaseStructure")] [Alias("OutFilePath", "OutputPath")] [string]$OutFile, [parameter(Mandatory = $true, ParameterSetName = "MoveFromCSV")] [switch]$MoveFromCSV, [parameter(Mandatory = $true, ParameterSetName = "MoveFromCSV")] [Alias("InputFilePath", "InputPath")] [string]$InputFile, [switch]$CheckFileHash, [switch]$NoDbccCheckDb, [switch]$DeleteSourceFiles, [switch]$Force ) DynamicParam { if ($sqlserver) { $dbparams = Get-ParamSqlDatabases -SqlServer $SqlServer -SqlCredential $SqlCredential $allparams = Get-ParamSqlDatabaseFileTypes -SqlServer $SqlServer -SqlCredential $SqlCredential $null = $allparams.Add("Databases", $dbparams.Databases) return $allparams } } BEGIN { function Get-SqlFileStructure { if ($server.versionMajor -eq 8) { $sql = "select DB_NAME (dbid) as dbname, name, filename, CAST(mf.Size * 8 AS DECIMAL(20,2)) AS sizeKB, '' AS Drive, '' AS DestinationFolderPath, groupid from sysaltfiles" } else { $sql = "SELECT db.name AS dbname, type_desc AS FileType, mf.name, Physical_Name AS filename, CAST(mf.Size * 8 AS DECIMAL(20,2)) AS sizeKB, '' AS Drive, '' AS DestinationFolderPath FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id" } $dbfiletable = $server.ConnectionContext.ExecuteWithResults($sql) $ftfiletable = $dbfiletable.Tables[0].Clone() $dbfiletable.Tables[0].TableName = "data" foreach ($db in $databaselist) { # Add support for Full Text Catalogs in Sql Server 2005 and below if ($server.VersionMajor -lt 10) { #$dbname = $db.name $fttable = $null = $server.Databases[$database].ExecuteWithResults('sp_help_fulltext_catalogs') foreach ($ftc in $fttable.Tables[0].rows) { $name = $ftc.name $physical = $ftc.Path $logical = "sysft_$name" $null = $ftfiletable.Rows.add($database, "FULLTEXT", $logical, $physical) } } } $null = $dbfiletable.Tables.Add($ftfiletable) return $dbfiletable } function Set-SqlDatabaseOffline { if ($PSCmdlet.ShouldProcess($database, "Set database offline")) { Write-Output "Set database '$database' Offline!" $server.ConnectionContext.ExecuteNonQuery("ALTER DATABASE [$database] SET OFFLINE WITH ROLLBACK IMMEDIATE") | Out-Null do { $server.Databases[$database].Refresh() Start-Sleep -Seconds 1 $WaitingTime += 1 Write-Verbose "Database status: $($server.Databases[$database].Status.ToString())" Write-Verbose "Waiting for database become offline: $WaitingTime seconds passed" } while (($server.Databases[$database].Status.ToString().Contains("Offline") -eq $false) -and $WaitingTime -le 10) #Validate if ($server.Databases[$database].Status.ToString().Contains("Offline") -eq $false) { throw "Cannot set database '$database' in OFFLINE status." } else { Write-Output "Database set OFFLINE successfull! Actual state: '$($server.Databases[$database].Status.ToString())'" } } } function Set-SqlDatabaseOnline { if ($PSCmdlet.ShouldProcess($database, "Set database online")) { Write-Output "Set database '$database' Online!" try { $server.ConnectionContext.ExecuteNonQuery("ALTER DATABASE [$database] SET ONLINE") | Out-Null } catch { Write-Warning $_ return $false } $WaitingTime = 0 do { $server.Databases[$database].Refresh() Start-Sleep -Seconds 1 $WaitingTime += 1 Write-Output "Database status: $($server.Databases[$database].Status.ToString())" Write-Output "WaitingTime: $WaitingTime" } while (($server.Databases[$database].Status.ToString().Contains("Normal") -eq $false) -and $WaitingTime -le 10) } if ($server.Databases[$database].Status.ToString().Contains("Normal") -eq $false) { throw "Database is not in Online status." } else { $server.Databases[$database].Status.ToString() } Write-Output "Database '$database' in Online!" if ($NoDbccCheckDb -eq $false) { Write-Output "Starting Dbcc CHECKDB for $dbname on $source" $dbccgood = Start-DbccCheck -Server $server -DBName $dbname if ($dbccgood -eq $false) { if ($force -eq $false) { Write-Output "DBCC failed for $dbname (you should check that). Aborting routine for this database" continue } else { Write-Output "DBCC failed, but Force specified. Continuing." } } } else { Write-Warning "DBCC skipped. -NoDbccChecDB switch was used." } return $true } function Set-SqlDatabaseFileLocation { Param ( [parameter(Mandatory = $true)] [string]$Database, [parameter(Mandatory = $true)] [string]$LogicalFileName, [parameter(Mandatory = $true)] [string]$PhysicalFileLocation ) if ($PSCmdlet.ShouldProcess($database, "Modifying file '$LogicalFileName' location to '$PhysicalFileLocation'")) { Write-Output "Modifying file path to new location" try { $server.ConnectionContext.ExecuteNonQuery("ALTER DATABASE [$database] MODIFY FILE (NAME = $LogicalFileName, FILENAME = '$PhysicalFileLocation');") | Out-Null } catch { Write-Exception $_ } } } function Compare-FileHashes { <# .SYNOPSIS Get file's hashes and compare them Return boolean value #> Param ( [parameter(Mandatory = $true)] [string]$SourceFilePath, [parameter(Mandatory = $true)] [string]$DestinationFilePath ) Write-Output "Comparing file hash". $SourceHash = Get-FileHash -FilePath $SourceFilePath $DestinationHash = Get-FileHash -FilePath $DestinationFilePath Write-Verbose "SourceHash : $SourceHash" Write-Verbose "DestinationHash: $DestinationHash" $SameHash = $SourceHash -eq $DestinationHash Write-Verbose "Source file hash is equal?: $SameHash" return $SameHash } function Get-FileHash { <# .SYNOPSIS Generate a file hash .NOTES This can take some time on larger files. #> Param ( [parameter(Mandatory = $true)] [string]$FilePath ) if ($PSCmdlet.ShouldProcess($sourcenetbios, "Generating hash for file '$FilePath'")) { Write-Output "Generating hash for file: '$FilePath'" $stream = New-Object io.FileStream ($FilePath, 'open') $Provider = New-Object System.Security.Cryptography.MD5CryptoServiceProvider $Hash = New-Object System.Text.StringBuilder if ($stream) { foreach ($byte in $Provider.ComputeHash($stream)) { [Void] $Hash.Append($byte.ToString("X2")) } $stream.Close() } return $Hash } } #Maybe turn this into sharedfunction Function Start-DbccCheck { param ( [object]$server, [string]$dbname ) $servername = $server.name $db = $server.databases[$dbname] if ($Pscmdlet.ShouldProcess($sourceserver, "Running dbcc check on $dbname on $servername")) { try { $null = $db.CheckTables('None') Write-Output "Dbcc CHECKDB finished successfully for $dbname on $servername" } catch { Write-Warning "DBCC CHECKDB failed" Write-Exception $_ if ($force) { return $true } else { return $false } } } } Function Remove-OldFile { <# .SYNOPSIS Remove source file .DESCRIPTION To run after database come online with success! Verify if both files exists. Then remove the old file. #> Param ( [string]$SourceFilePath, [string]$DestinationFilePath ) if (@("Local_Robocopy","Local_Bits", "UNC_Robocopy", "UNC_Bits") -contains $copymethod) { #Verify if file exists on both folders (source and destination) if ((Test-SqlPath -SqlServer $server -Path $DestinationFilePath) -and (Test-SqlPath -SqlServer $server -Path $SourceFilePath)) { try { #TODO: ONLY REMOVE FILES AFTER BRINGONLINE & DBCC CHECKDB?? #Delete old file already copied to the new path Write-Output "Deleting file '$SourceFilePath'" if ($PSCmdlet.ShouldProcess($sourcenetbios, "Deleting file '$SourceFilePath'")) { Remove-Item -Path $SourceFilePath } Write-Output "File '$SourceFilePath' deleted" } catch { Write-Warning "Can't delete the file '$SourceFilePath'. Delete it manualy" continue } } else { Write-Warning "File $SourceFilePath does not exists! No file copied!" } } else #remotely { #Delete old file already copied to the new path Write-Output "Deleting file '$SourceFilePath' remotely" $scriptblock = { param($SourceFilePath) #Verify if file exists on both folders (source and destination) if ((Test-Path -Path $DestinationFilePath) -and (Test-Path -Path $SourceFilePath)) { try { #Delete old file already copied to the new path Write-Output "Deleting file '$SourceFilePath'" Remove-Item -Path $SourceFilePath Write-Output "File '$SourceFilePath' deleted" } catch { Write-Warning "Can't delete the file '$SourceFilePath'. Delete it manualy." continue } } else { Write-Warning "File $SourceFilePath does not exists! No file copied!" } } if ($PSCmdlet.ShouldProcess($sourcenetbios, "Deleting file '$SourceFilePath'")) { Invoke-Command -Session $remotepssession -ScriptBlock $scriptblock -ArgumentList $SourceFilePath } } } Function Test-PathsAccess { <# .SYNOPSIS Will test if we have access to the specified paths. .DESCRIPTION Will create a file and delete it. If can't delete will warn about it. #> Param ( [object]$PathsToUse ) $ArrayList = @() [System.Collections.ArrayList]$PathsAlreadyTested = $ArrayList foreach ($Path in $PathsToUse) { try { $ValidPath = !([string]::IsNullOrEmpty($($Path.DestinationFolderPath))) $DestinationFolderPath = $Path.DestinationFolderPath if ($ValidPath) { if ($DestinationFolderPath -eq $Path.SourceFoldePath) { Write-Warning "Destination path for file '$LogicalName' is the same of source path. Skipping" continue } $dummyFilePath = "$DestinationFolderPath\DBATools_dummy$(Get-Date -Format 'yyyyMMddhhmmss').log" if ($PathsAlreadyTested.Contains($DestinationFolderPath)) { continue } if ($copymethod -ne "PSSession_Remote") { if ($PSCmdlet.ShouldProcess($sourcenetbios, "Test file creation on '$dummyFilePath'")) { $null = New-Item -ItemType File -Path $dummyFilePath } Write-Verbose "Can access on destination path '$dummyFilePath'." try { if ($PSCmdlet.ShouldProcess($sourcenetbios, "Deleting file '$dummyFilePath'")) { Remove-Item -Path $dummyFilePath } } catch { Write-Warning "Can't delete dummy file '$dummyFilePath'. Please delete it manually." } } else { $scriptblock = { param($FilePath) New-Item -ItemType File -Path $FilePath Write-Output "Can access on destination path." try { Remove-Item -Path $FilePath } catch { Write-Warning "Can't delete dummy file '$FilePath'. Please delete it manually." } } if ($PSCmdlet.ShouldProcess($sourcenetbios, "Test file creation on '$dummyFilePath'")) { Invoke-Command -Session $remotepssession -ScriptBlock $scriptblock -ArgumentList $dummyFilePath } } $null = $PathsAlreadyTested.Add($DestinationFolderPath) } else { Write-Warning "The specified path '$DestinationPathToUse' is not valid." Write-Exception $_ } } catch { Write-Error $_ Write-Warning "Can't create files on path '$DestinationPathToUse'" continue } } } Function Disconnect-RemovePSSession { if ($PSCmdlet.ShouldProcess($sourcenetbios, "Disconnect and removing PSSession '$($remotepssession.Id)'")) { Write-Verbose "Disconnect-PSSession" Disconnect-PSSession $remotepssession.Id Write-Verbose "Removing PSSession with id $($remotepssession.Id)" Remove-PSSession $remotepssession.Id } } Function Check-SpaceRequirements { #Verify file size and check if destination drive have sufficient freespace try { if ($PSCmdlet.ShouldProcess($sourcenetbios, "Getting drives free space using Get-DbaDiskSpace command")) { Write-Output "Getting drives free space using Get-DbaDiskSpace command." [object]$AllDrivesFreeDiskSpace = Get-DbaDiskSpace -ComputerName $sourcenetbios -Unit KB | Select-Object Name, FreeInKB #1st Get all drives/luns from files to move foreach ($DBFile in $FilesToMove) { #Verfiy path using Split-Path on $logfile.FileName in backwards. This way we will catch the LUNs. Example: "K:\Log01" as LUN name $DrivePath = Split-Path $DBFile.FileName -parent Do { if ($AllDrivesFreeDiskSpace | Where-Object {$DrivePath -eq "$($_.Name)"}) { #$TotalTLogFreeDiskSpaceKB = ($AllDrivesFreeDiskSpace | Where-Object {$DrivePath -eq $_.Name}).SizeInKB $DBFile.Drive = $DrivePath $match = $true break } else { $match = $false $DrivePath = Split-Path $DrivePath -parent } } while (!$match -or ([string]::IsNullOrEmpty($DrivePath))) } #2nd Group size by drive/lun $TotalSpaceNeeded = $FilesToMove ` | Group-Object Drive ` | Select-Object Name, ` @{Name=‘TotalSpaceNeeded’;Expression={($_.Group | Measure-Object sizeKB -Sum).Sum}} #3rd compare with $InstanceSpace luns free space foreach ($Drive in $TotalSpaceNeeded) { [long]$FreeDiskSpace = ($AllDrivesFreeDiskSpace | Where-Object {$Drive.Name -eq $_.Name}).FreeInKB.ToString().Replace(".", "") $FreeDiskSpaceMB = [math]::Round($($FreeDiskSpace / 1024), 2) $TotalSpaceNeededMB = [math]::Round($($Drive.TotalSpaceNeeded / 1024), 2) if ($Drive.TotalSpaceNeeded -le $FreeDiskSpace) { Write-Output "Drive '$($Drive.Name)' has sufficient free space ($FreeDiskSpaceMB MB) for all files to be copied (Space needed: $($Drive.TotalSpaceNeeded / 1024) MB)'" } else { throw "Drive '$($Drive.Name)' does not have sufficient space available. Needed: '$TotalSpaceNeededMB MB'. Existing: $FreeDiskSpaceMB MB. Quitting" } } Write-Output "Space requirements checked!" } } catch { Write-Exception $_ } } Function Get-PSSessionRobocopyLogContent { $scriptblock = { param($RobocopyLogPath) $file = [System.io.File]::Open($RobocopyLogPath, 'Open', 'Read', 'ReadWrite') $reader = New-Object System.IO.StreamReader($file) #done this way to replicate Get-Content output (is a collection :)) $text = @() while(($line = $reader.ReadLine()) -ne $null) { $text+= "$line" } $reader.Close() $file.Close() return $text } if ($PSCmdlet.ShouldProcess($sourcenetbios, "Reading robocopy log content using PSSession id '$($remotepssession.Id)'")) { $PSSessionRoboCopyLogContent = Invoke-Command -Session $remotepssession -ScriptBlock $scriptblock -ArgumentList $RobocopyLogPath return $PSSessionRoboCopyLogContent } } $server = Connect-SqlServer -SqlServer $SqlServer -SqlCredential $SqlCredential $source = $server.DomainInstanceName $Databases = $psboundparameters.Databases $FileType = $psboundparameters.FileType if ($Filetype -eq 'DATA') { $Filetype = 'ROWS' } } PROCESS { Write-Output "Get database file inventory" $filestructure = Get-SqlFileStructure Write-Output "Resolving NetBIOS name" $sourcenetbios = Resolve-NetBiosName $server Write-Output "SourceNetBios: $sourcenetbios" foreach ($database in $Databases) { if ($server.Databases["$database"]) { $where = "dbname = '$database'" if ($FileType.Length -gt 0) { $where = "$where and filetype = '$filetype'" } $files = $filestructure.Tables.Select($where) } else { Write-Warning "Database '$database' does not exists on server $($Server.name)" } } if ($ExportDatabaseStructure) { if (($OutFile.Length -gt 0)) #-and (!(Test-Path -Path $OutFile))) { $files | Export-Csv -LiteralPath $OutFile -NoTypeInformation Write-Output "Edit the file $OutFile. Keep only the rows matching the fies you want to move. Fill 'DestinationFolderPath' column for each file (path only).`r`n" Write-Output "Use the following command to move the files:`r`nMove-SqlDatabaseFile -SqlServer $SqlServer -Databases $database -MoveFromCSV -InputFilePath '$OutFile'" return } else { throw "The choosed file path does not exists" } } $FilesToMove = @() if ($MoveFromCSV) { if (($InputFile.Length -gt 0) -and (Test-Path -Path $InputFile)) { $FilesToMove = Import-Csv -LiteralPath $InputFile } else { throw "The choosed file path does not exists" } } else { if (([string]::IsNullOrEmpty($FileType))) { $FilesToMove = $files | Out-GridView -PassThru -Title "Select one or more files to move:" } else { Write-Output "Will move all files of type '$FileType'" $FilesToMove = $files } if (@($FilesToMove).Count -gt 0) { # This will go above if ($filepath.length -eq 0) { #Open dialog box with GUI $filepathToMove = Show-SqlServerFileSystem -SqlServer $server -SqlCredential $SqlCredential -Whatif:$false if ($filepathToMove.length -le 0) { throw "No path was chosen." return } foreach ($File in $FilesToMove) { $File.DestinationFolderPath = $filepathToMove } } else { #Need to move to PS-Session block $exists = Test-SqlPath -SqlServer $server -Path $FilePath if ($exists -eq $false) { throw "Directory does not exist" } } } else { throw "No files were selected!" } } <# Validate type of copy Can be (using this order): - Local copy: Use Robocopy. If not exits use Start-BitsTransfer - Remote copy If user have access to UNC paths (\\) uses robocopy If not, use Remote Session (uses robocopy on the machine if exists) #> $start = [System.Diagnostics.Stopwatch]::StartNew() #test if robocopy exists locally try { $testRobocopyExistance = robocopy $RobocopyExists = $true $copymethod = "Local_Robocopy" Write-Output "Robocopy exists locally." } catch { Write-Exception $_ $RobocopyExists = $false $copymethod = "Local_Bits" Write-Output "Cannot find robocopy." } if ($env:computername -eq $sourcenetbios) { if ($RobocopyExists) { $copymethod = "Local_Robocopy" } else { $copymethod = "Local_Bits" } } else { #Check if have permission to UNC path (this will be checked again for each file that needs to be move) if (Test-Path -Path $(Join-AdminUnc -servername $sourcenetbios -FilePath $(@($FilesToMove).Item(0).DestinationFolderPath)) -IsValid) { if ($RobocopyExists) { $copymethod = "UNC_Robocopy" } else { $copymethod = "UNC_Bits" } } else { # Test for WinRM #Test-WinRM neh. if ($PSCmdlet.ShouldProcess($sourcenetbios, "Testing remotee connection to '$sourcenetbios'")) { winrm id -r:$sourcenetbios 2>$null | Out-Null if ($LastExitCode -eq 0) { $remotepssession = New-PSSession -ComputerName $sourcenetbios if([string]::IsNullOrEmpty($remotepssession)) { throw "Can't create remote PowerShell session on $sourcenetbios. Quitting." } else { Write-Output "LastExitCode: $LastExitCode" Write-Verbose "Created remote pssession id: $($remotepssession.Id)" Write-Output "Verifying if robocopy.exe exists on default path." $RemoteRobocopyExists = Invoke-Command -Session $remotepssession -ScriptBlock {robocopy} -ErrorAction SilentlyContinue if ($RemoteRobocopyExists) { $copymethod = "PSSession_Remote" #Write-Output "Using Robocopy to copy the files" } else { #Disconnect and remove PSSession Disconnect-RemovePSSession throw "Robocopy does not exists on remote machine '$sourcenetbios'. Quitting." } } } else { throw "Remote PowerShell access not enabled on $sourcenetbios or access denied. Windows admin acccess required. Quitting." } } else { $copymethod = "PSSession_Remote" } } } #Add support columns to collection $FilesToMove | Add-Member -NotePropertyName FileToCopy -NotePropertyValue "" $FilesToMove | Add-Member -NotePropertyName SourceFilePath -NotePropertyValue "" $FilesToMove | Add-Member -NotePropertyName SourceFolderPath -NotePropertyValue "" $FilesToMove | Add-Member -NotePropertyName DestinationFilePath -NotePropertyValue "" #DestinationFolderPath already exists #To use when changing file location metadata $FilesToMove | Add-Member -NotePropertyName LocalDestinationFilePath -NotePropertyValue "" $FilesToMove | Add-Member -NotePropertyName LocalDestinationFolderPath -NotePropertyValue "" #Says if file is already handled with success. Used to print files to delete $FilesToMove | Add-Member -NotePropertyName SuccefullHandled -NotePropertyValue $false #Format files accordingly with copy type foreach ($file in $FilesToMove) { $fileToCopy = Split-Path -Path $($file.FileName) -leaf $file.FileToCopy = $fileToCopy Write-Host "DestinationFolderPath: $($file.DestinationFolderPath)" Write-Host "DestinationFolderPath: $fileToCopy" $file.LocalDestinationFilePath = [System.IO.Path]::Combine($file.DestinationFolderPath,$fileToCopy) #$file.LocalDestinationFilePath = Join-Path $file.DestinationFolderPath $fileToCopy - $file.LocalDestinationFolderPath = $file.DestinationFolderPath if (@("UNC_Robocopy", "UNC_Bits") -contains $copymethod) { $file.SourceFilePath = Join-AdminUnc -servername $sourcenetbios -FilePath $($file.FileName) $ManageUNCPath = Join-AdminUnc -servername $sourcenetbios -FilePath $(Split-Path -Path $($file.FileName)) if($ManageUNCPath.EndsWith("$\")) { $ManageUNCPath = $ManageUNCPath.TrimEnd("\") } $file.SourceFolderPath = $ManageUNCPath $FileDestinationFolderPathFilename = [System.IO.Path]::Combine($file.DestinationFolderPath,$fileToCopy) $file.DestinationFilePath = Join-AdminUnc -servername $sourcenetbios -FilePath $FileDestinationFolderPathFilename #$file.DestinationFilePath = Join-AdminUnc -servername $sourcenetbios -FilePath $(Join-Path $file.DestinationFolderPath $fileToCopy) $ManageUNCPath = Join-AdminUnc -servername $sourcenetbios -FilePath $file.DestinationFolderPath if($ManageUNCPath.EndsWith("$\")) { $ManageUNCPath = $ManageUNCPath.TrimEnd("\") } $file.DestinationFolderPath = $ManageUNCPath #TODO } else { $file.SourceFilePath = $file.FileName $file.SourceFolderPath = $(Split-Path -Path $($file.FileName)) $file.DestinationFilePath = Join-Path $file.DestinationFolderPath $fileToCopy } } Test-PathsAccess -PathsToUse $FilesToMove Check-SpaceRequirements #Get number of files to move $FilesCount = @($FilesToMove).Count #TODO: REMOVE #$copymethod = "Local_Bits1" #$RobocopyExists = $false if (@("Local_Robocopy","Local_Bits", "UNC_Robocopy", "UNC_Bits") -contains $copymethod) { Write-Output "You are running this command locally." switch ($copymethod) { "Local_Robocopy" { Write-Output "We will use robocopy as copy method." break } "Local_Bits" { Write-Output "We will use BitsTransfer as copy method." break } "UNC_Robocopy" { Write-Output "We will use robocopy with UNC paths as copy method." break } "UNC_Bits" { Write-Output "We will use BitsTransfer with UNC paths as copy method." break } } $filesProgressbar = 0 #Call function to set database offline Set-SqlDatabaseOffline foreach ($file in $FilesToMove) { $filesProgressbar += 1 #$file.FileToCopy #$file.SourceFilePath #$file.SourceFolderPath #$file.DestinationFilePath #$file.DestinationFolderPath #$file.LocalDestinationFilePath #$file.LocalDestinationFolderPath $dbName = $file.dbname $LogicalName = $file.Name $FileToCopy = $file.FileToCopy $LocalFilePath = $file.filename $SourceFilePath = $file.SourceFilePath $SourceFolderPath = $file.SourceFolderPath $DestinationFilePath = $file.DestinationFilePath $DestinationFolderPath = $file.DestinationFolderPath $LocalDestinationFilePath = $file.LocalDestinationFilePath $LocalDestinationPath = $file.LocalDestinationFolderPath Write-Progress ` -Id 1 ` -Activity "Copying file: '$FileToCopy' on database: '$dbName'" ` -PercentComplete ($filesProgressbar / $FilesCount * 100) ` -Status "Copying - $filesProgressbar of $FilesCount files" if (!(Test-SqlPath -SqlServer $server -Path $LocalFilePath))#$SourceFilePath)) { Write-Warning "Source file or path for logical name '$LogicalName' does not exists. '$LocalFilePath'" Continue } if (($LocalDestinationPath -eq $SourceFolderPath) -or ([string]::IsNullOrEmpty($LocalDestinationPath))) { Write-Warning "Destination path for file '$LogicalName' is the same of source path or is empty. Skipping" continue } Write-Verbose "Copy file from path: $SourceFolderPath" Write-Verbose "Copy file to path: $DestinationFolderPath" Write-Verbose "Copy file: $fileToCopy" Write-Verbose "DestinationPath and filename: $DestinationFilePath" try { $startRC = [System.Diagnostics.Stopwatch]::StartNew() if ($RobocopyExists) { # MIR = Mirror mode # NP = Don't show progress percentage in log # NC = Don't log file classes (existing, new file, etc.) # BYTES = Show file sizes in bytes # NJH = Do not display robocopy job header (JH) # NJS = Do not display robocopy job summary (JS) # TEE = Display log in stdout AND in target log file $CommonRobocopyParams = '/ndl /TEE /bytes /NC /COPY:DATS /R:10 /W:3'; #/MT:2 $RobocopyLogPath = "$env:windir\temp\$((Get-Date -Format 'yyyyMMddhhmmss'))Robocopy.log" #format this way because the double-quotes "" $ArgumentList = '"{0}" "{1}" "{2}" /LOG:"{3}" {4}' -f $SourceFolderPath, $DestinationFolderPath, $FileToCopy, $RobocopyLogPath, $CommonRobocopyParams; Write-Verbose "Beginning the robocopy process with arguments: $ArgumentList" if ($PSCmdlet.ShouldProcess($sourcenetbios, "Executing robocopy to copy file: '$FileToCopy' from '$SourceFolderPath' to '$DestinationFolderPath'")) { $Robocopy = Start-Process -FilePath robocopy.exe -ArgumentList $ArgumentList -Verbose -PassThru -NoNewWindow Start-Sleep -Milliseconds 100; Write-Output 'Waiting for file copies to complete...' do { $LogContent = Get-Content -Path $RobocopyLogPath; $RobocopyLogFiltered = $LogContent -match "^\s*(\d+)\s+(\S+)" if ($RobocopyLogFiltered -ne $Null ) { if ($LogContent[-1] -match "(100|\d?\d\.\d)\%") { Write-progress -Id 2 -ParentId 1 -Activity "Progress" -PercentComplete $LogContent[-1].Split("%")[0] $LogContent[-1] } else { if ($LogContent[-1].StartsWith("Waiting")) { Write-Warning "$($LogContent[-3]) - $($LogContent[-1])" Start-Sleep -Milliseconds 3000; } else { Write-progress -Id 2 -ParentId 1 -Activity "Progress" -Complete } } } Start-Sleep -Milliseconds 250; } while (!$Robocopy.HasExited) #Get content one last time to verify if it finished by "RETRY LIMIT EXCEEDED" $LogContent = Get-Content -Path $RobocopyLogPath; if ($LogContent | Where-Object { $_ -match "ERROR: RETRY LIMIT EXCEEDED." }) { $file.SuccefullHandled = $false Write-Warning "Can not copy file '$FileToCopy'. Please confirm that you have permissions to paths '$SourceFolderPath' and '$DestinationFolderPath'" continue } else { $file.SuccefullHandled = $true } Write-progress -Id 2 -ParentId 1 "Progress" -Complete } } else { try { if ($PSCmdlet.ShouldProcess($sourcenetbios, "Executing Start-BitsTransfer to transfer file '$FileToCopy' from '$SourceFolderPath' to '$DestinationFolderPath'")) { $BITSoutput = Start-BitsTransfer -Source $SourceFilePath -Destination $LocalDestinationFilePath -RetryInterval 60 -RetryTimeout 60 ` -DisplayName "Copying file" -Description "Copying '$FileToCopy' to '$DestinationFolderPath' on '$sourcenetbios'" } $file.SuccefullHandled = $true } catch { Write-Error $_ $file.SuccefullHandled = $false } } $totaltimeRC= ($startRC.Elapsed) Write-Output "Total elapsed time for copying '$FileToCopy' with robocopy: $totaltimeRC" if ($CheckFileHash) { if (Compare-FileHashes -SourceFilePath $SourceFilePath -DestinationFilePath $DestinationFilePath) { Write-Output "File copy OK! Hash is the same for both files." } else { Write-Verbose "File copy NOK! Hash is not the same." Write-Verbose "Deleting destination file '$DestinationFilePath'!" Remove-Item -Path $DestinationFilePath Write-Output "File '$DestinationFilePath' deleted" } } else { Write-Warning "The switch -CheckFileHash was not specified." } Write-Verbose "Change file path for logical file '$LogicalName' to '$DestinationFilePath'" Set-SqlDatabaseFileLocation -Database $dbName -LogicalFileName $LogicalName -PhysicalFileLocation $LocalDestinationFilePath Write-Verbose "File path changed" } catch { Write-Exception $_ } } Write-Progress ` -Id 1 ` -Activity "Files copied!"` -Complete } else #$copymethod = "PSSession_Remote" { Write-Output "You are running this command remotely. Will try use Remote PS Session with robocopy to copy the files." Set-SqlDatabaseOffline foreach ($file in $FilesToMove) { if ($PSCmdlet.ShouldProcess($sourcenetbios, "Connecting using Connect-PSSession")) { Connect-PSSession -Session $remotepssession } $filesProgressbar += 1 $dbName = $file.dbname $LogicalName = $file.Name $FileToCopy = $file.FileToCopy $SourceFilePath = $file.SourceFilePath $SourceFolderPath = $file.SourceFolderPath $DestinationFilePath = $file.DestinationFilePath $DestinationFolderPath = $file.DestinationFolderPath $LocalDestinationFilePath = $file.LocalDestinationFilePath $LocalDestinationPath = $file.LocalDestinationFolderPath Write-Progress ` -Id 1 ` -Activity "Working on file: $LogicalName on database: '$dbName'" ` -PercentComplete ($filesProgressbar / $FilesCount * 100) ` -Status "Processing - $filesProgressbar of $FilesCount files" if (!(Test-SqlPath -SqlServer $server -Path $SourceFilePath)) { Write-Warning "Source file or path for logical name '$LogicalName' does not exists. '$SourceFilePath'" Continue } if (($LocalDestinationPath -eq $SourceFolderPath) -or ([string]::IsNullOrEmpty($LocalDestinationPath))) { Write-Warning "Destination path for file '$LogicalName' is the same of source path or is empty. Skipping" continue } Write-Verbose "Using RemoteSession - Copy file from path: $SourceFolderPath" Write-Verbose "Using RemoteSession - Copy file to path: $DestinationPath" Write-Verbose "Using RemoteSession - Copy file: $fileToCopy" Write-Verbose "Using RemoteSession - DestinationPath and filename: $DestinationFilePath" # MIR = Mirror mode # NP = Don't show progress percentage in log # NC = Don't log file classes (existing, new file, etc.) # BYTES = Show file sizes in bytes # NJH = Do not display robocopy job header (JH) # NJS = Do not display robocopy job summary (JS) # TEE = Display log in stdout AND in target log file $CommonRobocopyParams = '/ndl /TEE /bytes /nfl /COPY:DATS /L /R:10 /W:3' $RobocopyLogPath = "$env:windir\temp\$((Get-Date -Format 'yyyyMMddhhmmss'))Robocopy.log" Write-Verbose "RobocopyLogPath: $RobocopyLogPath" $CommonRobocopyParams = '/ndl /TEE /bytes /NC' $ArgumentList = '"{0}" "{1}" "{2}" /LOG:"{3}" {4}' -f $SourceFolderPath, $LocalDestinationPath, $fileToCopy, $RobocopyLogPath, $CommonRobocopyParams Write-Verbose "Execution arguments: $ArgumentList" if ($PSCmdlet.ShouldProcess($sourcenetbios, "Executing robocopy to copy file: '$fileToCopy'")) { $scriptblock = {param($ArgumentList) Start-Process robocopy -PassThru -WindowStyle Hidden -ArgumentList $ArgumentList} $CopyList = Invoke-Command -Session $remotepssession -ScriptBlock $scriptblock -ArgumentList $ArgumentList Start-Sleep -Milliseconds 500 Write-Output 'Waiting for file copies to complete...' do { Start-Sleep -Milliseconds 100 $scriptblock = {Get-Process "robocopy*"} $CopyList = Invoke-Command -Session $remotepssession -ScriptBlock $scriptblock $LogContent = Get-PSSessionRobocopyLogContent $RobocopyLogFiltered = $LogContent -match "^\s*(\d+)\s+(\S+)" if ($RobocopyLogFiltered -ne $Null ) { if ($LogContent[-1] -match "(100|\d?\d\.\d)\%") { Write-progress -Id 2 -ParentId 1 -Activity "Progress" -PercentComplete $LogContent[-1].Split("%")[0] $LogContent[-1] } else { if ($LogContent[-1].StartsWith("Waiting")) { Write-Warning "$($LogContent[-3]) - $($LogContent[-1])" Start-Sleep -Milliseconds 3000; } else { Write-progress -Id 2 -ParentId 1 -Activity "Progress" -Complete } } } Start-Sleep -Milliseconds 250 } while (@($CopyList | Where-Object {$_.HasExited -eq $false}).Count -gt 0) #Get content one last time to verify if it finished by "RETRY LIMIT EXCEEDED" $LogContent = Get-PSSessionRobocopyLogContent if ($LogContent | Where-Object { $_ -match "ERROR: RETRY LIMIT EXCEEDED." }) { $file.SuccefullHandled = $false Write-Warning "Can not copy file '$FileToCopy'. Please confirm that you have permissions to paths '$SourceFolderPath' and '$DestinationFolderPath'" continue } else { $file.SuccefullHandled = $true } Write-progress -Id 2 -ParentId 1 "Progress" -Complete } Write-Verbose "Change file path for logical file '$LogicalName' to '$DestinationFilePath'" Set-SqlDatabaseFileLocation -Database $dbName -LogicalFileName $LogicalName -PhysicalFileLocation $LocalDestinationFilePath Write-Verbose "File path changed" } } Write-Verbose "Copy done! Lets bring database Online!" $resultDBOnline = Set-SqlDatabaseOnline if ($resultDBOnline) { Write-Verbose "Database online!" } else { Write-Verbose "Some error happened! Check logs." throw "Some error happened! Check logs." } if ($DeleteSourceFiles) { Write-Output "The switch -DeleteSourceFiles was specified. Deleting source files." foreach ($file in $FilesToMove) { Remove-OldFile -SourceFilePath $($file.SourceFilePath) -DestinationFilePath $($file.LocalDestinationFilePath) } } else { if ($FilesToMove | Where-Object { $_.SuccefullHandled -eq $true}) { Write-Warning "The -DeleteSourceFiles switch was not specified.`r`nSource files were not deleted! You need to manualy deleted all files copied.`r`nAfter you check that everything is OK, you can run the following command(s)." foreach ($file in $FilesToMove | Where-Object { $_.SuccefullHandled -eq $true}) { Write-Output "`r`nRemove-Item -Path ""$($file.SourceFilePath)""" } } } } # END is to disconnect from servers and finish up the script. When using the pipeline, things in here will be executed last and only once. END { $server.ConnectionContext.Disconnect() $totaltime = ($start.Elapsed) Write-Output "Total Elapsed time: $totaltime" #If remote session. Clear if ($copymethod -eq "PSSession_Remote") { #Disconnect and remove PSSession Disconnect-RemovePSSession } } } |