Full SQLBACKUP using PowerShell fails on $backup.SQLBackup()

Category: sql server dr

Question

Daniel Westerdale on Mon, 13 May 2013 19:59:18


Hi

I am logged on to one of the live app servers ( as this has SQL Management studio)

I am trying back up one of the active db server  instances

## Full + Log Backup of MS SQL Server databases/span>            
## with SMO.            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo');            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc');            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');            
# Requiered for SQL Server 2008 (SMO 10.0).            
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');            
$Server = "SP2010Instance";     # SQL Server Instance.            
$Dest = "y:\";    # shared drive.            
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server;  

Write-Output $srv.Name; 
         
# If missing set default backup directory.            
If ($Dest -eq "")            
{ $Dest = $server.Settings.BackupDirectory + "\" };            
Write-Output ("Started at: " + (Get-Date -format yyyy-MM-dd-HH:mm:ss));            
# Full-backup for every database            
foreach ($db in $srv.Databases)            
{   
    Write-Output $db.Name.          
    If($db.Name -ne "tempdb")  # Non need to backup TempDB            
    {            
        $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;            
        $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");            
        $backup.Action = "Database";            
        $backup.Database = $db.Name;            
        $backup.Devices.AddDevice($Dest + $db.Name + "_full_" + $timestamp + ".bak", "File");            
        $backup.BackupSetDescription = "Full backup of " + $db.Name + " " + $timestamp;            
        $backup.Incremental = 0;            
        # Starting full backup process.            
        $backup.SqlBackup($srv);     
        # For db with recovery mode <> simple: Log backup.            
        If ($db.RecoveryModel -ne 3)            
        {            
            $timestamp = Get-Date -format yyyy-MM-dd-HH-mm-ss;            
            $backup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup");            
            $backup.Action = "Log";            
            $backup.Database = $db.Name;            
            $backup.Devices.AddDevice($Dest + $db.Name + "_log_" + $timestamp + ".trn", "File");            
            $backup.BackupSetDescription = "Log backup of " + $db.Name + " " + $timestamp;            
            #Specify that the log must be truncated after the backup is complete.            
            $backup.LogTruncation = "Truncate";
            # Starting log backup process            
            $backup.SqlBackup($srv);            
        };            
    };            
};            
Write-Output ("Finished at: " + (Get-Date -format  yyyy-MM-dd-HH:mm:ss));

The script seems to list each (sharePoint ) database but then gives up!!!!!!
StateService_6e9ed1bd62e6471abe38f92fe145e98e
.
Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server '
SP2010DB01M'. "
At E:\daniel\backup_alldbs.ps1:32 char:26
+         $backup.SqlBackup <<<< ($srv);
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

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


Replies

Edwin M Sarmiento on Mon, 13 May 2013 21:15:29


Does it fail on all of your databases or just this one database? It may have something to do with the name of the database, having special characters in it. The SqlBackup() method of the Backup class takes only one parameter - the SQL Server instance name.

As a simple lest, try your PowerShell script on just one database. Remove the ForEach() loop and manually assign a database name to your Database object, like so

$db = $srv.Databases["SharePointAdminContent"]

Proceed with the rest of the script to test whether or not it fails on just that one database or on all of the databases on your instance

Daniel Westerdale on Tue, 14 May 2013 07:13:14


Hi 

Thanks for prompt response. I had to retire a bit early last night for a early start this morning ;-(

It does seem to fail on all  databases - I didn't bother boring you with each of the same error as generated by the foreach loop. I will test a single db as suggested and report back.

Daniel

Duane Lawrence on Tue, 14 May 2013 14:43:56


What version of Powershell and OS?  

Daniel Westerdale on Tue, 14 May 2013 20:31:28


Fanny/Duane

I spent a bit of time this morning on this.  

1) the PS is run from one of the app servers on the farm and not the Db server . For some unexplained reason whoever set up the farm did not install SQLServer Management tools on the db server.

2)  I brought in one of the BI/SQL consultants to look at this and he concluded my script was sound and identical to the ones he runs. Also, he strongly suspected I there was a permissions issue with the share folder ... even though we can all write to it.  As time was against us I discover there is a couple of maintenance plans in place which already had a full backup subtask set up..  so in the end I went for that - Well at least the subtask which I could run from SQLAgent ... none of us could run any of the plans manually though they do seem to be executed as scheduled .

3) I did put a break in the script tho didn't do as you suggested with the single name... something tells me the names are ok...but I could give your suggestion a try 

Win2008 R2 sp1

PS Z:\> $PSVersionTable

Name                           Value
----                           -----
CLRVersion                     2.0.50727.5466
BuildVersion                   6.1.7601.17514
PSVersion                      2.0
WSManStackVersion              2.0
PSCompatibleVersions           {1.0, 2.0}
SerializationVersion           1.1.0.1
PSRemotingProtocolVersion      2.1


PS Z:\>

Duane Lawrence on Wed, 22 May 2013 20:47:23


I am running Windows Server 2012 and under that PS ver 2 requires certs to run a script.  I have not had time to get any power shell scripts to run since we upgraded to Windows Server 2012 and they all worked under Windows Server 2008 R2.  I do know that I can get free certs by deploying a cert server to the domain, but again my domain admin does not have time to do this.  

DH2007 on Fri, 06 Sep 2013 08:07:33


This can happen if you are specifying a custom backup location and omit a trailing slash from the backup location path e.g. 

sqlbackup.ps1 MYHOST\MYDB C:\MyBackups

will result in the error that you see.

The script actually needs to be suppled with a backup path that includes the trailing slash e.g.

sqlbackup.ps1 MYHOST\MYDB C:\MyBackups\

This behaviour can be corrected by adding the additional line or two of code shown below under the existing check for the destination: 

# If destination was not supplied as arg set to default backup directory
If ($dest -eq "")
{
    $dest = $inst.Settings.BackupDirectory + "\"
}
# Supplying a backup location without the trailing slash breaks things
elseif ($dest -notlike "*\")
{
    $dest = $dest + "\"
}
Hope that helps




György Balássy on Thu, 20 Mar 2014 06:01:47


It can be a timeout issue if the backup would run more than 10 minutes:
http://gyorgybalassy.wordpress.com/2014/03/20/exception-calling-sqlbackup-with-1-arguments/

Hope this helps,

György Balássy