SQL Server Database Backup Error

Hi,

    I am taking backups for all databases using Maintenance plan on shared machine. While doing so it producing error like as below:


Executed as user:      Source: Back Up Database (Full) Execute SQL Task     Description: Executing the query "EXECUTE master.dbo.xp_create_subdir
N'\\SQLdatabase\backup" failed with the following error: "xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'".

SQL server Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End      Source: Back Up Database (Full) Execute SQL Task     Description: Executing the query "BACKUP DATABASE [master] TO  DISK  failed with the following error: "Cannot open backup device   Operating system error 5 Access is denied.

Note: SQLServerServiceAcct & AgentAcct has full controll or admin rights on the folders where backups gets stored.


thanks
SQLCustomerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
Can have 2 reasons:


1: Install service pack 2


2: is Sp2 is already installed :

Changed the SQL Server service account from Local System to  domain account.

SQL Server and SQL Server Agent services need  domain account to function properly.
0
SQLCustomerAuthor Commented:
SQLServerService Acct and Agent Acct are domain account itself and has full rights on folders.

 SP2 for Windows?
0
Haresh NikumbhSr. Tech leadCommented:
nope sp2 for SQL
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SQLCustomerAuthor Commented:
Do you think any other permission related issues?
0
SQLCustomerAuthor Commented:
Is any other solution for this problem?
0
Aaron TomoskySD-WAN SimplifiedCommented:
I don't do maintenance plans, but if you want to do a scheduled task .bat file, I've got those working great.

something like this backs up all databases on a server to the folder the .bat file is run in naming each .bak as the db name:
sqlcmd -S LOCALHOST\SQLEXPRESS -Q "EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') Backup database [?] to disk = N''%CD%\?.bak'' WITH INIT'"

Open in new window

0
Jim P.Commented:
Since this is a share \\SQLdatabase\backup does the Authenticated Users have full access to the share?

And has the SQL Service and Agent been restarted since granting permissions?
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
1. As jimpen asks, make certain SQL Server has been restarted since permissions to the share and remote NTFS filesystem were granted.

2. For the job within the SQL Agent, drill down thru, 'jobs' -> to the specific backup job, and pull up it's properties.  Make certain the job owner is appropriate, and not, for example, an account which may have lost permissions.

3. Troubleshooting step: Try opening cmd window as the SQL Agent Service Account (or connect with remote deskto with that account), and confirm that you can create a file in network share with those permissions.
0
ZberteocCommented:
is \\SQLdatabase\backup a folder on the actual server where you do the backup? If yes, use the local url(like D:\SQLdatabase\backup) and not the network one.
0
SQLCustomerAuthor Commented:
SQLdatabase  is server.

how can we  use the local url (like D:\SQLdatabase\backup)  ??
0
ZberteocCommented:
If your SQL server name you do the backup on is SQLdatabase then the

 \\SQLdatabase\backup

url points to a folder on that server but using the network notation. Instead of that url you should use the local version of the folder on the SQLdatabase server, which should be something like:

C:\backup or
D:\backup

or maybe at a deeper level. You should be able to find it.

I am suggesting to use that in your maintenance plan instead of  \\SQLdatabase\backup
0
SQLCustomerAuthor Commented:
If my plan use D:\backup instead of  \\SQLdatabase\backup then it would look that folder local server. I have to do it on remote server.

  Is any other way to point remote folder other than network notation? Pl let me know
whole path by example.

Thanks
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In the jobs, the backup path is always relative to the server. Just providing the local path (D:\backup) will then access the local path on the server.
0
SQLCustomerAuthor Commented:
I am creating/running job on one(local) server and writing/storing backups on remote server. So I have to give remote path.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you have to provide a remote path, you are forced to used UNC paths. Network drive mappings (to letters) are not available, as the mapping is not retained for services.

You still have to confirm that access to the share and path works from the executing server to the target server with the appropriate account (the one of the executing server).
0
SQLCustomerAuthor Commented:
We do have full control permission on those folder, but still not able to write on those folders.


        Strange thing is : target server's event viewer not catching that errors.
0
ZberteocCommented:
Create another backup plan from scratch and see if you will get the same behavior. Use one small database for test.
0
David ToddSenior DBACommented:
Hi,

As an aside, do check out Ola Hallengren's free script at
http://ola.hallengren.com/

I've recently implemented local backups for a client, and then another step to copy to the backup server.

Regards
  David
0
SQLCustomerAuthor Commented:
Hi,

    I found one article on microsoft website : http://support.microsoft.com/kb/207187.
That has solved my problem.

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I don't get that. You confirmed you are able to write to the share using the MSSQL service account, so share and file system permissions have been set. But the article tells you to check exactly those, as suggested several times in this thread?!
0
SQLCustomerAuthor Commented:
SQLServer service has log on has property. I assign sql service logon as to one of the domain account. And give full rights to that domain acct on target folder. Its working now.

        In past, I was keep focusing only on account which was shown in job error history:
say for example: domain\sqlservice acct.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Ah, that is the account the job is running under - for connection to the SQL Server. That is in no way related to the account of the SQL Server service.
0
SQLCustomerAuthor Commented:
article help me to solve problem..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
OS Security

From novice to tech pro — start learning today.