C:\ -
OS
D:\ - SQL
Server files
With this non-standard
drive configuration my standard create database script falls flat on its face
as the Data and Log directories are hard coded (not brilliant I know but with consistency
it works a treat :) As my Standard
installation guide specifies the Data and Log directory paths I know that any
new servers will be consistent but the new script will also work for existing “legacy”
servers with non-standard drive configurations.
Below is
the modified script, It will create a database called dbLogging in the default data and log directories with a few other settings, which may or may not be what you require so get modding. Yes it does assume that the database xabcx123xdbxdefaultxfilexpathx321xcbax
does not exist, to be honest I don’t really care, if someone has a database with
that name then I would love to know why. If you do then you just need to do a
find and replace to something more unconventional like say dbAdmin or
dbSQLAdmin.
/*
-----------------------------------------------------------------
Create database with files in default the
directories
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.co.uk
-----------------------------------------------------------------
You may alter this code for your own
purposes.
You may republish altered code as long as
you give due credit.
You must obtain prior permission before
blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED
"AS IS"
-----------------------------------------------------------------
*/
-- set database
context
USE master;
GO
-- Create a temp
database
CREATE DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax;
-- Declare
variables
DECLARE
@DefaultDataFilePath VARCHAR(512)
DECLARE
@DefaultLogFilePath VARCHAR(512)
DECLARE
@DatabaseName VARCHAR(512)
DECLARE
@DataFileName VARCHAR(517)
DECLARE
@LogFileName VARCHAR(517)
DECLARE
@DataFileExtension VARCHAR(4)
DECLARE
@LogFileExtension VARCHAR(4)
DECLARE @SQL VARCHAR(4000)
/*
*** THIS IS ALL YOU NEED TO SPECIFY ***
*/
SET
@DatabaseName = 'dbLogging'
-- Set variables
SET
@DataFileName = @DatabaseName + '_Data'
SET
@LogFileName = @DatabaseName + '_Log'
SET
@DataFileExtension = '.mdf'
SET
@LogFileExtension = '.ldf'
-- Get the
default data path
SELECT
@DefaultDataFilePath =
( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.databases d
ON mf.database_id = d.database_id
WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 0);
-- Get the
default log path
SELECT
@DefaultLogFilePath =
( SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.databases d
ON mf.database_id = d.database_id
WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 1);
-- Drop the temp
database
IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' )
BEGIN
DROP DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax
END;
-- If the
database already exists print message tro client
IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] =
@DatabaseName)
BEGIN
-- Print message if database already
exists
PRINT 'Database
' + @DatabaseName + ' already exists on '
+ @@SERVERNAME
END;
-- Build up SQL
string to create database
IF NOT EXISTS(SELECT 1 FROM master.sys.databases WHERE
[name] = @DatabaseName)
BEGIN
SET @SQL = 'CREATE DATABASE ' + '[' + @DatabaseName + ']' +'
( NAME = ' +
'[' +
@DataFileName + ']'
+ ',
FILENAME = '''+
@DefaultDataFilePath + @DataFileName + @DataFileExtension +
''',
MAXSIZE = UNLIMITED,
FILEGROWTH = 10% )
LOG ON
( NAME = ' +
'[' +
@LogFileName + ']'
+ ',
FILENAME = '''+
@DefaultLogFilePath + @LogFileName + @LogFileExtension +
''',
SIZE = 1024MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10% )'
-- Create the
database
--Try Catch
block to create database
BEGIN TRY
EXEC ( @SQL )
PRINT @DatabaseName + ' has been created on '
+ @@SERVERNAME
END TRY
BEGIN CATCH
SELECT @DatabaseName message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id =
@@ERROR
AND
language_id = 1033 --British
English
END CATCH
END;
GO
Enjoy!
Chris
