none
failed to create. Operating system error 5(failed to retrieve text for this error. Reason: 15105)

    Question

  • Hi,

    OS: Windows 2008 R

    Database: SQL Server 2008

    We have SSIS package installed on the SQL Server 2008, one of the task in SSIS package backs up database to the "E:\M1G3Datastore" drive.

    This pacakage was working fine for the last 4 months, this morning the task that backs up the database fails, the error message is:

    BackupDiskFile::CreateMedia: Backup device 'E:\M1G3Datastore\M1Test.bak' failed to create. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

    The following query work, this query backs up the database to C drive and it works:

    BACKUP

     

    DATABASE [M1Test] TO DISK = N'C:\M1G3Datastore\M1Test.bak'

    WITH

     

    NOFORMAT, INIT, NAME = N'M1Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    THE FOLLOWING QUERY DOES NOT WORK, PLEASE NOTE THE DRIVE IS CHANGED TO "E" IN THIS INSTANCE

     

     

    BACKUP DATABASE [M1Test] TO DISK = N'E:\M1G3Datastore\M1Test.bak'

    WITH

     

    NOFORMAT, INIT, NAME = N'M1Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

     Please help, and MANY thanks in advance

     


    sword
    • Moved by Todd McDermidMVP Tuesday, June 29, 2010 4:45 AM Backup Error (From:SQL Server Integration Services)
    Tuesday, June 29, 2010 12:43 AM

