Create Database :: ERROR

Question

Trying to create Database as follows:

    USE Master
    GO

    IF NOT EXISTS(SELECT [Name] FROM sys.databases WHERE [name] = 'QAudit')
CREATE DATABASE [QAudit] ON  PRIMARY 
( NAME = N'QAuditData', FILENAME = N'<filePath,nvarchar(300),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\>QAuditData.mdf' , SIZE = 921600KB , FILEGROWTH = 10%)
 LOG ON 
( NAME = N'QAuditLog', FILENAME = N'<filePath,nvarchar(300),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\>QAuditLog.ldf' , SIZE = 512000KB , FILEGROWTH = 10%)

    GO

Getting following error:

Msg 5105, Level 16, State 2, Line 3 A file activation error occurred. The physical file name 'QmastorAuditData.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. Msg 1802, Level 16, State 1, Line 3 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Any clues please.

Tenorite

There's no QmastorAuditData.mdf in the command, so the error was raised by a different command. In the command shown it's clear that <filePath,nvarchar(300), doesn't belong there. – Carricarriage

Answer 1

Should FILENAME be the fully qualified path.. I know I use the full folder path when creating databases.

USE [master]
GO

CREATE DATABASE [HereTis] ON  PRIMARY 
( 
    NAME = N'HereTis', 
    FILENAME = N'C:\DATA\HereTis.mdf' ,         --local data path
    SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB 
)
LOG ON 
( 
    NAME = N'HereTis_log', 
    FILENAME = N'C:\DATA\HereTis.ldf' ,         --local data path
    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%
)
GO

Kamkama

Answer 2

You must add the path to the name .

CREATE DATABASE [QAudit] ON PRIMARY ( NAME = N'QAuditData', FILENAME = N'c:\db_directory\QAuditData.mdf' , SIZE = 921600KB , FILEGROWTH = 10%) LOG ON ( NAME = N'QAuditLog', FILENAME = N'c:\db_directory\QAuditLog.ldf' , SIZE = 512000KB , FILEGROWTH = 10%)

bye.

Watterson

Answer 3

There seems to be a problem with the path to the MDF & LDF files.

CREATE DATABASE [QAudit] ON  PRIMARY 
(NAME = N'QAudit_data', FILENAME = N'E:\QAudit_data.MDF', SIZE = 614400KB, MAXSIZE = 921600KB, FILEGROWTH = 10%)
LOG ON 
(NAME = N'QAudit_log', FILENAME = N'E:\QAudit_log.LDF', SIZE = 102400KB, MAXSIZE = 512000KB, FILEGROWTH = 10%)

Selassie

Answer 4

I had this same problem earlier today; I think the problem is you are trying to script a copy of the database, but it can't find the log files paths to create on the backend.

My solution was to create the database from the management studio GUI, and then right click on the original DB, go to tasks, generate scripts, script all objects, finish. I then copied the generated script into a 'new query' on the new db.

Holothurian

Answer 5

When you are creating a new database through a script its best if you could avoid hard coded file paths. Instead you could do like this:

First create the database and then alter file properties through an alter.

USE [master]
GO

CREATE DATABASE [DBName]
GO

ALTER DATABASE [DBName] MODIFY FILE
( NAME = N'DBName' , SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO

ALTER DATABASE [DBName] MODIFY FILE
( NAME = N'DBName_log' , SIZE = 256MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO

This script is more portable and can be deployed in multiple servers without any modifications.

Jeremiahjeremias

Answer 6

If you are attaching DB files , Then you need also to provide the full rights to .mdf files and log files.

Then try to attach the DB files. This error will be removed and DB created successfully.

Consummate

Answer 7

I ran into this when creating a new database via the SSMS interface. It turns out that the directory I had configured for databases no longer existed. To change it: Right click the server node --> Properties --> Select "Database Settings" --> Change value under "Database default locations".

Trixi