none
Cannot open backup device 'C:\TEMP\Demo.bak'. Operating system error 2(The system cannot find the file specified.). RRS feed

  • Question

  • I have tried:

     RESTORE DATABASE [DemoDB]   
         FROM DISK = 'C:\TEMP\Demo.bak'   
          WITH FILE=1,   
            NORECOVERY;  

    And:

    RESTORE DATABASE [DemoDB]  
       FROM DISK = 'C:\TEMP\Demo.bak'   
       WITH RECOVERY,  
       MOVE 'Demo_Data' TO 'C:\TEMP\Database\Demo_Data',   
       MOVE 'Demo_Log' TO 'C:\TEMP\Database\Logs\Demo_Log.ldf';  
    GO

    I kept getting this error:

    Msg 3201, Level 16, State 2, Line 15
    Cannot open backup device 'C:\TEMP\Demo.bak'. Operating system error 2(The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 15
    RESTORE DATABASE is terminating abnormally.

    NETWORK SERVICE and MSSQLSERVER users do have access to C:\Temp folder.



    • Edited by Charlie2 Tuesday, May 14, 2019 5:52 PM
    Tuesday, May 14, 2019 5:49 PM

All replies

  • Run the following query and see if you get the output:

    RESTORE FILELISTONLY FROM DISK = 'C:\TEMP\Demo.bak' WITH FILE = 1
    GO


    A Fan of SSIS, SSRS and SSAS

    Tuesday, May 14, 2019 7:50 PM
  • I got this similar error:

    Msg 3201, Level 16, State 2, Line 22
    Cannot open backup device 'C:\TEMP\Demo.bak'. Operating system error 2(The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 22
    RESTORE FILELIST is terminating abnormally.

    Tuesday, May 14, 2019 7:54 PM
  • seems to be more of a privileges issue. Can you please move your backup to default backup location for the SQL instance and then try the command.

    to know what default backup location of the instance is, do the following:

    1. On Object Explorer, right click on SQL instance and choose properties
    2. On the properties screen, select page "Database Setting" on the left navigation tab
    3. On the right side, you will find "Database default locations". Check for "Backup:" location

    This will work for you!

    Tuesday, May 14, 2019 8:26 PM
  • 1. Does the backup file exists on that location?

    2. Are you on the correct server? If you are remoting into another SQL Server, then backup file should be present in that server.

    3. Access issue has already been pointed out. Ensure that the sql service account has access to that backup file/folder


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Wednesday, May 15, 2019 3:15 AM
  • Image result for find default backup location sql server

    keep your backup mention mention backup location and try.There by deafult permission is available in location so restoration will sucess.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx


    • Edited by Av111 Wednesday, May 15, 2019 4:12 AM
    Wednesday, May 15, 2019 4:10 AM
  • Hi Charlie2,

     

    Thank you for your question.

     

    Firstly, please check your path using following script. And it will show you all files and folder in 'C' driver. Please check if you already have 'temp' folder.

     

    execute master..xp_dirtree 'c:',1,1


    If it really exists, please check access. For more information , please refer to  Configure File System Permissions for Database Engine Access .

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 15, 2019 6:23 AM
  • You have been given some strange advice in this thread. The default backup folder is irrelevant for your error. Also, this is *not* a permission error, that would give you a different error message. The file 'C:\TEMP\Demo.bak' isn't there, quite simply. Remember that the file is on the server machine, not the client machine.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 15, 2019 9:47 AM
    Moderator
  • Rachel,

    Thanks for trying to help. I did try what you suggested.

    Wednesday, May 15, 2019 1:10 PM
  • Image result for find default backup location sql server

    keep your backup mention mention backup location and try.There by deafult permission is available in location so restoration will sucess.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx


    How do I change the location of the Data and Log? I tried pointing to the correct folder but after I clicked OK and come back, it reverted back.
    Wednesday, May 15, 2019 1:16 PM
  • As Tibor mentioned, is the backup file Demo.bak in the folder C:\Temp on the server or your local computer where you want to restore the database?

    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 15, 2019 1:25 PM
  • The GUI show the current setting. It requires a re-start to take effect, and after such a re-start you will see the change.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 15, 2019 1:51 PM
    Moderator
  • The C:\Temp is on the sql server.
    Wednesday, May 15, 2019 1:54 PM
  • You are not helping anyone with comments like this. No one can read your mind, see your screen, or access the machines in your situation. It is difficult to provide useful suggestions without your help in providing context and useful information. What was the result from executing that command? I also suggest that you try the same command using "C:\TEMP" as the directory. 
    Wednesday, May 15, 2019 1:56 PM
  • TiborK, restart SQL Server (MSSQLSERVER) service or the actual physical server? I did restart the SQL Server (MSSQLSERVER) service.
    Wednesday, May 15, 2019 1:58 PM
  • Re-start of the SQL Server service. I tested it before I posted and it behaved as I described.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 15, 2019 2:06 PM
    Moderator
  • I think the problem has to do with this. The "E" drive for Data and Log do not exist.

    How do I change these settings? I clicked the ... icon on the right and picked the correct location but it reverts back.

    Wednesday, May 15, 2019 2:23 PM
  • I fixed the Database default locations but I still get the same errors.
    Wednesday, May 15, 2019 2:55 PM
  • So, I created an empty database named Demo. Then I ran this code.

    use master
    restore database [DEMO] from
    disk = 'C:\TEMP\DEMO.bak'
    with replace, stats = 10; 

    And I got this new error:

    Msg 5133, Level 16, State 1, Line 8
    Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DEMO_1.ndf" failed with the operating system error 3(The system cannot find the path specified.).
    Msg 3156, Level 16, State 3, Line 8
    File 'ftrow_FullTextCatalog' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DEMO_1.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 5133, Level 16, State 1, Line 8
    Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DEMO_2.ndf" failed with the operating system error 3(The system cannot find the path specified.).
    Msg 3156, Level 16, State 3, Line 8
    File 'ftrow_FullTextCatalog_CC810_COMM_DIR' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DEMO_2.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 5133, Level 16, State 1, Line 8
    Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DEMO_EBMS_DOCS.ndf" failed with the operating system error 3(The system cannot find the path specified.).
    Msg 3156, Level 16, State 3, Line 8
    File 'EBMS_DOCS' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DEMO_EBMS_DOCS.ndf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 8
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 8
    RESTORE DATABASE is terminating abnormally.

    Wednesday, May 15, 2019 5:17 PM
  • Looks like the database you want to restore, which has the multiply data files. You can run the following query and get the file information:

    RESTORE FILELISTONLY FROM DISK = 'C:\TEMP\Demo.bak'
    GO


    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 15, 2019 6:50 PM
  • Guoxiong,

    I'm able to execute that t-sql without any error. What does this tell me? It does show me the LogicalName, PhysicalName, Type, FielGroupName and so on.

    Wednesday, May 15, 2019 7:46 PM
  • That tells you the names of the data files and log files and file groups. Based on the error messages you posted before, the backup database may have four data flies:

    PRIMARY.mdf
    DEMO_1.ndf
    DEMO_2.ndf
    DEMO_EBMS_DOCS.ndf

    But you empty database may only have the PRIMARY.mdf. You need to create the other three data files before you restore the database.


    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 15, 2019 7:57 PM
  • Don't create the database before you do a restore. It will be created for you by the restore command. Creating it first will only cause confusion, as it did for you.

    Also, the location and name for database files will not be picked up from the default folder (which is why I said earlier that this config is irrelevant for your situation). You have to use the MOVE option for the RESTORE command, and RESTORE FILELISTONLY gives you info for that. Google and you should find plenty of examples. Or, use the restore GUI, which will help you. Delete the database you created. Then right-click the "Databases" folder and open the restore GUI from there.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, May 16, 2019 12:56 PM
    Moderator