Solved

Parmeters on Create Database

Posted on 2014-04-07
8
228 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
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 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