How to Create 100's of Sample Databases by Script in SQL Server - SQL Server Tutorial

Scenario:

Let's say you just started with fresh Installation of SQL Server Instance. Now you want to create some sample database so you can use for different purposes. You don't want to run Create Database Script 100's of time and there is possibility you want to Data and Log location different than by default. 

Solution:

The below script can be used to create databases as many as you want. You have the options to provide the Data Directory and Log directory in which you want to create the database. You can choose the part of Database Name and then it will add the numbers to it. 

--Create Variables
Declare @DataFilePath VARCHAR(100)
Declare @LogFilePath VARCHAR(100)
Declare @SubPartDBName VARCHAR(100)
Declare @StartCnt int
Declare @MaxDBCnt int

--Set the Variable Values, @MaxDBCnt is Number of Databases you want to Create
SET @StartCnt=1
SET @MaxDBCnt=101

--Provide the Data File Path And Log File Path
SET @DataFilePath='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL01\MSSQL\DATA\'
SET @LogFilePath='C:\Program Files\Microsoft SQL Server\MSSQL12.SQL01\MSSQL\DATA\'
--Chose the First part of your DB name, Let's say TEST is chosen then Databae will be created Test1,Test2....Test100
SET @SubPartDBName='Test'


--Create Databases
While ( @startCnt<@MaxDBCnt)
BEGIN

Print CAst(@startCnt AS VARCHAR(100))
DECLARE @DBFullName VARCHAR(500) =@SubPartDBName+CAST(@StartCnt AS VARCHAR(10))
DECLARE @SQL NVARCHAR(MAX)
SET @SQL= 'CREATE DATABASE ['+@DBFullName+']

 ON 
( NAME = N'''+@DBFullName+''', FILENAME = N'''+@DataFilePath+@DBFullName+'.mdf'' ,

 SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'''+@DBFullName+'_log'', FILENAME = N'''+@LogFilePath+@DBFullName+'_log.ldf'' ,

 SIZE = 1024KB , FILEGROWTH = 10%)'
SET @startCnt=@startCnt+1
Print @SQL
Execute (@SQL)
END