locked
Backup Database hangs with invalid path RRS feed

  • Question

  • I tried running the following command programmatically:

    BACKUP DATABASE [TestDB] TO  DISK = N'\\SQL-SVR-1\SQL2012\c$\TestDB.bak' 

    While I understand the path is invalid, I would expect that the query would return an error.  Instead, the query hangs indefinitely.  It ran for 24 minutes until it hit the connection timeout limit.  Why can't it return an error right away if the path is invalid?

     

    Thursday, June 9, 2016 4:12 PM

All replies

  • I would guess that it got stuck in an OS call to Windows. What was the waittype during those 24 minutes?
    What connection timeout limit did it? What error message did you get?

    Thursday, June 9, 2016 9:39 PM
  • 10 minutes in, and I queried the sysprocesses table and I think these are the values you're interested in:

    waittype waittime lastwaittype
    0x008C 403976 BACKUPTHREAD                    
    0x01EA 403939 PREEMPTIVE_OS_FILEOPS           

    I think what happened is that the SQLConnection we created in our code had a timeout limit of 24 minutes (or thereabouts).  So when it got that far, our code received a timeout response.  

    Sorry for the delayed response.  I was expecting to have gotten an email if anyone replied, so I didn't think to come check.  

    FWIW, I checked 5 minutes later, and the numbers went to 

    704805
    704768

    So it's almost like it hung for a few minutes before it even went into this wait state.

    After 45 minutes in management studio, it gives this error:

    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device '\\SQL-SVR-1\SQL2012\c$\TestDB.bak'. Operating system error 67(The network name cannot be found.).
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    Wednesday, June 29, 2016 7:53 PM
  • Perhaps you need change to 

    \\SQL-SVR-1\c$\TestDB.bak'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 30, 2016 5:17 AM
    Answerer
  • As I suspected, it was hung in a call to Windows. You can read about PREEMPTIVE_OS_FILEOPS here:
    https://blogs.msdn.microsoft.com/ialonso/2012/07/25/whats-in-preemptive_os_fileops-wait-type/

    For some reason Windows gets stuck when trying to resolve this path. Why, I have no idea. While the path certainly looks incorrect, it's not syntactically incorrect. That is, there could well be a share SQL2012 on SQL-SVR-1, and in that share there could be a folder C$.

    What happens if you run:

    DIR \\SQL-SVR-1\SQL2012\c$\TestDB.bak

    from a command line window? How log time does it take before you get a response?

    Thursday, June 30, 2016 9:46 AM