Failed Backup (SQLEXPRESS)

[deevus]

The first issue I had was creating the log file. Since I am using the express edition creating a file with a backslash (\) would throw an exception. I added a simple regex to overcome that problem and replace the backslash with an underscore:

Code: Select all

$escapeddbhost = $dbhost -replace "\\", "_"
$logfile = [System.IO.Path]::Combine($logdir, $escapeddbhost + "-" + $now.ToString("yyyy-MM-dd") + ".log")

Once that was fixed it threw this:

Code: Select all

Backup failed for Server 'DEEVUS-PC\SQLEXPRESS'.
At C:\backup\automssqlbackup.ps1:230 char:15
+     $bk.SqlBackup <<<< ($srv)
    + CategoryInfo          : NotSpecified: (:) [], FailedOperationException
    + FullyQualifiedErrorId : DotNetMethodException

I haven't had any experience debugging powershell before so maybe you could help me in that regard. Could it be that I don't have the "SMO libraries" that you list as a prerequisite? Seems unlikely as it should have thrown an exception a few lines higher where it creates the SMO library object.

[devio]

Hi deevus

I guess you use PS2, as the CategoryInfo and FullyQualifiedErrorId do not show up in case of error in my environments.
You can add the lines

Code: Select all

      Write-Host "exception message"
      Write-Host $error[0].Exception.ToString();


inside the trap{} handler of the BackupDB() function to display the full exception text (I will update and release this later).

Most likely the cause for the exception is that (due to the day of week) an incremental backup is performed, but there is no full backup yet for that database. If that is the case, manually perform a full backup first, and then run automssqlbackup.

Full backups are automatically created on Sundays (or whatever day you set $doweekly to)