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!
sath350163Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

techhealthCommented:
You're gonna need to query the default database locations.  See the link below for two methods (through checking tempdb, or using regread xp script)
http://blogs.technet.com/b/sqlman/archive/2009/07/20/tsql-script-determining-default-database-file-log-path.aspx
0
sath350163Author Commented:
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
 
0
techhealthCommented:
Yes if you don't provide filespec parameters, the db engine will just create it at the default location, which is the data folder at the instance installation location, and will always be on the correct physical drive.  However if you do want to specify parameters, you must then provide the file's physical path, because it's mandatory for any valid filespec syntax.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sath350163Author Commented:
Awesome.
Thanks for the quick reply!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.