Operating system error 2 the system cannot find the file specified

Operating system error 2 the system cannot find the file specified

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Log Restore Job Failed

1. Event Log Error

BackupDiskFile::OpenMedia: Backup device ‘\xxx’ failed to open. Operating system error 2(The system cannot find the file specified.).

2. SQL Error log.

Date 8/24/2018 12:09:15 AM
Log SQL Server (Current — 4/7/2018 9:35:00 PM)
Source spid55
Message
Error: 18204, Severity: 16, State: 1.

Date 8/24/2018 12:09:15 AM
Log SQL Server (Current — 4/7/2018 9:35:00 PM)
Source spid55
Message
BackupDiskFile::OpenMedia: Backup device ‘\xxxxx’ failed to open. Operating system error 2(The system cannot find the file specified.).

we are verify RESTORE VERIFYONLY FROM DISK «please find below error»

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device ‘\xxxxx’. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Источник

Operating system error 2 the system cannot find the file specified

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Using SQL Server 2008R2 Express.

My db is a .mdf, 9GB in size. The .ldf is 23gb in size.

I am trying to attach my .mdf to SQL Server 2008R2 Express. The DB was take from our live environment. I am working in a test environment with 2008R2 Express.

When try attach — receive error:

Attach database failed for server «myserver/name». (Microsoft.SqlServer.Smo)

Additional information:

-> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-> Unable to open the physical file «e:Myfilename». Operating system error 2: «2(The system cannot find the file specified)». (Microsoft SQL Server, Error 5120)

I do not know why «cannot find the file specified» it is sitting right there on the same HDD as my SQL installation — User has full control to files an folders.

I need to attach this mdf! Is my .ldf too large or 2008R2 express?

Thanks for your help!

Answers

I committed out the line to attach the log file. Created a new log file.

Issue resolved. Possible issue was corrupt log file or .ldf too large for sqlexpress.

Thanks for you help everyone

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided «AS IS» with no warranties, and confers no rights. Please remember to click «Mark as Answer» and «Vote as Helpful» on posts that help you.
This can be beneficial to other community members reading the thread.

I’m on Server 2003.

I right clicked SSMS — Select «run as»

I have two options. Current user or «following user:

I choose current user (myCompay_ADmyID) — then removed the check box for «run this program with restricted access»

I will ask one of our DBA’s to login with SQLAdmin account — maybe this will help?

Using SQL Server 2008R2 Express.

My db is a .mdf, 9GB in size. The .ldf is 23gb in size.

I am trying to attach my .mdf to SQL Server 2008R2 Express. The DB was take from our live environment. I am working in a test environment with 2008R2 Express.

When try attach — receive error:

Attach database failed for server «myserver/name». (Microsoft.SqlServer.Smo)

Additional information:

-> An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-> Unable to open the physical file «e:Myfilename». Operating system error 2: «2(The system cannot find the file specified)». (Microsoft SQL Server, Error 5120)

I do not know why «cannot find the file specified» it is sitting right there on the same HDD as my SQL installation — User has full control to files an folders.

I need to attach this mdf! Is my .ldf too large or 2008R2 express?

what command are you using to attach this database ?

did you specify the location of both files (.mdf and .ldf) for attaching and does the SQL Server service account have full access to this directory?

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Источник

Operating system error 2 the system cannot find the file specified

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

Every 2 hours, I have a SQL Maintenance plan run that creates a differential backup, as well as cleans up .bak files older than 23 hours. Recently, we have been seeing an error in the event viewer stating:

BackupDiskFile::OpenMedia: Backup device ‘X:Differentiallive_backup_2018_03_20_163003_3924419.bak failed to open. Operating System error 2(The system cannot find the file specified.).

Looking in X:Differential, the backup gets created successfully, however its labeled ‘live_backup_2018_03_21_163003_7732180.bak’, and no file named ‘live_backup_2018_03_20_163003_3924419.bak’ is in the folder.

The SQL Server Log shows Error: 18204, Severity: 16, State: 1. at 4:30:02, and then at 4:30:03, the differential completes fine. The SQL Server Agent Log shows no errors. The domain account running the SQL Server Agent has full access to the drive. Any pointers in the right direction would be much appreciated!

Every 2 hours, I have a SQL Maintenance plan run that creates a differential backup, as well as cleans up .bak files older than 23 hours. Recently, we have been seeing an error in the event viewer stating:

