Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Failing backup can't create backup Expand / Collapse
Author
Message
Posted Wednesday, November 11, 2009 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:16 AM
Points: 53, Visits: 545
I have a new SQL2005 database with several databases on it. I have a pretty de-facto backup (from using the backup wizard) that does a full backup of the user databases every night.
Yesterday I added a new database and it won't backup. The errors are
2009-11-11 15:09:32.70 Backup Error: 3041, Severity: 16, State: 1.
2009-11-11 15:09:32.70 Backup BACKUP failed to complete the command BACKUP DATABASE Database. Check the backup application log for detailed messages.
2009-11-11 15:11:18.57 spid99 Error: 18204, Severity: 16, State: 1.
2009-11-11 15:11:18.57 spid99 BackupDiskFile::CreateMedia: Backup device 'N:\Backups\Userdatabases\Database\Database _backup_200911111511.bak' failed to create. Operating system error 3(error not found).
2009-11-11 15:11:18.57 Backup Error: 3041, Severity: 16, State: 1.


I've migrated another database on today and that backups up fine to the same area.

The issue seems to be creating the files in the sub-directory \Userdatabases\Database\
The job runs fine if it doesn't create the sub-directory for each database.
Ideas?
thanks
Post #817247
Posted Wednesday, November 11, 2009 9:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 41,508, Visits: 34,424
Does the directory N:\Backups\Userdatabases\Database\ exist?

Do the SQL agent and SQL Server service accounts have permission to that directory?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #817257
Posted Wednesday, November 11, 2009 3:15 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 676, Visits: 1,333
Is the database that is failing the backup actually called database? If so, it could be because your database name is set to a reserved word in SQL. That is not a recommended practice. If you change the name of the database does it backup then?

Joie Andrew
"Since 1982"
Post #817509
Posted Thursday, November 12, 2009 1:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:16 AM
Points: 53, Visits: 545
Thansk for the replies

GilaMonster- initially the job creates the folder (create sub directories for each database), when it failed I tried it by creating the folder.
The SQL agent and server has the correct permissions as its created folders and backup files on the same drive and directories.
As this is a cluster the SQL has a dependancy on the drive.


UnixBomber- No its not the real name of the database. I just masked it


Post #817656
Posted Friday, November 13, 2009 9:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 2:49 PM
Points: 1,279, Visits: 2,203
Do you have any space in the database name? I noticed that a trailing space in the backup file from your post.
Post #818624
Posted Monday, November 16, 2009 3:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:16 AM
Points: 53, Visits: 545
Ah sharp eyes there. Yes thats it, I must have added a space when creating the database.
I'm going to follow the instructions at
http://blogs.msdn.com/sqlserverfaq/archive/2008/07/27/maintenance-plans-with-backup-database-task-fails-when-the-create-a-sub-directory-for-each-database-option-is-checked.aspx
to solve it
Post #819276
Posted Tuesday, November 17, 2009 2:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:16 AM
Points: 53, Visits: 545
Yes that did indeed solve the problem.
Thank you for your help
Post #819886
Posted Wednesday, August 29, 2012 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 4:13 PM
Points: 1, Visits: 0
I know this is an old post but thanks! I had been going round in circles all day and the trailing space was my issue. Saved my sanity!
Post #1351951
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse