# can anyone help me in generating a automated script to create a database

I need help in generating a automated script for database where I can give parameters for database name, size etc.

I need automated script to create a database where in I can give parameters for instance name, database name, size of the database.

So far I have created a powershell script to create databases but I want to bypass that create a stored procedure where I can insert parameters.

My powershell script looks like this

#Import SQL Server Module called SQLPS
Import-Module SQLPS -DisableNameChecking

$Inst = "YourInstanceName"$Srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Inst #database PSDB with default settings #by assuming that this database does not yet exist in current instance$DBName = "databasename"
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($Srvr, $DBName)$db.Create()

• What have you tried so fat? What do you want to create precisely and why? If you don't give much details your question will most likely be tagged as too broad or unclear what you are asking or off topic and will be closed. How do I ask a good question? – Julien Vavasseur Jan 25 '16 at 10:15
• eidted the question – jason Jan 25 '16 at 15:37
• The procedure will be stored on one instance and you will connect to this instance. You can then execute it from this instance and create a DB on this same instance only. If you want to create DB on any instance, Powershell is probably a better option. – Julien Vavasseur Jan 25 '16 at 15:58
• it will be part of maintenance database which will be created by default when we build servers so creating a stored procedure where I can input parameters like database name, size of the database I think that would do. – jason Jan 25 '16 at 16:19

The easiest way to do this is just to script out a database and then parameterize it the way you want. Use that to dump into a query and then just pass in the query. Here is a very simple example...

Powershell Method

[string]$sqlInstance [string]$dbName
[string]$dbInitialSize [string]$dbMaxSize
[string]$dbGrowthSize [string]$logInitialSize
[string]$logMaxSize [stinrg]$logGrowthSize

$sqlQuery= "IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'$dbName')
BEGIN
CREATE DATABASE [$dbName] ON PRIMARY ( NAME = N'$dbName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2K8R2\MSSQL\DATA\$dbName.mdf' , SIZE =$dbInitialSize , MAXSIZE = $dbMaxSize, FILEGROWTH =$dbFileGrowth )
LOG ON
( NAME = N'$dbName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2K8R2\MSSQL\DATA\$dbName_log.ldf' , SIZE = $logInitialSize , MAXSIZE =$logMaxSize , FILEGROWTH = $logFileGrowth) COLLATE SQL_Latin1_General_CP1_CI_AS END" Invoke-Sqlcmd -Query$sqlQuery -ServerInstance \$sqlInstance


Stored Procedure Method

USE master
GO

IF OBJECT_ID('usp_CreateMaintenanceDB','P') IS NULL
EXEC sp_executesql N'CREATE PROC dbo.usp_CreateMaintenanceDB AS SELECT ''STUB'' STUB'
GO

ALTER PROC dbo.usp_CreateMaintenanceDB
(
@dbName VARCHAR(255)
, @mdfPath VARCHAR(259)
, @logPath VARCHAR(259)
, @dbInitialSize VARCHAR(25)
, @dbMaxSize VARCHAR(25)
, @dbGrowthSize VARCHAR(25)
, @logMaxSize VARCHAR(25)
, @logGrowthSize VARCHAR(25)
)
AS

DECLARE @sqlCmd NVARCHAR(MAX)

SET @sqlCmd=N'
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'''+@dbName+''')
BEGIN
CREATE DATABASE ['+@dbName+'] ON  PRIMARY
( NAME = N'''+@dbName+''', FILENAME = N'''+@mdfPath+@dbName+'.mdf'' , SIZE = '+@dbInitialSize+' , MAXSIZE = '+@dbMaxSize+', FILEGROWTH = '+@dbGrowthSize+' )
LOG ON
( NAME = N'''+@dbName+'_log'', FILENAME = N'''+@logPath+@dbName+'_log.ldf'' , SIZE = '+@logInitialSize+' , MAXSIZE = '+@logMaxSize+', FILEGROWTH = '+@logGrowthSize+' )
COLLATE SQL_Latin1_General_CP1_CI_AS
END'

EXEC sp_executesql @sqlCmd


SAMPLE CALL

exec dbo.usp_CreateMaintenanceDB
@dbName        = 'TEST2'
, @mdfPath       = 'c:\mssqlserver\sqlversion\data\'
, @logPath       = 'c:\mssqlserver\sqlversion\log\'
, @dbInitialSize = '2048MB'
, @dbMaxSize     = 'UNLIMITED'
, @dbGrowthSize  = '1024KB'
`