SolvedPrivate

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

Posted on 2013-10-29
3
29 Views
Last Modified: 2016-02-11
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
Comment
Question by:citicus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39609199
" 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
 

Author Comment

by:citicus
ID: 39609258
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
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 39609291
"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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 52
the way to learn Microsoft BI 13 71
2008 to 2016 jump (SQL Server) 1 33
Sql Query to return all values in Capital letters 2 17
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question