SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failing backup can't create backup


Failing backup can't create backup

Author
Message
adrian.saunders
adrian.saunders
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 585
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86212 Visits: 45229
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, MVP, M.Sc (Comp Sci)
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


Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2279 Visits: 2032
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"
adrian.saunders
adrian.saunders
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 585
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
Vivien Xing
Vivien Xing
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1846 Visits: 2204
Do you have any space in the database name? I noticed that a trailing space in the backup file from your post.
adrian.saunders
adrian.saunders
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 585
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
adrian.saunders
adrian.saunders
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 585
Yes that did indeed solve the problem.
Thank you for your help
agibb
agibb
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search