All replies

  • Although you first encountered the error in an SSIS package that does your backup, you've verified that the problem still exists when direct T-SQL is used to instruct the engine to perform a backup.  Given that, I'm going to move this thread to the DR forum where I think you'll get much more assistance.


    Todd McDermid's Blog Talk to me now on
    Tuesday, June 29, 2010 4:45 AM
  • Thanks Todd, please do,

    need to get this sorted asap, please,

    all our processing has come to stand still,

    Many thanks for your help :)


    sword
    Tuesday, June 29, 2010 4:51 AM
  • From a OS command prompt, type:

    net helpmsg 5

    You get "Access is denied.". So, it seems like the service account for SQL Server doesn't have permissions on where you try to produce the backup file.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 29, 2010 7:02 AM
    Moderator
  • Hi Tibor,

    Many thanks for the reply, can you please let me know how to grant premission to the sql 2008 "service account"

    I can backup to the C drive, using SSIS task and sql commands... but I cant backup to the E drive.

    your help is greatly appreciated.

    Thanks heaps.

     


    sword
    Tuesday, June 29, 2010 8:38 AM
  • Get the service account name for  SQL Server service. You can use "SQL Server Configuration Manager"   to gather Service account details

    1) Launch SQL Server Configuration Manager - you should see the short cut in Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

    2) Expand tree view "SQL Server Services". On right pane, youl will see the list of Services installed. Look at the SQL Server  service specific to that instance. Chekc the column "Log on As" - This will give you the SQL Server service account context

    3) Grant permissions for the above account to drive / folder that you are backing up files to . Please refer following articles on how to grant file / folder permissions in windows

    http://technet.microsoft.com/en-us/library/bb727008.aspx

    http://support.microsoft.com/kb/308421

    If you are still seeing this  issue, Let us know

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Server

     

    Tuesday, June 29, 2010 5:22 PM
    Moderator
  • Hello Sethu,

    Many thanks for the mail,

    Our SQL Server 2008 runs under "NETWORK SERVICE" account

    I right clicked the E drive and looked at the security tab, "NETWORK SERVICE" account was missing, so I have added this account with full access. But this did not help.

    I have checked the D drive security tab and there is no "NETWORK SERVICE" account on C drive. but our SSIS is able to backup in C drive, and not able to do so in the E drive.

    Please help. and thanks is advance.

     


    sword
    Tuesday, June 29, 2010 9:45 PM
  • Hello,

    Can you open a "New Query" Windows from SSMS and run the same BACKUP statement?. Are you seeing the same permission error on your E Drive?

     

    Thanks

    Sethu Srinivasan[MSFT]

    SQL Server

    Wednesday, June 30, 2010 12:29 AM
    Moderator
  • Hi Sethu,

    Thanks for the reply,

    I get the same error, when I open a "new query" window in SSMS. and run the same BACKUP Statement, on C and E drive

    The following is the script, used in SSIS and in SSMS "NEW QUERY" window:

    BACKUP

     

    DATABASE [M1Test] TO DISK = N'E:\M1G3Datastore\M1Test.bak'

    WITH

     

    NOFORMAT, INIT, NAME = N'M1Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    The error reads as follows:

    Msg 3201, Level 16, State 1, Line 2

    Cannot open backup device 'E:\M1G3Datastore\M1Test.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

    Msg 3013, Level 16, State 1, Line 2

    BACKUP DATABASE is terminating abnormally.


    sword
    NOFORMAT, INIT, NAME = N'M1Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
    DATABASE [M1Test] TO DISK = N'E:\M1G3Datastore\M1Test.bak'
    Wednesday, June 30, 2010 1:42 AM
  • Hello,

    Did you manage to get any closure on what this issue was? I have the same scenario where the backup has been running fine, however overnight it failed with the same error:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device 'E:\hg_backup_20100915.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    My server is running on Network Service account and this has full permission to the specified drive. As above, backing up to another drive works, however I do not have enough capacity to simply change the path for all databases.

    This is super weird given that nothing has changed at all since the last successful backup (which was the previous night).

    Any thoughts?

    Cheers

    Troy

     

    Tuesday, September 14, 2010 8:32 PM
  • Okay interestingly enough I found this link: http://www.kodyaz.com/forums/thread/10397.aspx which mentions adding in the SQLServerMSSQLUser$machine_name$MSSQLSERVER user group and assigning full control.

    I have done this and the backup is now working - any ideas why this has happened? And additionally do others see this as a back move assigning this group full control?

    Keen to hear feedback.

    Cheers

    Troy

    • Proposed as answer by AleBeerenhoff Tuesday, September 14, 2010 8:40 PM
    Tuesday, September 14, 2010 8:40 PM
  • hi there,

    To all who are still experiencing the error message: "failed to retrieve text for this error. Reason: 15105 ”", I have just found out the solution. I have been following the discussion on this thread, but luckily the staff at asphostcentral.com did help me out with my problem (two thumbs up for them!). I did have SQL 2008 R2 database hosted with them and I could not make any backup initially. Here is what you guys need to do:


    1) Launch SQL Server Configuration Manager - you should be able to find this program on your: Start menu -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

    2) Expand tree view "SQL Server Services". On right pane, youl will see the list of Services installed. In my case, I will have to look at the SQL Server 2008 R2 instance. Previously, it was set to run under "NT AUTHORITY\NETWORK SERVICE"

    3) Click on this SQL Server instance -> Properties and change the "Log on" to LOCAL SYSTEM. The system will prompt you that it needs to restart the SQL Service. Please do so.

    4) Once this is done, you can then backup your database without any hassles!

    Hope this helps!
    • Proposed as answer by Alan-Sy Thursday, December 15, 2011 7:34 PM
    • Unproposed as answer by TiborKMVP, Moderator Thursday, December 15, 2011 7:42 PM
    • Proposed as answer by Sarwath Tuesday, March 05, 2013 4:08 PM
    Wednesday, March 02, 2011 4:04 AM
  • I'm sorry, but I have to unpropose that as answer. Local systrem is a local administrator and that goes against best practices. You want, for security reasons, run software with as low priviliges as possible, and it doesn't get much higher (locally) than an administrator. If the "fail to retrieve error" is a problem (this error is not what the OP in this thread is about), then just use the OS NET HELPMSG command (unless there is some other solution which doesn't require to run with local admin rights).
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, December 15, 2011 7:44 PM
    Moderator
  • Figured it out...

    Previously I shared the folder containing my db files and then removed the share afterwards. That is when i got the exception message above. I just compared the security on the folder with that of another location containing db files....

    The difference is that mine was missing the local "Authenticated Users" group.

    Just add that and allow the Modify permission.

    Thursday, June 07, 2012 11:33 AM
  • Hi

    I have spent a couple of hours today trying to attach an mdf file of the adventureworks2008R2 DB. I tried quite a few things including attaching through GUI and T-SQL script. I ensured that I was running SSMS as Administrator. Clicking on Attach in GUI gave an error right away. So, I tried the T-SQL method. After trying some variations of the script, I found that the following one worked:

    USE [master]
    GO
    CREATE DATABASE AdventureWorks ON
    ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\AdventureWorks2008R2_Data.mdf' )
    FOR ATTACH_REBUILD_LOG
    GO

    However, this produced errors at first. I then tried changing the security permissions for folder 'Microsoft SQL Server' and added the user 'Authenticated Users' to this folder and gave it full control. Running the above script thereafter worked  Hurrah!!

    Friday, March 08, 2013 4:28 AM
  • Thank you very much, worked great.
    Monday, April 14, 2014 4:31 PM
  • Para mi la solucion fue sencilla, solo hay que iniciar la sesion de Como Administrador y se puede cargar sin problemas las bases
    Saturday, May 17, 2014 4:27 PM
  • It worked fine... thanks for the help...much appreciated.
    Monday, May 26, 2014 8:12 AM