maintenance backup failing
-
Hi everybody
I have a problem. I am creating a new DAtabase maintenance Plans , what i want to do is BACKUP of the databases. If I chose to write the backup in a local drive it works fine but if I change the directory to write to a share folder (to another server) is not working.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 21BD92B7-6AAF-4411-926B-3FE019D31168 -To "carolinag" -Rpt "H:\backup master in Itfectp074.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\Itfectp07\sqlbk$\DOCS-SQL-04\PRDIDB\sqlmaint\weekly" -DelBkUps 1DAYS -BkExt "BAK"'
The error I am getting is the following
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
Can anybody help me? please?
Carolina
-
This sounds like the SQL Agent Login doesn't have permission to write to the remote server. Check to make sure that SQL Agent is set up with a login that has permission on the remote server.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Thank you for your prompt reponse but the agent login has permissions. Actually I have another job that runs O.K writing to the other server.
This is the one that works great! It uses a sql command
sql -S DOCS-SQL-04\PRDIDB -E -i \\sydpdba2\sqlmaint\backup_databases_diff.sql -o \\ITFECTP07\SQLBK$\DOCS-SQL-04\PRDIDB\sqlmaint\backup_databases_diff_run.sql -n -w 200
If you see it writes an output file to the ITFECTP07 server, but is not working with the command EXECUTE (below)
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3B0B9C5C-DA5F-4CFD-B308-05E9A056F8C0 -To "carolinag" -Rpt "H:\Program Files\Microsoft SQL Server\MSSQL$PRDIDB\LOG\DB Maintenance Plan14.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\Itfectp07\sqlbk$\DOCS-SQL-04\PRDIDB\sqlmaint\weekly" -DelBkUps 1DAYS -BkExt "BAK"'
I have tried a solution from microsoft page :
Two changes are required to each job step that executes the SQLMaint utility by using the xp_sqlmaint extended stored procedure.
- Modify the job step "Type" from "Transact-SQL Script (TSQL)" to "Operating System Command (CmdExec)".
- Change the "Command" associated with the job step from an extended stored procedure call to the direct execution of the SQLMaint executable as described above.
- Replace the string "EXECUTE master.dbo.xp_sqlmaint" with "SQLMaint" (without the quotes).
- Remove the single quotes which encapsulate the parameter list. You must delete the opening single quote, N', and the closing single quote, ', at the end of the parameter list.
Two changes are required to each job step that executes the SQLMaint utility by using the xp_sqlmaint extended stored procedure.
- Modify the job step "Type" from "Transact-SQL Script (TSQL)" to "Operating System Command (CmdExec)".
- Change the "Command" associated with the job step from an extended stored procedure call to the direct execution of the SQLMaint executable as described above.
- Replace the string "EXECUTE master.dbo.xp_sqlmaint" with "SQLMaint" (without the quotes).
- Remove the single quotes which encapsulate the parameter list. You must delete the opening single quote, N', and the closing single quote, ', at the end of the parameter list.
But I still getting the same error...Any idea?
-
OK. Lets try removing the SQL Maint all together and creating a job manually to see if we can find out what is happening.
Try creating a backup device using the remote machine. Then create a simple job to backup the database to this device and run the job. Also test the SQL in QA to make sure it works there.
BACKUP DATABASE Foo TO RemoteDevice
If that run in QA under your credentitals but the job fails with a similar error then you know that the problem is a permission issue for the SQL Agent.
I never use the SQL Maintenance utility as I like to have a little more control over what is going on. I also find it easier to document what I'm doing by creating my own jobs
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
I had been told trhat backing up to a remote device or UNC was not supported by Microsoft. We have had varying luck with this. Sometimes it works, sometimes it doesn't. We have gone to backing up locally and then copying the backup file to our backup server. Good luck and if this is supported, please post a reply and let me know!!!
-
Open your maintenace plan, goto reporting tab, check "write report to text file in directory', run your job and come back the director to find out report which may have more information to tell what exactly happpens to your job failure.
-
analyise this in the sql profiler, it may give you some answers
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
- Hello Everybody!! I just want to thank all of you for your help but you are not going to believe what solved the problem. I delete all the folders in the share server and recreated them , making sure that the folders were created by the server and it worked!! It seems that the folder were created by someone else and even they had authorization for everybody to write it didn't allowed it . Finally it worked!!! Thank you so much!!
Carolina
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply