I have an automated third party backup places files on a network drive.

I've mapped this drive to a local folder on the DB server using the following PowerShell script

$pass ="xxxx"|ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PsCredential('MERIVALEHOSTED\SVC.APPSQL',$pass)

New-PSDrive -Name K -PSProvider "FileSystem" -Credential $cred -Root '\\\sqlbackup\ACCCluster01$AccWarehouseAG';

This maps my network drive to the K drive.

I then want to copy the entire folder structure of this (it's the output of Ola Hallengrens backup - DIFF/FULL/LOGS) to a local drive, but not before deleting the current contents of this folder.

This is the first step of a automated DB restore. I've been able to automate the restoration process successfully using the DbaTools powershell module, but the file copy isn't working.

I've tried both:

Copy-Item -path K:\*.* -destination M:\VoxtronRestore\;

as well as this:

function Copy-Directories 
    param (
        [parameter(Mandatory = $true)] [string] $source,
        [parameter(Mandatory = $true)] [string] $destination        

        Get-ChildItem -Path $source -Recurse -Force |
            Where-Object { $_.psIsContainer } |
            ForEach-Object { $_.FullName -replace [regex]::Escape($source), $destination } |
            ForEach-Object { $null = New-Item -ItemType Container -Path $_ }

        Get-ChildItem -Path $source -Recurse -Force |
            Where-Object { -not $_.psIsContainer } |
            Copy-Item -Force -Destination { $_.FullName -replace [regex]::Escape($source), $destination }

        Write-Host "$_"

$source = "K:\"
$dest = "M:\VoxtronRestore"

Copy-Directories $source $dest

The first of which fails to copy anything, and the second gives me an error about overwriting the source with itself.

I am wondering if anyone has any better suggestions or modifications to the above which will enable this to complete successfully.

  • So, quick question about the drive mapping. Does it happen under the same account that is running either the SQL Agent or the Credential for the Powershell step? Because the drive mapping is per user and if you ran the create mapping command as one user, it doesn't necessarily exist for any other user on that server. – Jonathan Fite May 21 '18 at 1:24
  • The drive mapping uses different credentials as it is outside of our domain. (hence why I had to store the credentials before I run the new psdrive. But, this is all run from the same script under the same user in powershell/sql agent. – Asher May 21 '18 at 2:00
  • 1
    Why not use Remove-Item to clean up the directory first (since you said "deleting the current contents of this folder"). Also, instead of using *.*, it should rather be `Copy-Item K:\ -Recurse M:\VoxtronRestore` – Arun Gopinath May 21 '18 at 3:37
  • I'll post that as an answer then :) – Arun Gopinath May 21 '18 at 7:03
  • 4
    I'm voting to close this question as off-topic because it's a system administration question, not a DBA question. – Colin 't Hart May 21 '18 at 8:18

*.* will only return the files directly under K:\ (You probably have a folder with the database/instance name here). Use Copy-Item K:\ -Recurse M:\VoxtronRestore instead.

Not the answer you're looking for? Browse other questions tagged or ask your own question.