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.

Answer 1

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.

Answer 2

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

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%)

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.