Can't create a database in SQL Server 2012

Question

I have recently installed SQL Server 2012 on my machine. When I try to create a database in SSMS by right clicking on Databases and selecting New Database, it prompts me for various items in order to create the database. After entering the name of the database and clicking OK, I get an exception:

"Create failed for Database 'aaaa'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A file activation error occurred. The physical file name 'aaaa.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5105)"

It seems the problem is only with the wizard because when I execute Create Database query it successfully creates the database.

That KB seems applied on SQL Server 7 and 2000.. did you try to script the snippet using the script button instead of pressing "OK" button? How is the CREATE DATABASE query written? — Alessandro Alpi
– Alessandro Alpi, Commented Jul 24, 2014 at 13:04

Wasim · Accepted Answer · 2014-07-25 06:24:00Z

4

I figured it out that when Database is created from wizard, a file path is to be provided in Path column. If it is blank by default then it means there is no path specified in Database settings.

In Object Explorer, right-click a server and click Properties. In the left panel, click the Database settings page. In Database default locations, view the current default locations for new data files and new log files. To change a default location, enter a new default path name in the Data or Log field, or click the browse button to find and select a path name.

We can change the file path while creating Database.

answered Jul 25, 2014 at 6:24

Wasim

711 gold badge1 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yuezheng.hu · Accepted Answer · 2014-07-24 09:41:37Z

1

The actual database file permissions were set to read_only,please try unchecked the read_only checkbox on the file permissions.

answered Jul 24, 2014 at 9:41

yuezheng.hu

212 bronze badges

Comments

Paulo Fernando · Accepted Answer · 2015-02-16 14:18:39Z

1

I also had this problem and I can in this link where I've created any string values (DefaultData and DefaultLog) in regedit in this path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer. In this "string values" you must put the path where your SQL data and log must stay.

answered Feb 16, 2015 at 14:18

Paulo Fernando

1877 bronze badges

Comments

Alb · Accepted Answer · 2020-10-28 15:39:34Z

As you realized there is a Path column in the grid view for each file (.mdf and .ldf).

This is set to <default>.

At the first usage of the server this default path may not be set, so the full-path of the new database cannot be computed.

Solution:

Based on this article you can set it via the interface of SSMS. You just need to:

right click on your server name (e.g.: (LocalDB)\v11.0),
select Properties,
select Database Settings,
Fill up all 3 entry of Database default locations with valid directory paths. (Defining top 2 paths: Data and Log is enough to create databases.)

This will create the right registry entries for you, without touching regedit.exe.

What path to choose?

Either the location that is proposed along installation:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data

Or the location of system databases:

C:\Users\[UserName]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0

Or better choices for non-system databases are:

C:\Users\[UserName]\My Documents\Databases

C:\Users\[UserName]\My Documents\SQL Server Management Studio\Databases

Note 1: [UserName] can be "Public" to make it common data (please correct me if this causes multiple copy of the database, but I think it won't).

Note 2: I don't know whether latter is deleted along uninstallation of SSMS.

Collectives™ on Stack Overflow

Can't create a database in SQL Server 2012

4 Answers 4

Comments

Comments

Comments

Solution:

What path to choose?

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Solution:

What path to choose?

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related