-5

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

#Your SQL Server Instance Name
$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()
  • 1
    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
0

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)
    , @logInitialSize 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'
    , @logInitialSize= '512KB'
    , @logMaxSize    = 'UNLIMITED'
    , @logGrowthSize = '512KB'
  • I was thinking of creating a stored procedure instead of having a powershell script. – jason Jan 25 '16 at 16:37
  • Use the same approach only you are going to use dynamic SQL and the sp_executesql stored proceudure to execute the dynamic SQL. Powershell would be much easier for this type of automation, IMHO. – Steve Mangiameli Jan 25 '16 at 16:41
  • Added a stored procedure example, written and tested with 2008R2. Should be fine in more current versions. Again, this is just an example. There are a lot of options you could script out and get much more fancy than this. – Steve Mangiameli Jan 25 '16 at 17:04
  • Msg 8146, Level 16, State 2, Procedure usp_CreateMaintenanceDB, Line 0 Procedure usp_CreateMaintenanceDB has no parameters and arguments were supplied. I get the above error when I tried to test it on my box. I am new to SQL server please let me know if my questions are naive. – jason Jan 25 '16 at 17:25
  • Sounds like you didn't create the SP correctly, but hard to troubleshoot from that one line. I ran that exact code on my box without issue. Check object explorer and make sure the sp was created correctly. Make sure you didn't miss any punctuation anywhere in either execution or sp call – Steve Mangiameli Jan 25 '16 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.