Microsoft SQL Cloning Database to a Different DB Name

# User defined variables
$sourceSqlServer='DEV-SQL01'
$sourceDatabaseName='TEST_MSCRM'
$sourceSaCredential=$(get-credential)
$destinationSqlServer='DEV-SQL01'
$destinationDatabaseName='Test_MSCRM'
$destinationSaCredential=$(get-credential)

function copyDatabase{
    param(
        $sourceSqlServer,
        $sourceDatabaseName,
        $sourceSaCredential,
        $destinationSqlServer,
        $destinationDatabaseName,
        $destinationSaCredential
    )
    function convertLocalToUnc($localPath,$computername){    
        $uncPath=.{$x=$localPath -replace "^([a-zA-Z])\:","\\$computername\`$1`$";
                    if($x -match '\\$'){return $x.Substring(0,$x.length-1)}else{return $x}
                    }
        return $uncPath
    }
 
    function mountDriveAsUser($username,$password,$driveLetter,$uncPath){
        if(get-psdrive $driveLetter -ea SilentlyContinue){
            #Remove-PSDrive $firstAvailableDriveLetter -ea SilentlyContinue #This does not affect drives being mounted by 'net use' command
            net use /delete ($driveLetter+':') 2>&1>null
            }    
        try{
            # This command cannot persist when out of scope of function; hence, net use is required
            # New-PSDrive –Name $mountLetter –PSProvider FileSystem –Root $uncPath –Persist -Credential $mountAsCred|out-null
            net use "$driveLetter`:" "$uncPath" /user:$username $password /persistent:Yes 2>&1>null
            if(test-path "$driveLetter`:\"){
                write-host "$driveLetter`: has successfully mounted.";
                #return $true;
                }
            else{
                write-host "Unable to mount drive $driveLetter";
                #return $false
                }
            }
        catch{
            write-warning "$error"
            #return $false
            }
        }
    $triggerSqlBackup={
        param ($databaseName)
        try{
            import-module sqlps
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $database=Get-SqlDatabase -ServerInstance $env:computername|?{$_.Name -eq $databaseName}
            Backup-SqlDatabase -DatabaseObject $database -CompressionOption On -CopyOnly
            return $true
        }catch{
            write-warning $_
            return $false
        }
    }

    $restoreDbChangeName={
        param(
            $dbBackupFile,
            $newDatabaseName,
            $dbData='mscrm',
            $dbLog='mscrm_log'
            )
        import-module sqlps	
        function confirmation($content,$testValue="I confirm",$maxAttempts=3){
            $confirmed=$false;
            $attempts=0;        
            $content|write-host
            write-host "Please review this content for accuracy.`r`n"
            while ($attempts -le $maxAttempts){
                if($attempts++ -ge $maxAttempts){
                    write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
                    break;
                    }
                $userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
                if ($userInput.ToLower() -eq $testValue.ToLower()){
                    $confirmed=$true;
                    write-host "Confirmed!`r`n";
                    break;                
                }elseif($userInput -like 'cancel'){
                    write-host 'Cancel command received.'
                    $confirmed=$false
                    break
                }else{
                    Clear-Host;
                    write-host $($content|out-string).Trim()
                    write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
                    }
                }
            return $confirmed;
        }
        
        [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
        $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
        $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
        $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
        $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
        $sqlDefaults=@{
            'dataDirectory'=$defaultDataDirectory
            'logDirectory'=$defaultLogDirectory
            'backupDirectory'=$defaultBackupDirectory
            }
        #$originalDb=invoke-sqlcmd -query "restore filelistonly from disk = '$dbBackupFile'"
        #$originalDbDataPath=$originalDb[0].PhysicalName
        #$originalDbLogPath=$originalDb[1].PhysicalName
        $sqlRestoreChangeDbName="
            USE [master]
            GO
            ALTER DATABASE [$newDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            GO
            RESTORE DATABASE [$newDatabaseName] FROM DISK = N'$dbBackupFile' WITH FILE = 1,
                MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
                MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
                NOUNLOAD,  REPLACE,  STATS = 5;
            GO
            "	
        $confirmed=confirmation -content $sqlRestoreChangeDbName
        if($confirmed){
            try{
                invoke-sqlcmd -Query $sqlRestoreChangeDbName -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
                $removeBackupFile=confirmation -content "Delete backup file: $dbBackupFile?"
                if($removeBackupFile){
                    $null=remove-item -path $dbBackupFile -force -ea SilentlyContinue
                }else{
                    write-host "Backup file NOT removed: $dbBackupFile"
                }
                return $true
            }catch{
                write-warning $_
                return $false
            }
        }else{
            write-host 'No confirmations received. No changes have been made.'
            return $false
        }
    }
    function invokeGetDefaultSqlPaths($sqlServer,$saCredential){
        $defaultValues=invoke-command -ComputerName $sqlServer -Credential $saCredential -ScriptBlock{      
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername 
            $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
            $defaultDataDirectory=$sqlConnection.Settings.DefaultFile
            $defaultLogDirectory=$sqlConnection.Settings.DefaultLog
            return @{
                'dataDirectory'=$defaultDataDirectory
                'logDirectory'=$defaultLogDirectory
                'backupDirectory'=$defaultBackupDirectory
                }
        }
        if($defaultValues){return $defaultValues}
        else{return $false}    
    }

    $successfulBackup=invoke-command -computername $sourceSqlServer `
        -credential $sourceSaCredential `
        -scriptBlock $triggerSqlBackup `
        -Args $sourceDatabaseName

    if($successfulBackup){
        if ($sourceSqlServer -ne $destinationSqlServer){
            # Extract username and passwords to be used as mounts
            $sourceUsername=$sourceSaCredential.Username
            $sourcePassword=$sourceSaCredential.GetNetworkCredential().password
            $destinationUsername=$destinationSaCredential.Username
            $destinationPassword=$destinationSaCredential.GetNetworkCredential().password

            # Scan for next available drive letter, excluding D "CD Rom" and H "Home"
            $unavailableDriveLetters=(Get-Volume).DriveLetter|sort
            $availableDriveLetters=.{(65..90|%{[char]$_})|?{$_ -notin $unavailableDriveLetters}}
            [char]$firstAvailableDriveLetter=$availableDriveLetters[0]
            [char]$secondAvailableDriveLetter=$availableDriveLetters[1]
        
            # Mount Source UNC path
            $sourceSqlPaths=invokeGetDefaultSqlPaths $sourceSqlServer $sourceSaCredential
            $sourceUncPath=convertLocalToUnc $sourceSqlPaths.BackupDirectory $sourceSqlServer
            mountDriveAsUser $sourceUsername $sourcePassword $firstAvailableDriveLetter $sourceUncPath
            $sourceFolder="$firstAvailableDriveLetter`:\"
        
            # Mount Destination UNC path
            $destinationSqlPaths=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCredential
            $destinationUncPath=convertLocalToUnc $destinationSqlPaths.BackupDirectory $destinationSqlServer
            mountDriveAsUser $destinationUsername $destinationPassword $secondAvailableDriveLetter $destinationUncPath
            $destinationFolder="$secondAvailableDriveLetter`:\" 
            
            $sourceBackupFile="$sourceFolder$sourceDatabaseName.bak"
            $sanityPassed=(test-path $sourceFolder) -and (test-path $destinationFolder) -and (test-path $sourceBackupFile)
            if ($sanityPassed){
                write-host "Now copying $sourceBackupFile to $destinationFolder. Please wait..."
                $fileSize=(get-item $sourceBackupFile).Length/1GB
                $transferTime=(measure-command {robocopy "$sourceFolder" "$destinationFolder" "$sourceDatabaseName.bak"}).TotalHours
                $transferSpeed=[math]::round($fileSize/$transferTime,2)
                write-host "$([math]::round($fileSize,2)) GB was copied in $([math]::round($transferTime,2)) hours => Speed: $transferSpeed GB/Hour"
            }else{
                write-warning "Unable to reach one of these paths:`r`n$sourceFolder`r`n$destinationFolder`r`n$sourceBackupFile"
            }
            # Cleanup Routine
            write-host "Deleting backup file at $sourceBackupFile..."
            $null=remove-item -path $sourceBackupFile -force -ea SilentlyContinue
            write-host "Removing drive mounts..."
            net use /delete $($firstAvailableDriveLetter+':') 2>&1>null
            net use /delete $($secondAvailableDriveLetter+':') 2>&1>null         
        }else{
            $destinationSqlPaths=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCredential            
        }
        $destinationBackupFile="$($destinationSqlPaths.BackupDirectory)\$sourceDatabaseName.bak"
        $result=invoke-command -computername $destinationSqlServer `
            -credential $destinationSaCredential `
            -scriptBlock $restoreDbChangeName `
            -Args $destinationBackupFile,$destinationDatabaseName
        return $result
    }else{
        write-warning "SQL Backup has failed at the source server $sourceSqlServer"
        return $false
    }
}

copyDatabase $sourceSqlServer `
            $sourceDatabaseName `
            $sourceSaCredential `
            $destinationSqlServer `
            $destinationDatabaseName `
            $destinationSaCredential

Leave a Reply

Your email address will not be published. Required fields are marked *