Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CREATE DATABASE using file in default path

I want to create an SQL script that creates a database. Right now, I have this:

CREATE DATABASE [Documents] ON  PRIMARY  ( NAME = N'Documents', FILENAME = N'Documents.mdf')  LOG ON  ( NAME = N'Documents_log', FILENAME = N'Documents_log.ldf')  COLLATE SQL_Latin1_General_CP1_CI_AS 

However, this generates the following error:

Msg 5105, Level 16, State 2, Line 2 A file activation error occurred. The physical file name 'Documents.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. Msg 1802, Level 16, State 1, Line 2 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 

I know the problem is that I did not specify fully qualified path for the filenames. But I want to be able to run this script regardless of the directory structure of the database server. Is there some way to use a default path?

like image 422
recursive Avatar asked Oct 28 '09 14:10

recursive


People also ask

What is the default data file created for a database?

The database has a primary data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. An ALTER DATABASE statement makes the user-defined filegroup the default.


2 Answers

Create the database 'Documents' and give file properties through an alter.

USE [master] GO  CREATE DATABASE [Documents] GO  ALTER DATABASE [Documents] MODIFY FILE ( NAME = N'Documents', SIZE = 512MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) GO  ALTER DATABASE [Documents] MODIFY FILE ( NAME = N'Documents_log', SIZE = 256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) GO 

This script is more portable and can be deployed in multiple servers without any modifications.

like image 114
Gayan Dasanayake Avatar answered Oct 20 '22 21:10

Gayan Dasanayake


You can create a database without specifying file details, like:

CREATE DATABASE Documents; 
like image 39
Damir Sudarevic Avatar answered Oct 20 '22 19:10

Damir Sudarevic