Getting error while backing up SQL database using powershell

Question

I am trying to backup SQL database but strange thing is taking lpace, few databases i can backup with powershell but few i am getting error

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 
'Server1'. "
At C:\_Scripts\defaultbackup.ps1:40 char:1
+ $smoBackup.SqlBackup($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

$dbToBackup = "test"



#clear screen
cls

#load assemblies
#note need to load SqlServer.SmoExtended to use SMO backup in SQL Server 2008
#otherwise may get this error
#Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: make sure
#the assembly containing this type is loaded.

[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

#create a new server object
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$backupDirectory = $server.Settings.BackupDirectory
"Default Backup Directory: " + $backupDirectory
$db = $server.Databases[$dbToBackup]
 $dbName = $db.Name

 $timestamp = Get-Date -format yyyyMMddHHmmss
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")

#BackupActionType specifies the type of backup.
#Options are Database, Files, Log
#This belongs in Microsoft.SqlServer.SmoExtended assembly

$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"

$smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp +   
".bak",   
"File")

$smoBackup.SqlBackup($server)

#let's confirm, let's list list all backup files
$directory = Get-ChildItem $backupDirectory

$smoBackup.Devices.AddDevice($backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak", "File")

$smoBackup.SqlBackup($server)

let's confirm, let's list list all backup files

$directory = Get-ChildItem $backupDirectory

list only files that end in .bak, assuming this is your convention for all backup files

$backupFilesList = $directory | where {$_.extension -eq ".bak"}
$backupFilesList | Format-Table Name, LastWriteTime
$backupFilesList = $directory | where {$_.extension -eq ".bak"}
$backupFilesList | Format-Table Name, LastWriteTime

I read on internet that by using this in the script this problem can be sorted but i am not sure where exactly to put this line in above script set the ConnectionContext.StatementTimeout to 0 — user3770612
– user3770612, Commented Aug 13, 2014 at 13:27

Bruce · Accepted Answer · 2014-08-13 14:21:13Z

2

Replace

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"

with

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$conContext = $srv.ConnectionContext
$conContext.LoginSecure = $True
$conContext.ConnectTimeout = 0
$server = new-object Microsoft.SqlServer.Management.Smo.Server($conContext)

answered Aug 13, 2014 at 14:21

Bruce

1,6231 gold badge11 silver badges12 bronze badges

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

1 Comment

Nico Over a year ago

I got same problems, backup seems to failed sometimes, it worked yesterday, but not today, without any reason. I'll give this a try, and give feedback if successfull ! But I won't use "LoginSecure" switch, as I'm not sure it's required, the Timeout property might be enough.

Faiyaz · Accepted Answer · 2015-06-04 03:34:53Z

2

Since you can take backup of few and few are failing, seems like script is fine. Why not add the exception handling to trap exact error. You may want to modify your code like below

Try
{
    $smoBackup.SqlBackup($server
}
Catch
{
    Write-Output $_.Exception.InnerException
}

edited Jun 4, 2015 at 3:34

answered Jun 4, 2015 at 3:19

Faiyaz

1,41112 silver badges9 bronze badges

Comments

GrumpyGel · Accepted Answer · 2022-12-22 04:56:37Z

I know this is an old thread, but not fully answered. It did though help me resolve my problem (which is on a later version 14.0.1000.169 of SQL Server).

My database backup is done via a Powershell script on the task scheduler. It would randomly fail. Same when stepping through in the Powershell IDE it failed with the above error...

Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'ABC'."

When backed up manually through 'Tasks' in SMS (v18), it works every time.

In reading through the properties of the ServerConnection, the StatementTimeout property defaults to 600 seconds (10 minutes). My backup script, once the DB backup has completed, Zips the backup then FTPs the Zip to another server. Looking at the timestamp on the Zips, they were roughly 10 minutes from the backup initiating.

So I added the StatementTimeout line to my code as follows to double the timeout to 20 minutes:

$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance = $serverName
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = $userName
$mySrvConn.Password = $userPwd
$mySrvConn.StatementTimeout = 1200
$server = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)

This has resolved the problem. Some backups were completing within 10 minutes, others must have not quite completed.

Collectives™ on Stack Overflow

Getting error while backing up SQL database using powershell

let's confirm, let's list list all backup files

list only files that end in .bak, assuming this is your convention for all backup files

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

let's confirm, let's list list all backup files

list only files that end in .bak, assuming this is your convention for all backup files

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related