CODEWITHSUNDEEP
c#sqlsql-serverdatabase
Wasim
Wasim

Reputation: 71

Can't create a database in SQL Server 2012

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.

Upvotes: 3

Views: 11077

Answers (4)

Alb
Alb

Reputation: 529

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:

  1. right click on your server name (e.g.: (LocalDB)\v11.0),
  2. select Properties,
  3. select Database Settings,
  4. 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.

Upvotes: 0

Paulo Fernando
Paulo Fernando

Reputation: 187

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.

Upvotes: 1

Wasim
Wasim

Reputation: 71

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.

Upvotes: 4

yuezheng.hu
yuezheng.hu

Reputation: 21

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

Upvotes: 1

Related Questions