Parmeters on Create Database

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

LVL 1
AlyantoAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Olaf DoschkeSoftware DeveloperCommented:
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
 
AlyantoAuthor Commented:
2008 R2
0
 
AlyantoAuthor Commented:
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
 
AlyantoAuthor Commented:
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
 
AlyantoAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.