CODEWITHSUNDEEP
sqlsql-servert-sql
Sreedhar
Sreedhar

Reputation: 30025

Create Database :: ERROR

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.

Upvotes: 5

Views: 25774

Answers (7)

MikeH
MikeH

Reputation: 4395

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".

Upvotes: 0

user2361751
user2361751

Reputation: 21

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.

Upvotes: 0

Gayan Dasanayake
Gayan Dasanayake

Reputation: 2021

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.

Upvotes: 0

javamonkey79
javamonkey79

Reputation: 17765

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.

Upvotes: 0

Kirtan
Kirtan

Reputation: 21695

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

Upvotes: 0

misteraidan
misteraidan

Reputation: 2003

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

Upvotes: 9

RRUZ
RRUZ

Reputation: 136391

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.

Upvotes: 0

Related Questions