SQL Server backup script (PowerShell) works on 2016 server but not 2012

Question

I have the following script:

$serverName = "."
$backupDirectory = "\\backup\server1"
$daysToStoreDailyBackups = 7
$daysToStoreWeeklyBackups = 28
$monthsToStoreMonthlyBackups = 3

[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

$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$serverName
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "sa"
$mySrvConn.Password = "myPass"

$server = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)

$dbs = $server.Databases
$startDate = (Get-Date)
"$startDate"

Get-ChildItem "$backupDirectory\*_daily.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreDailyBackups)} |% {Remove-Item $_ -force }
"removed all previous daily backups older than $daysToStoreDailyBackups days"

foreach ($database in $dbs | where {$_.IsSystemObject -eq $False})
{
    $dbName = $database.Name      

    if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
    {
        $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
        $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_daily.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.SqlBackup($server) 
        "backed up $dbName ($serverName) to $targetPath"    
    }
    else
    {
        "$dbName backup skipped"
    }
}

if([Int] (Get-Date).DayOfWeek -eq 0)
{
    Get-ChildItem "$backupDirectory\*_weekly.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreWeeklyBackups)} |% {Remove-Item $_ -force }
    "removed all previous daily backups older than $daysToStoreWeeklyBackups days"

    foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
    {
        $dbName = $database.Name  

        if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
        {    
            $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
            $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_weekly.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.SqlBackup($server) 
            "backed up $dbName ($serverName) to $targetPath"
        } 
        else
        {
            "$dbName backup skipped"
        }                   
    }
}

if([Int] (Get-Date).Day -eq 1)
{
    Get-ChildItem "$backupDirectory\*_monthly.bak" |? { $_.lastwritetime -le (Get-Date).AddMonths(-$monthsToStoreMonthlyBackups)} |% {Remove-Item $_ -force }
    "removed all previous monthly backups older than $monthsToStoreMonthlyBackups days"

    foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
    {
        $dbName = $database.Name  

        if ($dbName -ne "ReportServer" -and $dbName -ne "ReportServerTempDB")
        {
            $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
            $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_monthly.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.SqlBackup($server) 
            "backed up $dbName ($serverName) to $targetPath" 
        } 
        else
        {
            "$dbName backup skipped"
        }               
    }
}

This works perfectly on one of my servers (running server 2016), the other (running server 2012) it does not, it gives me the following error:

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'Server1'. "
At C:\Users\Administrator\Documents\Scripts\sqlFullBackup.ps1:43 char:9
+         $smoBackup.SqlBackup($server)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

Any ideas how to get around this?

thanks in advance

--- EDIT -----------------------------

When running

$error[0] | fl -force

I get the following:

System.Data.SqlClient.SqlException: Cannot open backup device '\\ServerAddress\Folder\file_2017-06-17-110451_daily.bak'. Operating system error 5(Access is denied.).

M.Hassan · Accepted Answer · 2017-06-17 06:27:50Z

1

I tested your script on sql server 2012 and success backup to local folder.

Your script is working fine.

Try (for test) to backup to local folder on the server.

Be sure:

You installed Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012 (with SMO). For details, read my answer: What do I need to execute a SQL Server PowerShell module
Your server has access permission to the shared folder "\\backup\server1". In server 2012, Set MSSQLSERVER account a permission R/W on the shared folder "\\backup\server1"

Edit:

From your comment, the script is working fine by using local folder. So, the problem is the setting of the shared folder.

In Workgroup Environment

If you sql server 2012 is working in WORKGROUP environment:

Create the same window user account with the same password (e.g 'sql2012') on both sql server 2012 and the backup server (using Computer manager).
Set this account (sql2012/password) as the account running sql service (using the Sql Server Configuration Manager tool).
in backup server set the permission in the shared folder Full R/W to the account sql2012

In domain environment

Use a domain account as the account running sql service (using the Sql Server Configuration Manager tool)
in backup server set the permission in the shared folder Full R/W to the that domain account.

edited Jun 17, 2017 at 6:27

answered Jun 16, 2017 at 15:47

M.Hassan

11.2k5 gold badges74 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Owain Esau Over a year ago

Yeah it works to a local folder, i can't add MSSQLSERVER as a permission as the backup server doesn't have Sql Server installed

M.Hassan Over a year ago

What the environment the server 2012 is working: Workgroup or domain? are sql server service account is built-in account or user account(from Sql Server Configuration Manager)?

Owain Esau Over a year ago

Ahh thank you so much that worked. It is on a workgroup, changing the the account in SQLconfiguration manager did the trick.

Collectives™ on Stack Overflow

SQL Server backup script (PowerShell) works on 2016 server but not 2012

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related