Solved

Parmeters on Create Database

Posted on 2014-04-07
8
234 Views
Last Modified: 2014-04-08
I would like to use parameters for the creation of databases based upon server name.  I am upto the point where I am using @@SERVERNAME to determine which server the install is on.  I would like to have variables for the paths.  I am a little uncertain how best to do this.  I suspect that using just + to concatenate would not be best or possibly correct.

Any help in doing this would be gratefully received.

DECLARE @DataPath nvarchar(255), @LogPath nvarchar(255)

SET @DataPath = '$(DataPath)' 	--'E:\Databases\'
SET @LogPath = '$(LogPath)' 	--'F:\Logs\'


CREATE DATABASE [PASTIS] ON  PRIMARY 
( NAME = N'PASTIS', FILENAME = N'D:\SQL_Data\MSSQL10.MSSQLSERVER\MSSQL\DATA\HASTATI.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'PASTIS_log', FILENAME = N'D:\SQL_Data\MSSQL10.MSSQLSERVER\MSSQL\DATA\HASTATI_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Open in new window

0
Comment
Question by:Alyanto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39982660
Which SQL Server version? (EE still has no SQL2012 section, so it's still a guess whther you are at sql2008, 2008r2 or 2012 or any other version).

In sql2012 you can determine standard path via

SELECT
SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') as [Default_data_path], SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') as [Default_log_path];

What else are you looking for? A T-SQL equivalent to .NET Path.Combine, perhaps? Anything in that direction? I'd say string concatenation is good to go to add a specific filename to the default data and log paths, unless you have even other directories dedicated to sql server database files.

Bye, Olaf.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39982717
something to note: if you don't specify a path, but just the file name, it will be created in the default path anyhow ...
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39982761
True, and if you specify a logical database name only, the files will be named dbname.mdf and dbname_log.ldf automatic, too.

I think you rather want more control about the file names, yet still verify paths and file names passed in for conflicts.

AFAIK T-SQL doesn't offer much about file system, rather limited functions, eg to determine the physical location of a filestream object. So either you verify paths and filenames elsewhere or handle errors and exceptions of the database creation.

That means:

BEGIN TRY
     CREATE DATABASE
END TRY
BEGIN CATCH
     -- Exception handling for cases like: DBname already exists, File already exists,...
END CATCH

Open in new window


http://technet.microsoft.com/en-us/library/ms175976.aspx

Bye, Olaf.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:Alyanto
ID: 39983092
2008 R2
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39983112
I am scripting an installer for the implementation team.

The implementation process is DEV which I have rights to do as I like.  UAT which I have rights to the install log table and LIVE which I have no rights to.  The implementations team need a simple click an deploy batch file and I am scripting so I can present parameters to the SQL.  I would like the installer really to be able to find out what server it is being deployed to an amend paths etc as needed from that information
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 39983185
Well, wouldn't a setup t-sql script you tested on DEV simply also work unchanged at UAT and LIVE. All your installer would need to do is connect to the LIVE Server instance instead of DEV or UAT. Wouldn't that be the only difference?

What programming language are you using? And what installer? In VS you can create DacPacs to apply to a server, see http://sqlproj.com/index.php/2011/11/sql-server-data-tools-ssdt/

Bye, Olaf.
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39983408
The team here are using and have been for some years a batch file that iterates through as folders SQL files.  The order is determined by a 3 digit number.  I might not have chosen this method but that is what is being used none the less.
0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39986129
Olaf's solution is not using the same method as exists however having read it and considered whether it would be acceptable to the existing team I have proposed that we trial it.

/Aly
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 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