0

I recently set up a series of maintenance plans to backup all online databases. A full backup runs once a week, a differential runs once a day, and a transactional runs every ten minutes. We have two SQL Server instances on two machines. One works perfectly, backing up to another server on its network. The other, a Google cloud compute instance, is trying to back up to another Google compute instance, and it can't.

The SQL server in question, let's call it SQL1, is trying to back up to a Google server we'll call storage1. Storage1 is NOT a Google Storage bucket, just a full server with space to store the backups. Thus, standard Windows sharing should apply. I've made the SQLBackups folder on Storage1's hard drive fully shared to everyone on the network, so accessing it shouldn't be a problem. In fact, the plan can't be created unless the folder can be reached, so I know that's working. Yet, when I run the plan, it fails within ten seconds. The plan log isn't helpful, but the job log is more interesting: it reports that the job failed because it "could not load package. Error: 0X80131534. Description: no description available." Google has next to nothing on this error number.

The odd thing is, I ran into a similar problem while setting the same plans up on the other server, but it went away in the course of working out how to do all this so I never found out what exactly the problem was. I'm very confused, and in the meantime, our database isn't able to back up. Anything anyone can suggest is appreciated. Again, this is just the full backup job, and it's set to ignore offline databases and back up all databases. Thanks.

migrated from stackoverflow.com Aug 29 '16 at 21:16

This question came from our site for professional and enthusiast programmers.

  • Does a FULL backup succeed to the Storage1 location if you manually run BACKUP DATABASE? – Tara Kizer Aug 29 '16 at 21:21
  • It does not. Oddly, the backup wizard can't locate the folder in question, while the maintenance plan wizard never complains about it. At least now I know that folder access is the problem, though I would have expected a more helpful error message in this instance. Does anyone know if the particular error number here means anything special? – AH16 Sep 1 '16 at 15:28
  • RDP to the server and login with the SQL Server service account. Try to UNC to the path. I bet you can't and that's the issue. Is the service account the default NT Service\yada yada yada? – Tara Kizer Sep 1 '16 at 15:39
  • I'm about to show my ignorance of aspects of database administration: how do I know the account used by SQL? If I connect over SSMS, I log in as 'sa', but when I view the history for maintenance plans, the user is normally NT Authority\Network Access. In case it matters, the permissions on the remote folder are set to 'everyone'. However, we have a weird setup at the moment as we transition from one network to another, so "everyone" may not be including "everyone". I'm just not sure how to check on that for sure. – AH16 Sep 1 '16 at 15:52
  • SQL Server runs as a service. Open services.msc, scroll down to SQL Server and see what "Log On As" is configured for. – Tara Kizer Sep 1 '16 at 17:52
1

Grant the access to the computer account since the service is running under NT Service\InstanceName: domain_name>\$

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window. By adding the dollar sign to the end, it's the computer account.

  • Just to be sure I have this right: say the domain is example.com, and the two servers are backupSource and backupDestination. On backupDestination, I add a user called example\backupSource$, correct?Or do I follow the dollar sign with the specific user (SQLAgent, etc) that SQL Server is running under? – AH16 Sep 1 '16 at 22:10
  • @AH16, it would be backupsource$. I am not sure about the domain part, whether or not to drop the .com. Try example\backupsource$ first. – Tara Kizer Sep 1 '16 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.