August 20, 2012 at 9:26 pm
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
August 21, 2012 at 12:36 am
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?
August 21, 2012 at 3:15 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply