Solved

Parmeters on Create Database

Posted on 2014-04-07
8
237 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
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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