May 24, 2016 at 3:05 pm
Hello experts,
I found the promising script below at the following link:
https://sqlactions.com/2015/03/30/powershell-script-to-manipulate-sql-server-backup-files/[/url]
The purpose of the script is to allow copy of backup files from one SQL Server to another assuming the backups are made by the Ola Hallengren backup tool. The script text below is a little different from what is at the link above because I've been attempting to troubleshoot it for my system.
However, I ran into the 3 errors here and was wondering if anyone can help me decipher and fix them:
(1)
The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 45 in a PowerShell script. The corresponding line is ' New-Item -ItemType Dir -Path $PathToCopy -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Object does not support this operation.
'
(2)
A job step received an error at line 48 in a PowerShell script. The corresponding line is ' Copy-Item $FileToCopy $PathToCopy'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Copy-Item is not supported.
'
(3)
A job step received an error at line 52 in a PowerShell script. The corresponding line is ' $RenamedFile = ($DestinationFile.substring(0,$DestinationFile.length-20))+'.bak''. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "Substring" with "2" argument(s): "Length cannot be less than zero.
Parameter name: length"
'
<#################################################################################
Script Name: CopyLatestBackupandRename.ps1
Author : Prashant Kumar
Date : March 29th, 2015
Description: The script is useful for those using Ola Hallengren's backup solution.
This script takes SQL Server full backup parent folder as an input,
a remote UNC path as another input and copies the latest backup file
for each database, renames the backup file to the remote UNC path.
This Sample Code is provided for the purpose of illustration only and is not
intended to be used in a production environment. THIS SAMPLE CODE AND ANY
RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
##################################################################################>
#Specify Parent folder where Full backup files are originally being taken
$SourcePath = 'D:\SQLBackup\InstanceName'
#Specify UNC path ot network share where backup files has to be copied
$UNCpath = '\\RemoteServer\UNCBackup'
#Browse thru subfolders (identical to database names) inside $SourcePath
$SubDirs = dir $SourcePath -Recurse | Where-Object {$_.PSIsContainer} | ForEach-Object -Process {$_.FullName}
#Browse through each sub-drorectory inside parent folder
ForEach ($Dirs in $SubDirs)
{
#List recent file (only one) within sub-directories
$RecentFile = dir $Dirs | Where-Object {!$_.PSIsContainer} | Sort-Object {$_.LastWriteTime} -Descending | Select-Object -First 1
#Perform operation on each file (listed above) one-by-one
ForEach ($File in $RecentFile)
{
$FilePath = $File.DirectoryName
$FileName = $File.Name
$FileToCopy=$FilePath+'\'+$FileName
$PathToCopy=($filepath -replace [regex]::Escape($SourcePath), $UNCpath)+'\'
#Forecfully create the desired directory structure at destination if one doesn't exist
New-Item -ItemType Dir -Path $PathToCopy -Force
#Copy the backup file
Copy-Item $FileToCopy $PathToCopy
#Trim the date time from the copied file name, store in a variable
$DestinationFile = $PathToCopy+$FileName
$RenamedFile = ($DestinationFile.substring(0,$DestinationFile.length–20))+'.bak'
#Rename the copied file
Rename-Item $DestinationFile $RenamedFile
}
}
Thanks for any help!!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 24, 2016 at 3:23 pm
can you run $PSVersionTable?
i'm thinking the script is for Powershell 3.0, but it's running under 2.0 context?
the third error i would think can occur if the string $DestinationFile has less than 20 chars., c:\Data\test.csv for example
Name Value
---- -----
CLRVersion 2.0.50727.5485
BuildVersion 6.1.7601.17514
PSVersion 2.0
WSManStackVersion 2.0
PSCompatibleVersions {1.0, 2.0}
SerializationVersion 1.1.0.1
PSRemotingProtocolVersion 2.1
Lowell
May 25, 2016 at 8:37 am
Thanks so much for your response. I will check the PS version now.
I just realized the third error could be a side-effect - i.e., if the file name assignment failed earlier due to the other errors, it could be causing the third error as a knock-on effect.
Thanks again.
-webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 25, 2016 at 8:56 am
Looks like you're correct:
Name Value
---- -----
CLRVersion 2.0.50727.5485
BuildVersion 6.1.7601.17514
PSVersion 2.0
WSManStackVersion 2.0
PSCompatibleVersions {1.0, 2.0}
SerializationVersion 1.1.0.1
PSRemotingProtocolVersion 2.1
Do you happen to know of any way of rewriting this code for version 2.0? Or do I need to recommend to the admins to upgrade to PS 3.0?
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 25, 2016 at 9:07 am
In my case, i was bitten by the 2./3.0 when i wax using Export-Csv i think, which added -Appen parameter.
I ended up using a command line call to the 3.0 version, in a specific folder, instead.
Both versions were 8nstalled in my case, but in sql2008R2, the Sql Agent uses Powershell 2.0, so i had to end run around that.
Lowell
May 25, 2016 at 9:14 am
Lowell (5/25/2016)
In my case, i was bitten by the 2./3.0 when i wax using Export-Csv i think, which added -Appen parameter.I ended up using a command line call to the 3.0 version, in a specific folder, instead.
Both versions were 8nstalled in my case, but in sql2008R2, the Sql Agent uses Powershell 2.0, so i had to end run around that.
Thanks! I'll look into those options. I marked your original reply as the answer because that was my original question - the source(s) of the errors.
Many thanks, again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 3, 2016 at 2:05 pm
Hi webrunner - did you get this working?
I'm trying to do this exact same thing with the same starting script, running on SQL Agent on a SQL 2012 box.
I've already removed "cls" to get around the "cannot set screen color" errors. I've added "cd c:" at the top of the script to get out of "sqlserver:\" provider. But after all that I started getting "c:\ cannot be coped to c:\" errors, so I abandoned use of the "Powershell" step as being too wonky, and am instead trying to just call powershell.exe directly with a CmdExec.
I try the following:
c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe -file "?c:\Scripts\CopySQLBackups.ps1"
However I'm getting these errors:
Processing -File '?c:\Scripts\CopySQLBackups.ps1' failed: The given path's format is not supported. Specify a valid path for the -File parameter.
Notice the "?" mark in the file name on the error, not sure where that's coming from.
Thanks for any help from any quarters!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply