.mdf" failed with the operating system error 2

[stanlyn]

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

 

[TomKane]

I tried running your script on my local SQL Server (version 2005) and the create database part worked fine - I had to change the folder location as I don't have a drive S but otherwise it worked.

Are you running the script as "sa"? It does sound like a rights issue.. maybe you could check your folder locations and rights to make sure everything lines up.

 

[TomKane]

Further to what I just posted - is your s drive part of your computer or is it a mapping to an external drive?

If it's not and you have to - check out this blog post - it's not something I've tried but the dude who owns the blog really knows his stuff

http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/

 

[stanlyn]

Hi Tom,

Thanks for taking a stab at this...

The S: drive is mapped to another machine on the local network. SQL server is on the machine that contains the S drive and both versions of the script is ran from the same machine with the same credentials. One version [DRI] works and the other [DRI_KY193] fails. The S: drive is a local drive on the sql server as well as a mapped drive on the client...

When I first originally ran the 2nd version that fails, I assumed it would create the folders as mentioned in the script. Since it failed, I assumed that maybe I would need to manually create the folder structure, so I did. I then gave it the same perms as the folder that works. Nothing I've tried works with the 2nd version...

Thanks, Stanley

 

[TomKane]

Just to clarify - SQL Server is on another computer and S: is a drive mapping on your computer to that computer or is S: a local drive letter on the other machine?

 

[SQLBill]

Try putting a GO or semi-colon ( before this statement.

ALTER DATABASE [DRI_KY193]
SET COMPATIBILITY_LEVEL = 100

Maybe the database isn't created yet and it is trying to alter it too soon.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[stanlyn]

Tom,

>> Just to clarify - SQL Server is on another computer and S: is a drive mapping on your computer to that computer or is S: a local drive letter on the other machine?

1. SQL Server machine has a local drive S: where this database is to be installed.
2. The client machine with ssms has a mapped S: drive that maps to the SQL Servers machine's S: drive.

Remember that both [DRI] and [DRI_KY193] is to create their databases to this same S: drive, and one succeeds and one fails.

Thanks, Stanley

 

[stanlyn]

SQLBill, I added a GO where you asked me to with no difference... still errors...

Other test results...

I was able to get this to work, and note that this is the only thing I've done...

In the file path names I changed all the "\DRI_KY193_DATA\" to "\Data\KY193\" and that works. Note that the "\Data" part of that path is the same folder that was created when the sql instance was created. Also note that the script changes below fails and succeeds both locally and remotely. Here are the before and after lines pertaining to the .mdf file.

1. Fails... FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DRI_KY193_DATA\DRI_KY193.mdf'

2. Works... FILENAME = N'S:\SQL\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\KY193\DRI_KY193.mdf'

Go figure... Stanley

 

[TomKane]

Well done for fixing it! You live and learn, huh?

 

[stanlyn]

Hi Tom,

I don't understand what is going on here with this problem. I don't consider it fixed, only found an undesirable way in which it worked. Actually it worked fine as just [DRI], but then I needed to create a version each for different sites, hence the [DRI_KY193] name that would not work. I assumed incorrectly that I could just change the db name and path info in the script and all would work. It did not. I've read several articles that states the name can be just about anything if enclosed in [square brackets]... Now, I'm no closer at understanding why the failing way fails...

Thanks, Stanley