I'm using OLA's SQL JOBs to do my database maintenance. So far, I really like them; they're very handy, especially when apps create new databases each month (WILDCARD dbnames!).
I have to copy files across server instances on occasion; since we're deploying SQL2008r2 I've started using Powershell to do this.
I've got a powershell job step that works fine to get bak files from my old servers to new servers.
But now I have to copy a backup created by the Ola backup process... it includes $ in the path (as servername$instancename) and at the start of the backup file name.
It LOOKS like the $ is confusing Powershell...
Is there a control character I can use to allow the "$" into the path/filename, or is there another
way to do this?
#Example: (this is a powershell step in a sql job)
# get yesterdays date as YYYYMMDD
$dd = (get-date).AddDays(-1).ToString("yyyyMMdd")
# here's the original source/target location for the backup file
#$from = "\\srvrname\r$\SQL\MSSQL10_50.instname\MSSQL\Backup\srvrname$instname\DBNAME\FULL\srvrname$instname_DBNAME_FULL_" + $dd + "*.BAK"
#$to = "\\SA01SQL002\u$\SQL\MSSQL10_50.TEST\MSSQL\Backup\from$instname\"
#and here's the error on the copy-item line:
# 'An object at the specified path \\srvrname\r$\SQL\MSSQL10_50.instname\MSSQL\Backup\srvrname\DBNAME\FULL does not exist.'
# whoops... that should say \srvrname$instname\
# that blows up so tried wrapping $from and $to within a double quote... this didn't fly either.
$from = """" + ...TheOriginalString... + """"
$to = """" + ...TheOriginalString... + """"
# the error references this line
copy-item $from $to -Force -Recurse
Cursors are useful if you don't know SQL