/*================================================================*/

/*================================================================*/

/* Before executing, modify this script as follows:               */

/*  1. change physname of each device to the desired location     */

/*  2. Change the size of each device if necessary                */

/*  3. Change the size of each database if necessary              */

/*  ... 							  */

/*  This script must be executed by sa from the machine on which  */

/*  SQL Server is executing 					  */

/*          		 					  */

/*  Rob Kraft 1998       		  	         	  */

/*================================================================*/

/*================================================================*/



declare @sql varchar(255)

declare @nextdev int

declare @counter int

declare @dbpath varchar(200)

declare @dbsuffix varchar(4)

declare @physname varchar(220)

declare @thisdb varchar(12)

declare @thislog varchar(15)

declare @devsize int

declare @logsize int

declare @dbsize tinyint

declare @dblogsize tinyint



set nocount on



/*================================================================*/

/*================================================================*/

/* this is all that needs to change for each database		  */

select @dbpath = 'd:\mssql\data\'

select @dbsuffix = '.DAT'

select @thisdb = 'YOURDB'

select @dbsize = 5

select @dblogsize = 2

/*================================================================*/

/*================================================================*/





/*================================================================*/

/*================================================================*/

/* This rest of this is totally generic - works for every db      */

/*================================================================*/

/*================================================================*/





/* this ugly command retrieves the next available device number   */

select @nextdev =  

max (convert(tinyint, substring(convert(binary(4), low),1,1))) + 1 from sysdevices

where convert(tinyint, substring(convert(binary(4), low),1,1)) < 125



/* Create Device for DATA */

select @devsize = @dbsize * 512  /* 5mb */

select @logsize = @dblogsize * 512  /* 2mb */



select @physname = @dbpath + @thisdb + @dbsuffix

DISK INIT NAME = @thisdb,

	  PHYSNAME = @physname,

	  VDEVNO = @nextdev,

	  SIZE = @devsize





/*================================================================*/

/* see if creation was successful */

select @counter = count(*) from sysdevices where name = @thisdb

if @counter = 1

    select '<<< Created device ' + @thisdb + ' >>>'

else

    select '<<< Failed to create device ' + @thisdb + ' >>>'





/*================================================================*/

/* Create Device for LOG */

select @nextdev = @nextdev + 1



select @physname = @dbpath + @thisdb + 'Log' + @dbsuffix

select @thislog = @thisdb + 'Log'

DISK INIT NAME = @thislog,

	  PHYSNAME = @physname,

	  VDEVNO = @nextdev,

	  SIZE = @logsize





/*================================================================*/

/* see if creation was successful */

select @counter = count(*) from sysdevices where name = @thislog

if @counter = 1

    select '<<< Created device ' + @thisLog + ' >>>'

else

    select '<<< Failed to create device ' + @thisLog + ' >>>'



/*================================================================*/

/* Create the Database */

select @sql = 'CREATE DATABASE ' + @thisdb + ' ON ' + @thisdb + ' = ' + 

		convert(varchar(3),@dbsize) + ' LOG ON ' + @thislog + ' = ' + convert(varchar(3),@dblogsize)

exec (@sql)





/*================================================================*/

/* see if creation was successful */

select @counter = count(*) from sysdatabases where name = @thisdb

if @counter = 1

    select '<<< Created database ' + @thisdb + ' >>>'

else

    select '<<< Failed to create database ' + @thisdb + ' >>>'

/*================================================================*/

/*================================================================*/