asked on

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

unknown_routine

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.

SQLCustomer

ASKER

SQLServerService Acct and Agent Acct are domain account itself and has full rights on folders.

SP2 for Windows?

Haresh Nikumbh

nope sp2 for SQL

SQLCustomer

ASKER

Do you think any other permission related issues?

SQLCustomer

ASKER

Is any other solution for this problem?

Aaron Tomosky

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

Jim P.

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?

Rich Weissler

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.

Zberteoc

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.

SQLCustomer

ASKER

SQLdatabase is server.

how can we use the local url (like D:\SQLdatabase\backup) ??

Zberteoc

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

SQLCustomer

ASKER

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

Qlemo

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.

SQLCustomer

ASKER

I am creating/running job on one(local) server and writing/storing backups on remote server. So I have to give remote path.

Qlemo

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).

SQLCustomer

ASKER

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.

Zberteoc

Create another backup plan from scratch and see if you will get the same behavior. Use one small database for test.

David Todd

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

ASKER CERTIFIED SOLUTION

SQLCustomer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

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?!

SQLCustomer

ASKER

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.

Qlemo

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.

SQLCustomer

ASKER

article help me to solve problem..