Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parmeters on Create Database

Posted on 2014-04-07
8
Medium Priority
?
240 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 30

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 30

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 30

Accepted Solution

by:
Olaf Doschke earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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