Powershell Error!

chanakyajupudi

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"

seigex

.\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.

chanakyajupudi

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 ?

seigex

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

chanakyajupudi

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.

seigex

I'm not positive about the escaping path thing, that's typically how it works in most languages, but PowerShell might be different.

seigex

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.

chanakyajupudi

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!

seigex

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..

chanakyajupudi

I will look into that. But I have a couple of issues to sort before I go fixing something like that.

Qord

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.