Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

.mdf" failed with the operating system error 2 Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 9:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 3:27 AM
Points: 27, Visits: 53
Hi, I'm having a problem when creating a new database to a new file system location. The original script that contains the database name [DRI] works fine. It fails with the "DRI_KY193.mdf" failed with the operating system error 2(The system cannot find the file specified.)." message when I only change [DRI] to [DRI_KY193] in the script. The os filenames were also changed in the script to reflect both the new database name as well as its new location.

I manually 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.

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 these folders as both of these paths exists...

Thanks, Stanley



Both error message and the failing script is shown below.

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
Post #1347545
Posted Tuesday, August 21, 2012 12:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:17 PM
Points: 286, Visits: 576
stanlyn (8/20/2012)


I manually 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.


Access to this new sub-folder seems, so far, to be the only potential cause that I can think of. You said you have 'added sa full access to it'; does the SQL Server Service Account have the correct NTFS permissions to access and create files in this folder? Have you tried creating this database in a different folder, e.g. the [DRI] folder?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1347583
Posted Tuesday, August 21, 2012 3:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
I manually 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.


SA is a sql user how did you configured permission in a windows folder, am i missing something.

check in which account sql service is running and configure write access for that account and try.



Regards
Durai Nagarajan
Post #1347647
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse