Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 37
  • Last Modified:

Weekly full backup of multiple SQL databases fails to backup a single database

Hello Experts

We have a single SQL server with a single instance of SQL on it. This server has > 50 databases on it. Each Tuesday morning we run a backup maintenance task that takes a full backup of each database and every other day of the week another job runs which takes a differential backup.

The problem I have is that the full backup job backs up all the databases on the server with one exception - the xyz database. This database is good and in use. Every Tuesday morning we get an error similar to, or the same as, this:

Date: '29/10/2013'
Time: '01:14:34'
Event: '3041'
Type: 'ERROR'
Category: 'Backup'
User: 'NT AUTHORITY\SYSTEM'
Source: 'MSSQL$WEB_SC'
Computer: 'servername'
Message: 'BACKUP failed to complete the command BACKUP DATABASE xyz . Check the backup application log for detailed messages.'
Data: 'server \ W E B _ S C xyz'

The backup application log has nothing in it at all and the only other error that we get is this:

Date: '29/10/2013'
Time: '01:14:28'
Event: '17063'
Type: 'ERROR'
Category: 'Server'
User: 'NT AUTHORITY\SYSTEM'
Source: 'MSSQL$WEB_SC'
Computer: 'servername'
Message: 'Error: 50000 Severity: 16 State: 1 Full backup failed for database: '
Data: 'server \ W E B _ S C m a s t e r '

The differential backups run fine. If I run a manual full backup of this database it works OK.

We are running SQL Server 2008 R2 with SP1. All help most appreciated.
0
citicus
Asked:
citicus
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
" If I run a manual full backup of this database it works OK."

How is the backup running other than manual? My guess is - Scheduled Maintenance Plan right? and if it's like that what account/login is that one starting at? Can you check SQL Server Agent Service and see if that account has sufficient rights?

What account/login are you using when " If I run a manual full backup of this database it works OK."?
0
 
citicusAuthor Commented:
Your guess is correct re the scheduled maintenance plan.

When I run the manual backup I am logged in as myself - a local/domain/SQL admin.

The job runs as NTAUTHORITY\SYSTEM - would you mind telling me how I can check if the SQL Server Agent Service has the rights it needs? I think it will have as the database that is failing to be backed up is a copy of one that is being backed up OK.
0
 
lcohanDatabase AnalystCommented:
"I think it will have as the database that is failing to be backed up is a copy of one that is being backed up OK. "

In my opinion that assumption is not 100% always true.

Aside of that
"When I run the manual backup I am logged in as myself - a local/domain/SQL admin."
I presume the
"...database that is failing to be backed up is a copy of one that is being backed up OK"

so that backup was already completed - maybe one file is already open because the backup is going against it and needs to finish before the other one starts - in this case will confirm that the authority is not an issue and remember Maintenance Plans as SSIS as well and by default I believe Microsoft set it to run 4 tasks in parallel. In your case this means you need to serialize your backups/check backups if authority/rights is not an issue.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now