locked
BACKUP LOG command failed - need some help !! RRS feed

  • Question


  • Hello,

    When I execute the following transaction log backup TSQL in SSMS

    BACKUP LOG ProductDB 
    TO DISK = 'D:\Database_Backup\Trans\ProductDB.trn';

    I get this error:

    Msg 3201, Level 16, State 1, Line 2
    Cannot open backup device 'D:\Database_Backup\Trans\ProductDB.trn'. Operating system error 21(The device is not ready.).
    Msg 3013, Level 16, State 1, Line 2
    BACKUP LOG is terminating abnormally.

    I also have a daily database backup job that puts the backup file under this directory.

    D:\Database_Backup\Data

    So I changed the path to this and it still fails.

    BACKUP LOG ProductDB 
    TO DISK = 'D:\Database_Backup\Data\ProductDB.trn';

    However if I change it to this path, it works:

    BACKUP LOG ProductDB 
    TO DISK = '\\MyServer\Data\ProductDB.trn';

    This turns out to be the path I used a few weeks back when I backed up the log for the very
    first time.  But I now wanted to store my transaction log backups in D:\Database_Backup\Trans.  
    How do I fix this?


    Thanks








    Tuesday, October 6, 2015 6:09 PM

Answers

  • Erland,

    You were absolutely right!!  I executed the BACKUP LOG against a different server.  The difference in server name was between the letter "O" and the number "0" (zero).   I didn't catch it.   Operator error.  :(

    Thanks for everyone's inputs.

    • Marked as answer by Sidney L Friday, October 9, 2015 7:03 PM
    Friday, October 9, 2015 7:03 PM

All replies

  • Can you access the D: drive from Windows?

    What is the D drive? A local disk? A mapped share? Something else?

    The message "The device is not ready" comes from Windows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 6, 2015 9:55 PM
  • It sounds like a permission issue.

    The share will probably have permissions for the SQL Server account so it can travers e to the correct folder whereas the root of the D:\ drive may not have the permissions and so it cannot traverse to find the folder.

    Try granting the SQL Server account "List folder contents" at the root level and see if that helps.


    Martin Cairney SQL Server MVP

    Tuesday, October 6, 2015 11:22 PM
  • Yes, it appears to be a permission issue. I would grant the permission to the folder for SQL Server Agent account. 

    Tuesday, October 6, 2015 11:40 PM
  • I am not sure whether this can be permission issue. I would expect OS error 5 for access denied.

    You need to confirm what is D drive like erland asked ? I guess that is mapped drive and there is some issue.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, October 7, 2015 7:02 AM
  • The D:\ drive is a local disk.  As I mentioned in my original post, I have daily database backup job that runs successfully and it writes to D:\Database_Backup\Data.  Even when I used this path in the log backup, it failed.  The agent jjob account is a member of the local admin group where the instance of the sql server is running.  The problem is a bit strange.   If I use this UNC \\MyServer\Data, it works.  It does appear to be a permission issue but if that was the case, why wouldn't it work when using D:\Database_Backup\Data??

    Thanks

    Wednesday, October 7, 2015 12:16 PM
  • I think you have mixed up the machines somewhere and you are not running the command where you think. I would guess that the D: you think is a local disk is in fact a CD drive with no disk loaded. (I get exactly that error when I do DIR on my CD drive.)

    Since \\MyServer\Data works, my guess that the window from where you are run the BACKUP command is not connected to an instance on MyServer. Check SELECT @@servername.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, October 7, 2015 12:53 PM
  • Erland,

    Yes, I'm looking into this.  Just to confirm.  When I manually run the BACKUP LOG TSQL in SSMS, it runs under the sql server service account, am I right?  Thanks

    Wednesday, October 7, 2015 1:26 PM
  • Erland,

    Yes, I'm looking into this.  Just to confirm.  When I manually run the BACKUP LOG TSQL in SSMS, it runs under the sql server service account, am I right?  Thanks

    Yes it would run under SQl Service account.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, October 7, 2015 2:05 PM
  • Erland,

    You were absolutely right!!  I executed the BACKUP LOG against a different server.  The difference in server name was between the letter "O" and the number "0" (zero).   I didn't catch it.   Operator error.  :(

    Thanks for everyone's inputs.

    • Marked as answer by Sidney L Friday, October 9, 2015 7:03 PM
    Friday, October 9, 2015 7:03 PM
  • Oh n0! :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 9, 2015 9:22 PM