Script to create multiple databases

Question

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

• Anti-weakpasswords: Whoa; a good chunk of your SET statements are directly contrary to current MS recommendations; ANSI_PADDING and QUOTED_IDENTIFIER are is but two of many examples, some (like these) of which can screw up attempts to use indexed views, computed columns, etc.

Answer 1

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

Answer 2

This piece of SQL can be used as a template:

DECLARE @sql nvarchar(max);
DECLARE @query nvarchar(max);
DECLARE @params nvarchar(500);
SET @params = N'@SQL nvarchar(max), @db_name sysname';
SET @sql= N'SET @SQL=REPLACE(@SQL, ''db_name'', @db_name); print @sql;'
--SET @sql = N'SET @SQL=REPLACE(@SQL, ''db_name'', @db_name); EXEC sp_executesql @SQL;';
SET @query = N'
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
'

• @sql is the piece of SQL that will be executed once for each db
• @params is the parameter used by @sql
• @query is a string with the piece of SQL use to create a new DB

While playing with @query, be really careful with single and double quotes.

First SET @sql... only prints the query. The second one --SET @sql... will execute it if uncommented.

Then this template can be followed by one more more of the below lines and the whole batch can be executed:

-- template
--
...
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'xxx';
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'yyy';
EXEC sp_executesql @sql, @params, @query = @query, @db_name = 'zzz';
...

If db_names are already in a table, this query will output all the EXEC lines:

SELECT N'EXEC sp_executesql @sql, @params, @query = @query, @db_name = '''+col_db_name+''';;' 
FROM db_list;

You can then copy some or all of them after the template and execute the whole batch.

Or without a table, this can simply be used:

SELECT N'EXEC sp_executesql @sql, @params, @query = @query, @db_name = '''+col_db_name+''';' 
FROM (values
 ('xxx')
 , ('yyy')
 , ('zzz')
 , (...)
 , ...
) as dblist(col_db_name);

Answer 3

Create a temp table containing all your database names, loop through this with a cursor.

declare @dbname nvarchar(200);
create table #dbs (name nvarchar(200));
insert into #dbs (name) VALUES ('myfirstdb')
insert into #dbs (name) VALUES ('myseconddb')
declare dbcursor cursor for select name from #dbs
open dbcursor
fetch next from dbcursor into @dbname
while @@FETCH_STATUS = 0
begin
 print('...create database '+@dbname+'...')
 fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor

credit: got the idea from this site: https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

• til_b: I am looking for a better way to express "while rows are returned" without having to specify the fetch next from ... twice. Any comments on that?
• Mark Sinkinson: Isn't that just how cursors work? There are always 2 FETCH statements
• til_b: Thats...suboptimal. I hate it, especially if the fetch statements get long. It's ugly. whine,groan

Answer 4

I think the best way is to create a table with dbnames and change server settings to point to new file location,if it is the desired location..I believe most of settings you have are same as msdb and are as per best practices.,if not change them to your new settings only till this script gets executed. Once you have them in place ,then its simple as below...

//place to change settings..

enter image description here

//code taken from tilb
create table #dbs (name nvarchar(200));
insert into #dbs (name) VALUES ('myfirstdb')
insert into #dbs (name) VALUES ('myseconddb')

Then a simple select will generate the scripts you need ,which is the best way..

select 'create database '+name from #dbs

• til_b: hint: use alt+PrintScreen to capture only the active window.