Backing up SQL databases with powershell - seems to not do anything

The below script should be working however I have the following issues:
1. when running as the authorized domain admin user, it says "access denied"

2. When running using the the "run as administrator" option, it launches but does not seem to do anything, just seems to hang and not produce any output/backups or any errors.

If this would work, it would be awesome (It is on SQL 2005):

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

 
$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(SB-SRV-004\SPSQL)"
$conContext = $srv.ConnectionContext
$conContext.LoginSecure = $True
$conContext.ConnectTimeout = 0
$server = new-object Microsoft.SqlServer.Management.Smo.Server($conContext)
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
{
    $dbName = $database.Name
 
    $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
    $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"
 
    $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.CopyOnly = $TRUE
    $smoBackup.CompressionOption = “1”
    $smoBackup.SqlBackup($server)
 
    "backed up $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"
webdude2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

webdude2000Author Commented:
I suspect the problem is here where it connects to the SQL instance:
$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(SB-SRV-004\SPSQL)"

I have added the domain acc as a login in SQL although it may be better to look at the commands to have it authenticate using the sa account.

Before the above line, it used:
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName

But threw up an exception error then on line:
$smoBackup.SqlBackup($server)
DBAduck - Ben MillerPrincipal ConsultantCommented:
Here you go. This should help and make things simpler.

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 -TypeName Microsoft.SqlServer.Management.Smo.Server -Args $serverName
 $conContext = $server.ConnectionContext
 $conContext.StatementTimeout = 0
 
 $dbs = $server.Databases
 foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
 {
     $dbName = $database.Name
  
     $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
     $targetPath = "$backupDirectory\$dbName_$timestamp.bak"
  
     $smoBackup = New-Object -TypeName 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.CopyOnly = $TRUE
     $smoBackup.CompressionOption = “On”
     $smoBackup.SqlBackup($server)
  
     "backed up $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"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
webdude2000Author Commented:
Hi Ben
Thank you for the changes, the script runs now and completes but the process still fails on line 34:
$smoBackup.SqlBackup($server)

I run it using a bat file as follows:
powershell C:\SQLbackup\Tools\SQLbackupScript.ps1 -serverName "SB-SRV-004\SPSQL" -backupDirectory "\\SC-DC01-BNE\sysadmin\Configuration\Backups\SecurityCommander" -daysToStoreBackups 7 >> C:\SQLbackup\Logs\%date%.log


Error in the log produced is:
Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server '
SB-SRV-004\SPSQL'. "
At C:\SQLbackup\Tools\SQLbackupScript.ps1:34 char:26
+      $smoBackup.SqlBackup <<<< ($server)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 
backed up All8300History (SB-SRV-004\SPSQL) to \\SC-DC01-BNE\sysadmin\Conf
iguration\Backups\SC\2015-06-03-095025.bak
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

webdude2000Author Commented:
Windows event Viewer shows the following under Application:

BackupDiskFile::CreateMedia: Backup device '\\SC-DC01-BNE\sysadmin\Configuration\Backups\SC\2015-06-03-095025.bak' failed to create. Operating system error 5(failed to retrieve text for this error. Reason: 15105).


The destination folder is SecurityCommander, I abbreviated it to SC for these questions but I see I left it in the bat command I posted. My apologies, the errors are not due to mismatched destination  folders.

It backs up 3 databases.
DBAduck - Ben MillerPrincipal ConsultantCommented:
So everything is good?
webdude2000Author Commented:
No, get this error:

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server '
SB-SRV-004\SPSQL'. "
At C:\SQLbackup\Tools\SQLbackupScript.ps1:34 char:26
+      $smoBackup.SqlBackup <<<< ($server)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
DBAduck - Ben MillerPrincipal ConsultantCommented:
OK, what you need to ensure is that the Service Account for SQL Server is in the security of the share folder with FULL control.  The Service Account is the one that actually backs up the database and any folder that it writes to it needs FULL control.  The error message was Error 5 which is Access denied.  Check out the folder permissions.
DBAduck - Ben MillerPrincipal ConsultantCommented:
Haven't heard back. Did you resolve the permissions?  Error 5 is access denied, so the permissions on the folder should be the resolution.  Thanks for any word.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.