Post reply

directory lookup for the file failed with the operating system error 2

  • November 16, 2009 at 1:55 am

    #1079548

    Alternatively:

    Just before you click 'OK' to create the database from the New Database screen, click on the Script button at the top.

    That will create a script. Copy and paste that here. It should then be obvious where the problem lies.

    It doesn't seem smart to me to call a database 'AS'. AS is a reserved word. I see you are using SP2 - perhaps there was a bug in SSMS back then when creating a database called AS...? In any case, you should really be using SP3 - and don't forget to apply it to the client tools too.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 16, 2009 at 2:05 am

    #1079552

    I'd prefer to see the screenshot, to see exactly what the path has been set to, exactly what the filename has been set to. The script's just going to show the combination, which will probably (hopefully) be the same as the error message. I suspect what's happened is that the file name has been entered in both the path and file name boxes.

    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

  • November 16, 2009 at 2:07 am

    #1079554

    It has been done...I was giving the path -- e:\as.mdf while I should give the path as e:\ only.

    Thanks

  • November 16, 2009 at 2:28 am

    #1079564

    You were right! My fear was that the screenshot wouldn't show the path column (or the important bit):

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 16, 2009 at 7:09 am

    #1079653

    Ajay, do you understand directory structures and file paths?

    If you look at what Gail posted, you appear to be trying to use a directory called "as.mdf" to hold a file called "as.mdf". Doesn't matter what "as" stands for, the problem is the file path.

    If you don't understand directory structures and file paths, then you need to hire someone who does understand technical matters who can help you to create your database.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 16, 2009 at 3:16 pm

    #1079946

    GSquared (11/16/2009)

    Ajay, do you understand directory structures and file paths?

    I think he does - it's just not massively clear that the 'path' box requires just a directory path - not a full path and file name. It's a mistake I remember making many years ago too.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 16, 2009 at 9:17 pm

    #1080015

    once again error is coming : S: is a san disk

    I am running command:

    CREATE DATABASE [test2] ON PRIMARY

    ( NAME = N'test2', FILENAME = N's:\test2.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

    LOG ON

    ( NAME = N'test2_log', FILENAME = N's:\test2_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

    GO

    Error:

    Msg 5133, Level 16, State 1, Line 1

    Directory lookup for the file "s:\test2.mdf" failed with the operating system error 3(The system cannot find the path specified.).

    Msg 1802, Level 16, State 1, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Thanks

  • November 17, 2009 at 1:35 am

    #1080085

    Is there a drive named 'S:'?

    Does the SQL Server service account have full access to that drive?

    If this is a clustered SQL Server, is that drive a dependency of the SQL service?

    If you use the gui to browse for a filename, does the s: drive appear?

    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

  • November 17, 2009 at 4:24 am

    #1080143

    Actually the drive Letter had changed by the client from S: to G:,therefore problem was occurring.

    Thanks

  • August 19, 2012 at 3:22 pm

    #1527413

    Hi, I'm having the same problem when changing the filenames. The original script that contains the database name [DRI] works fine. It fails with the "" message when I only change [DRI] to [DRI_KY193]. The os filenames were also changed in the script. I actually created a new folder on the server named ""S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\" and added sa full access to it in permissions. Both error message and the failing script is shown below.

    Thanks, Stanley

    The ERROR MESSAGE is:

    Msg 5133, Level 16, State 1, Line 1

    Directory lookup for the file "S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\DRI_KY193.mdf" failed with the operating system error 2(The system cannot find the file specified.).

    Msg 1802, Level 16, State 1, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Msg 5011, Level 14, State 5, Line 1

    User does not have permission to alter database 'DRI_KY193', the database does not exist, or the database is not in a state that allows access checks.

    Msg 5069, Level 16, State 1, Line 1

    ALTER DATABASE statement failed.

    Msg 911, Level 16, State 4, Line 1

    Database 'DRI_KY193' does not exist. Make sure that the name is entered correctly.

    THE FAILING SCRIPT IS:

    Use [master]

    CREATE DATABASE [DRI_KY193] ON PRIMARY ( NAME = N'DRI_KY193', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\DRI_KY193.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), FILEGROUP [FG_ImagesPDF] ( NAME = N'FG_ImagesPDF', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\FG_ImagesPDF.ndf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), FILEGROUP [FG_ImagesSLA] ( NAME = N'FG_ImagesSLA', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\FG_ImagesSLA.ndf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), FILEGROUP [FG_ImagesTIF] ( NAME = N'FG_ImagesTIF', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\FG_ImagesTIF.ndf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ) LOG ON ( NAME = N'DRI_KY193_log', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\DRI_KY193_Log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB ), ( NAME = N'FG_ImagesPDF_Log', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\FG_ImagesPDF_Log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB ), ( NAME = N'FG_ImagesSLA_Log', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\FG_ImagesSLA_Log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB ), ( NAME = N'FG_ImagesTIF_Log', FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\FG_ImagesTIF_Log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB ) ALTER DATABASE [DRI_KY193]

    SET COMPATIBILITY_LEVEL = 100

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

    begin

    EXEC [DRI_KY193].[dbo].[sp_fulltext_database] @action = 'enable'

    end

    ALTER DATABASE [DRI_KY193]

    SET ANSI_NULL_DEFAULT OFF

    ALTER DATABASE [DRI_KY193]

    SET ANSI_NULLS OFF

    ALTER DATABASE [DRI_KY193]

    SET ANSI_PADDING OFF

    ALTER DATABASE [DRI_KY193]

    SET ANSI_WARNINGS OFF

    ALTER DATABASE [DRI_KY193]

    SET ARITHABORT OFF

    ALTER DATABASE [DRI_KY193]

    SET AUTO_CLOSE OFF

    ALTER DATABASE [DRI_KY193]

    SET AUTO_CREATE_STATISTICS ON

    ALTER DATABASE [DRI_KY193]

    SET AUTO_SHRINK OFF

    ALTER DATABASE [DRI_KY193]

    SET AUTO_UPDATE_STATISTICS ON

    ALTER DATABASE [DRI_KY193]

    SET CURSOR_CLOSE_ON_COMMIT OFF

    ALTER DATABASE [DRI_KY193]

    SET CURSOR_DEFAULT GLOBAL

    ALTER DATABASE [DRI_KY193]

    SET CONCAT_NULL_YIELDS_NULL OFF

    ALTER DATABASE [DRI_KY193]

    SET NUMERIC_ROUNDABORT OFF

    ALTER DATABASE [DRI_KY193]

    SET QUOTED_IDENTIFIER OFF

    ALTER DATABASE [DRI_KY193]

    SET RECURSIVE_TRIGGERS OFF

    ALTER DATABASE [DRI_KY193]

    SET DISABLE_BROKER

    ALTER DATABASE [DRI_KY193]

    SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    ALTER DATABASE [DRI_KY193]

    SET DATE_CORRELATION_OPTIMIZATION OFF

    ALTER DATABASE [DRI_KY193]

    SET TRUSTWORTHY OFF

    ALTER DATABASE [DRI_KY193]

    SET ALLOW_SNAPSHOT_ISOLATION OFF

    ALTER DATABASE [DRI_KY193]

    SET PARAMETERIZATION SIMPLE

    ALTER DATABASE [DRI_KY193]

    SET READ_COMMITTED_SNAPSHOT OFF

    ALTER DATABASE [DRI_KY193]

    SET HONOR_BROKER_PRIORITY OFF

    ALTER DATABASE [DRI_KY193]

    SET READ_WRITE

    ALTER DATABASE [DRI_KY193]

    SET RECOVERY FULL

    ALTER DATABASE [DRI_KY193]

    SET MULTI_USER

    ALTER DATABASE [DRI_KY193]

    SET PAGE_VERIFY CHECKSUM

    ALTER DATABASE [DRI_KY193]

    SET DB_CHAINING OFF

  • August 19, 2012 at 9:19 pm

    #1527435

    Further clarification...

    If I were to search and replace all occurances of DRI_KY193 with DRI, the script works without errors.

    The DRI database is to be created in subfolder "S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_DATA\"

    The DRI_KY193 database is to be created in subfolder "S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\"

    And yes, you can browse to that folder as both of these paths exists...

    Thanks, Stanley

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply