Options
Powershell Error!
chanakyajupudi
Member Posts: 712
in Off-Topic
Hey!
So I am trying to get a .bat file created and run using powershell. Pretty much a single line.
.\Backup1.ps1 -serverName "localhost" -backupDirectory "D:\SQL Backups\" -daystoStore 7
The above command works directly when run from powershell.
I created a .bat file with the contents
powershell
\Backup1.ps1 -serverName "localhost" -backupDirectory "D:\SQL Backups\" -daystoStore 7
And when I run the .bat I get the following error
The string is missing the terminator:".
+ Category Info : ParseError: (:) [], Parent ContainsErrorRecordException
+ Fully QualifiedErrorID : TerminatorExpectedAtEndofString
I have tried Single quotes and stuff like that but dont seem to understand what the problem is.
Just in case. The contents of backup1.ps1 are
param(
$serverName,
$backupDirectory,
$daysToStoreBackups
)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
{
$dbName = $database.Name
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$backupFileName = $dbName + "_UAT_" + $timestamp + ".bak"
$backupFileNames = $dbName + "_UAT_" + $timestamp + ".log"
$targetPath = $backupDirectory + "\" + $backupFileName
$targetPaths = $backupDirectory + "\" + $backupFileNames
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)
Write-S3Object -BucketName sqlbackup -File $targetPath -Key $backupFileName -Region ap-southeast-2
Backup-SqlDatabase -ServerInstance localhost -Database $dbName -BackupAction Log -BackupFile $backupFileNames
Write-S3Object -BucketName sqlbackup -File $targetPaths -Key $backupFileNames -Region ap-southeast-2
"backed & uploaded $dbName ($serverName) to $targetPath"
}
Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"
So I am trying to get a .bat file created and run using powershell. Pretty much a single line.
.\Backup1.ps1 -serverName "localhost" -backupDirectory "D:\SQL Backups\" -daystoStore 7
The above command works directly when run from powershell.
I created a .bat file with the contents
powershell
\Backup1.ps1 -serverName "localhost" -backupDirectory "D:\SQL Backups\" -daystoStore 7And when I run the .bat I get the following error
The string is missing the terminator:".
+ Category Info : ParseError: (:) [], Parent ContainsErrorRecordException
+ Fully QualifiedErrorID : TerminatorExpectedAtEndofString
I have tried Single quotes and stuff like that but dont seem to understand what the problem is.
Just in case. The contents of backup1.ps1 are
param(
$serverName,
$backupDirectory,
$daysToStoreBackups
)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
{
$dbName = $database.Name
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$backupFileName = $dbName + "_UAT_" + $timestamp + ".bak"
$backupFileNames = $dbName + "_UAT_" + $timestamp + ".log"
$targetPath = $backupDirectory + "\" + $backupFileName
$targetPaths = $backupDirectory + "\" + $backupFileNames
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)
Write-S3Object -BucketName sqlbackup -File $targetPath -Key $backupFileName -Region ap-southeast-2
Backup-SqlDatabase -ServerInstance localhost -Database $dbName -BackupAction Log -BackupFile $backupFileNames
Write-S3Object -BucketName sqlbackup -File $targetPaths -Key $backupFileNames -Region ap-southeast-2
"backed & uploaded $dbName ($serverName) to $targetPath"
}
Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"
Work In Progress - RHCA [ ] Certified Cloud Security Professional [ ] GMON/GWAPT if Work Study is accepted [ ]
http://adarsh.amazonwebservices.ninja
http://adarsh.amazonwebservices.ninja
Comments
-
Optionsseigex
Member Posts: 105
.\Backup1.ps1 -serverName "localhost" -backupDirectory "D:\SQL Backups\" -daystoStore 7
Two issues I notice, first your parameter is daysToStoreBackups but you're using daysToStore
Second is you're escaping your quote in "D:\SQL Backups\" so to the powershell script it looks like
"D:SQL Backups
Which would give an incorrect path AND not include the second quote, which is what it looks like your error is.
try "D:\\SQL Backups\\"
I'm saying this off the top of my head as I don't script w/ powershell much, so I could be off base. -
Optionschanakyajupudi
Member Posts: 712
Hey! Thanks. I have just corrected the first parameter.
But I am not sure I got the second one.
So what should I be using ?Work In Progress - RHCA [ ] Certified Cloud Security Professional [ ] GMON/GWAPT if Work Study is accepted [ ]
http://adarsh.amazonwebservices.ninja -
Optionsseigex
Member Posts: 105
Here is your parameter list:
param(
$serverName,
$backupDirectory,
$daysToStoreBackups
)
And here's the command you're using:
.\Backup1.ps1 -serverName "localhost" -backupDirectory "D:\SQL Backups\" -daystoStore 7
Looks like you need to be using -daysToStoreBackups 7 not -daystoStore 7 -
Optionschanakyajupudi
Member Posts: 712
Hey. I got that one. TheSecond is you're escaping your quote in "D:\SQL Backups\" so to the powershell script it looks like
"D:SQL Backups
Which would give an incorrect path AND not include the second quote, which is what it looks like your error is.
try "D:\\SQL Backups\\"
was what I did not get. I tried the "D:\\SQL Backups\\"
The command works but I have other errors saying the path does not work.Work In Progress - RHCA [ ] Certified Cloud Security Professional [ ] GMON/GWAPT if Work Study is accepted [ ]
http://adarsh.amazonwebservices.ninja -
Optionsseigex
Member Posts: 105
I'm not positive about the escaping path thing, that's typically how it works in most languages, but PowerShell might be different.
-
Optionsseigex
Member Posts: 105
Try removing the quotes and just using the path
Edit: nevermind, you need the quotes for the space, but I do notice you're adding an extra trailing backslash even thought there's one in the code. -
Optionschanakyajupudi
Member Posts: 712
Thank you very much. I have just renamed the folder without a space in the name. Modified the DB properties to allow for the change. And it works!
I owe you one!Work In Progress - RHCA [ ] Certified Cloud Security Professional [ ] GMON/GWAPT if Work Study is accepted [ ]
http://adarsh.amazonwebservices.ninja -
Optionsseigex
Member Posts: 105
chanakyajupudi wrote: »Thank you very much. I have just renamed the folder without a space in the name. Modified the DB properties to allow for the change. And it works!
I owe you one!
Awesome, although me being the inquisitive fixer of things I am, I would be trying to figure out why the space is causing an issue, but that's me!
Good luck.. -
Optionschanakyajupudi
Member Posts: 712
I will look into that. But I have a couple of issues to sort before I go fixing something like that.Work In Progress - RHCA [ ] Certified Cloud Security Professional [ ] GMON/GWAPT if Work Study is accepted [ ]
http://adarsh.amazonwebservices.ninja -
Options
Qord
Member Posts: 632 ■■■■□□□□□□
Had you typed it out in the terminal like that it would have worked. But since you're passing it as an argument, the "" gets stripped off and powershell sees the path as being just\SQL. I *think* if you wrapped the whole thing in single quotes it would work. Like using '"D:\\SQL Backups\\"' instead of just "D:\\SQL Backups\\". I think that would force the bat file to pass the proper argument with quotes. If not that, then I'd say to switch them and use the single followed by the double.
