SQL Server Database Existence – Troubleshooting Database Creation Issues

sql server

I am having problems running a SQL script to CREATE a database on SQL Server 2014.

My script fails with the following error, however I am not sure how to resolve the issue as I am a beginner with SQL Server.

CREATE DATABASE [BIDS] 
  CONTAINMENT = NONE ON PRIMARY 
  ( NAME = N'BIDS', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\BIDS.mdf' , 
    SIZE = 4096KB , 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 1024KB 
  ) 
  LOG ON 
  ( NAME = N'BIDS_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\BIDS_log.ldf' , 
    SIZE
    ---

What should I do to resolve this error?

Msg 1803, Level 16, State 1, Line 4
The CREATE DATABASE statement failed. The primary file must be at least 5 MB to accommodate a copy of the model database.

Msg 5011, Level 14, State 5, Line 11
User does not have permission to alter database 'BIDS', the database does not exist, or the database is not in a state that allows access checks.

Best Answer

Sounds like someone changed the defaults that exist in the model database on the server (512 is usually the default from what I've seen--not 4096). Since this is for a deployment on possibly any SQL Server (where model specifications could well be different) just remove the SIZE = 4096KB in both the mdf and ldf sections--to avoid this issue altogether. Each SQL Server will create the database based on specifications of the model database on SQL Server.

Example:

CREATE DATABASE [BIDS] CONTAINMENT = NONE 
ON PRIMARY ( NAME = N'BIDS', FILENAME = N'C:\temp\BIDS.mdf' ,  
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
LOG ON ( NAME = N'BIDS_log', FILENAME = N'C:\temp\BIDS_log.ldf' ,  
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
Related Question