Error running SQL 2008 SMO SQLBackup

The actual error I was getting is this.

Exception calling “SqlBackup” with “1” argument(s):

I am going to explain what I was doing, the cause of the error and the fix. The purpose of this little exercise is to inform and hopefully reduce the stress level of others.

A little back ground.

I have been working with powershell creating scripts to replace our maintenance plans. So, naturally backups were on the list. After creating the scripts at home (yeah, I’m like that, work is never done. But can you call this work?), testing and confirming they worked. I took them to work to start replacing different tasks in the Maintenance Plans with Jobs being run by SQL Agent.

Since we backup system, user, and user logs (if Recovery Model set to “Full”) I created three seperate Jobs and set a scheduled for the jobs to run. I then moved to the next task to be replace in the Maintenance plans; removing old backup files.

I finished writing the script and during testing needed to add some database files (bak) and log (trn) into the directory to confirm testing results. I ran the backup job no problem. When I ran the log backup, *BAM* the job failed. Reading through the error message in the Job History as usual was very informative, not! So I decided to take a took at the script in powershell ISE.

Again, I ran the backups with no error, but every time I ran the log backup I got this error.

Exception calling “SqlBackup” with “1” argument(s): On server “MyServerName”

What the heck was going on here? It ran fine on my machine at home. (Never heard that one before, hug)

I searched on Google, but did not find much help there. There were a few things but again as usual they were similar but different. So I kept digging. I then turned to my next source of good information Twitter. For those of you that don’t use twitter (What are you waiting for?) there are several help tags that all the smart (and cool) people monitor. Like #sqlhelp for SQL Server related problems, issues or questions and #poshhelp for the same related to Powershell. Unfortunitaley there wasn’t much help there either.

Then I got a direct message (DM), again for those of you not on twitter, that is a private message. It was from Aaron Nelson, a.k.a. SQLvarient ( blog | twitter ). He asked me if I wanted some help with the error. Know Aaron, (through twitter, I haven’t actually met him. Yet) I promptly took him up on the offer and sent him my scripts.

Long story short he said they worked on his testing environment. One thing he did ask. Was I loading in the plugins for SQL server (more on those later). He said he had seen weird behavior in the past and loading them into the profile fixed his issue, besides it couldn’t hurt. So, loaded the plugins and no luck, same error. Aaron then said something that sparked the light bulb, “It’s probably some kind of security “feature” that causes the problem.”

I search Google again, this time for SQLBackup (this is using SMO) and securiy related issues. I found a post from someone saying they had the same error and after setting the correct permissions on the database the error went away. SQL BOL (books online) stated the following:

To run the SqlBackup method, users must have BACKUP DATABASE or BACKUP LOG permissions on the database, or be a member of the db_owner and db_backupoperator fixed database role and the sysadmin fixed server role.

Am I reading that correctly? To create Backups I need permissions in addition to my sysadmin permissions? Hmm. Confusing to say the least. I sent an email to our Microsoft Rep and then we talked on the phone. He agreed, the wording is confusing and suggested doing some additonal testing related to permissions to confirm.  We are working with him and our security group to update post install scripts that remove Guest CONNECT permissions to databases. I asked if he thought that may be causing the issue, he said, “That is a strong possibility”.

So, armed with this knowledge, I went to the server I was originally testing my scripts on and granted connect permissions for the user I was logged in as to msdb.

Then I tested the log backup script and it ran without any errors. YeeHaa! (that’s a Texas thing)

So there you have it. If you are getting the error above, you may want to check permissions for the user you are logged in as and see if you have guest connect permissions on msdb. Removing these permissions can cause all kids of issues.

If you are removing GUEST CONNECT permissions based on old/bad information in BOL like we are I suggest you read more about why it is a bad idea from Buck Woody’s (blog | twitter ) post. Here are a few additional links to information you can check here and here about this as well.

I hope this helps someone else if/when they run into this issue.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s