BackupDiskFile::OpenMedia: Backup device ‘X:Differentiallive_backup_2018_03_20_163003_3924419.bak failed to open. Operating System error 2(The system cannot find the file specified.).

Looking in X:Differential, the backup gets created successfully, however its labeled ‘live_backup_2018_03_21_163003_7732180.bak’, and no file named ‘live_backup_2018_03_20_163003_3924419.bak’ is in the folder.

The SQL Server Log shows Error: 18204, Severity: 16, State: 1. at 4:30:02, and then at 4:30:03, the differential completes fine. The SQL Server Agent Log shows no errors. The domain account running the SQL Server Agent has full access to the drive. Any pointers in the right direction would be much appreciated!

Your differential plan is failing but there is other maintenance plan, job or TSQL running which is taking backup see the date difference in both the files. Please port the output of below query, source

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

Источник

Operating system error 2 the system cannot find the file specified

Вопрос

I kept getting this error:

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

Все ответы

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

RESTORE FILELISTONLY FROM DISK = ‘C:TEMPDemo.bak’ WITH FILE = 1
GO

A Fan of SSIS, SSRS and SSAS

I got this similar error:

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!

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

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

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.

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.

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.

Tibor Karaszi, SQL Server MVP (Web Blog)

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

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

A Fan of SSIS, SSRS and SSAS

Tibor Karaszi, SQL Server MVP (Web Blog)

Tibor Karaszi, SQL Server MVP (Web Blog)

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.

So, I created an empty database named Demo. Then I ran this code.

And I got this new error:

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:TEMPDemo.bak’
GO

A Fan of SSIS, SSRS and SSAS

• Предложено в качестве ответа Rachel_Wang Microsoft contingent staff 16 мая 2019 г. 7:56

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.

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

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.

Источник

• November 11, 2009 at 4:39 am

  #227822

  Dear All,

  I am getting a problem.I have SQL server 2005 installed on C:..drive.and I have S: drive as a LUN.

  Now If I am trying to create a database on S: drive then it is giving error:»directory lookup for the file failed with the operating system error 2 Microsoft Error 5133″

  Help..Please

  Thanks

• stevecable

  SSC Veteran

  Points: 230

  Ajay,

  Does the service account that you are using have permission to read/write there?

  Can that account do anything on that drive outside SQL Server? For example, can you copy a simple text file there? You want to confirm that there is not an operating system or permission issue first.

  Steve

• Gail Shaw

  SSC Guru

  Points: 1004494

  Is this a clustered SQL Server?

  Are you creating the DB from management studio gui or from script? If script, paste it here please?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• forsqlserver

  SSCoach

  Points: 18902

  That is not clustered…

  Thanks

• forsqlserver

  SSCoach

  Points: 18902

  I have detached the database and attach it after moving DB files to S: drive (SAN Disk)

  I am Using GUI of SSMS.

  Thanks

• forsqlserver

  SSCoach

  Points: 18902

  Straightforward: If I want to create the database in different drive then this error is coming…

  Help Please…

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004494

  Please post the code used to create the DB and the exact error message that you get.

  Either write the create DB statement yourself or use the ‘script’ button on the Create database dialog.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• forsqlserver

  SSCoach

  Points: 18902

• Gail Shaw

  SSC Guru

  Points: 1004494

  guptaajay1985 (11/12/2009)

  ---

  Directory lookup for the file «e:as.mdfas.mdf» failed with the operating system error 2

  That doesn’t look like a valid filename to me. Double check what it is that you’re setting things to, make sure there’s no typo happening.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• forsqlserver

  SSCoach

  Points: 18902

  Thanx Gilla ,

  as.mdf means application server.

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004494

  guptaajay1985 (11/13/2009)

  ---

  Thanx Gilla ,

  as.mdf means application server.

  Huh?

  Do you really have a directory called E:as.mdf? Not a file, a directory?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• forsqlserver

  SSCoach

  Points: 18902

  Dear I am directly saving it in the E: drive.

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004494

  Not according to the error message you aren’t.

  Directory lookup for the file «e:as.mdfas.mdf» failed

  Check what you’ve got set for the directory and what you have for the filename. Remember that the gui has one place for the directory, one for the file. Take a screenshot of the create database dialog and post here.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• forsqlserver

  SSCoach

  Points: 18902

  The Upload limit has been exceeded

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004494

  Then go to «Edit Attachments» (it’s part of the user control panel) and delete some of your old uploads.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass