Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-05
4
Medium Priority
?
231 Views
Last Modified: 2013-11-05
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!
0
Comment
Question by:sath350163
  • 2
  • 2
4 Comments
 
LVL 11

Assisted Solution

by:techhealth
techhealth earned 500 total points
ID: 36922171
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
 

Author Comment

by:sath350163
ID: 36926456
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
 
LVL 11

Accepted Solution

by:
techhealth earned 500 total points
ID: 36926810
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
 

Author Closing Comment

by:sath350163
ID: 36927361
Awesome.
Thanks for the quick reply!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question