Sql-server – Script to create multiple databases

sql servert-sql

I need to create 90 databases. Something like this. Do you have any idea?

My script:

declare @db_name nvarchar(100)
set @db_name = 'name';

CREATE DATABASE @db_name
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'@db_name', FILENAME = N'D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\@db_name.mdf' , SIZE = 10240KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'@db_name_log', FILENAME = N'L:\MSSQL11.MSSQLSERVER\MSSQL\Log\@db_name_log.ldf' , SIZE = 512KB , FILEGROWTH = 256KB )
GO
ALTER DATABASE @db_name SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE @db_name SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE @db_name SET ANSI_NULLS OFF 
GO
ALTER DATABASE @db_name SET ANSI_PADDING OFF 
GO
ALTER DATABASE @db_name SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE @db_name SET ARITHABORT OFF 
GO
ALTER DATABASE @db_name SET AUTO_CLOSE OFF 
GO
ALTER DATABASE @db_name SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE @db_name SET AUTO_SHRINK OFF 
GO
ALTER DATABASE @db_name SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE @db_name SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE @db_name SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE @db_name SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE @db_name SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE @db_name SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE @db_name SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE @db_name SET  DISABLE_BROKER 
GO
ALTER DATABASE @db_name SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE @db_name SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE @db_name SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE @db_name SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE @db_name SET  READ_WRITE 
GO
ALTER DATABASE @db_name SET RECOVERY FULL 
GO
ALTER DATABASE @db_name SET  MULTI_USER 
GO
ALTER DATABASE @db_name SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE @db_name SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE @db_name
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE @db_name MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Best Answer

No cursors, No need to use a template, multiple DBs created provided you have the dbnames in a table

DECLARE 
@query as varchar(max)

CREATE TABLE #db_names(dbname varchar(250))

INSERT INTO #db_names values('test')
INSERT INTO #db_names values('second')

SET @query = ''

SELECT @query = @query + 'CREATE DATABASE [' + dbname + ']  
        CONTAINMENT = NONE
        ON  PRIMARY 
            ( NAME = N''' + dbname + ''', 
              FILENAME = N''D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\' + dbname +'.mdf'' , 
              SIZE = 10240KB , 
              FILEGROWTH = 1024KB )
        LOG ON 
            ( NAME = N''' + dbname + '_log''' +', 
              FILENAME = N''L:\MSSQL11.MSSQLSERVER\MSSQL\Log\' + dbname + '_log' +'.ldf'' , 
              SIZE = 512KB , 
              FILEGROWTH = 256KB )
GO
ALTER DATABASE '+ dbname + ' SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE '+ dbname + ' SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE '+ dbname + ' SET ANSI_NULLS OFF 
GO
ALTER DATABASE '+ dbname + ' SET ANSI_PADDING OFF 
GO
ALTER DATABASE '+ dbname + ' SET ANSI_WARNINGS OFF 

GO
ALTER DATABASE '+ dbname + ' SET ARITHABORT OFF 
GO
ALTER DATABASE '+ dbname + ' SET AUTO_CLOSE OFF 
GO
ALTER DATABASE '+ dbname + ' SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE '+ dbname + ' SET AUTO_SHRINK OFF 
GO
ALTER DATABASE '+ dbname + ' SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE '+ dbname + ' SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE '+ dbname + ' SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE '+ dbname + ' SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE '+ dbname + ' SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE '+ dbname + ' SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE '+ dbname + ' SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE '+ dbname + ' SET  DISABLE_BROKER 
GO
ALTER DATABASE '+ dbname + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE '+ dbname + ' SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE '+ dbname + ' SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE '+ dbname + ' SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE '+ dbname + ' SET  READ_WRITE 
GO
ALTER DATABASE '+ dbname + ' SET RECOVERY FULL 
GO
ALTER DATABASE '+ dbname + ' SET  MULTI_USER 
GO
ALTER DATABASE '+ dbname + ' SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE '+ dbname + ' SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE '+ QUOTENAME(dbname) + '
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N''PRIMARY'') 
    ALTER DATABASE ' + dbname + ' MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
' FROM #db_names

select @query
GO

--exec sp_executesql @query

comment select @query and uncomment exec sp_executesql @query to create the dbs

Related Question