Link to home
Create AccountLog in
Avatar of sath350163
sath350163

asked on

Creating database (data and Log file) in SQL SERVER 2008 installed location

I looking for a sample create database script that would create the database in same exact drive in which sql server is installed.

Example:
Say Person A has SQL SERVER 2008 installed in C:\
Person B has has SQL SERVER 2008 installed in D:\
and
Person C has SQL SERVER 2008 installed in E:\

Currently the create database script that I generated from SSMS has the path explicitly specified for both Data and Log file.

Here is a portion from the generated create database script:
BEGIN
CREATE DATABASE [test] ON  PRIMARY 
( NAME = N'TestInstance', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TestInstance\MSSQL\DATA\test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestInstance_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TestInstance\MSSQL\DATA\test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO

Open in new window

How should I change my script, so that one single create database script could be used to create database in appropriate location in different PCs.

Thanks!
SOLUTION
Avatar of techhealth
techhealth
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of sath350163
sath350163

ASKER

Thanks for the link!

I was looking at the first method given the link, and it basically
- creates a database in the current instance
- gets the data and log file location of the database
- stores the data and log file location in a variable.

So if I specify as below, it looks like it would automatically pick up the appropriate location for data and log file (i.e., sql server installed location), even if the sql server was installed in E:\.
Can you please confirm?

CREATE DATABASE test;

Open in new window


In my case, I need to also specify the Size, Maxsize, and Filegrowth, as a part of the create database statement. So I cannot use the plain "Create database test" syntax.
I tried to take out the "FileName" alone from Create database script (as shown below), but looks like "FileName" must be specified, so which means that I need the correct path for data and log file.

For eg, like the one shown below,
BEGIN
CREATE DATABASE [test] ON  PRIMARY 
( NAME = N'TestInstance', SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestInstance_log', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO 

Open in new window




So should I first do like what Method 1 in the link does and use that data and log file path to create the actual database?
BEGIN
CREATE DATABASE [test] ON  PRIMARY 
( NAME = N'TestInstance', FILENAME = <@Default_Data_Path from Metod 1> , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestInstance_log', FILENAME = <@Default_Log_Path from method 1>, SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO 

Open in new window


Just want to confirm that my understanding here is correct and I'm doing the right thing?

Thanks
 
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Awesome.
Thanks for the quick reply!