SQL Server Create Database :: ERROR

aelbi1ox 于 12个月前发布在其他

关注(0)|答案(6)|浏览(187)

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.

sql-server

来源：https://stackoverflow.com/questions/1397550/create-database-error

6条答案

按热度按时间

cwtwac6a1#

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

赞(0）回复(0）举报 12个月前

vyu0f0g12#

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.

赞(0）回复(0）举报 12个月前

ldioqlga3#

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

赞(0）回复(0）举报 12个月前

5w9g7ksd4#

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.

赞(0）回复(0）举报 12个月前

yebdmbv45#

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.

赞(0）回复(0）举报 12个月前

rvpgvaaj6#

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.

赞(0）回复(0）举报 12个月前