Using VBScript to make a new SQL Server Database

By Bill Graziano on 2 August 2000 | Tags: Queries

---

SPG writes "My question is, Given that I can automate user creation with ADSI, table creation with SQL commands via ADO, and basic template web page creation with the FileSystemObject... Can I make a whole new database in SQL Server to complete the process? I've got a wide base of unrelated users, so -- even though they use similar web applications -- I'm keeping their data in separate databases. Being able to finish off new user creation automation would be faboo."

I'll give you the Transact-SQL commands to create a database and you can put them into a VBScript. It shouldn't be hard. You can create a database using the CREATE DATABASE command. For example, you might want to create a database for user2. The syntax would be:

CREATE DATABASE USER2

This is the simplest form of the create database. This actually takes the Model database and makes a copy of it naming it USER2. Therefore, any objects you create in model will appear in any new database you create. Also, any settings in model will be inherited by each new database. If you are creating multiple databases on the fly (like an ISP might) I'd recommend tuning the settings in Model to be just what you want. Especially the SELECT INTO/BULK COPY option and the file growth attributes.

In order to create a database, you must be a memeber of the sysadmin or dbcreator server role.

The CREATE DATABASE actually has a number of parameters that you can set to customize your database creation. A more involved example follows:

CREATE DATABASE USER8
ON
(NAME = 'User8_dat',
FILENAME = 'c:\mssql7\data\User8dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
(NAME = 'User8_log',
FILENAME = 'c:\mssql7\data\User8log.ldf',
SIZE = 2MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

This example provides more detail on the creation of the database. We are naming both the data file and the log file explicitly. The NAME and FILENAME allow us to assign a logical and physical file name to both the data file (ON) and log file (LOG ON). The SIZE allows us to set the initial size for the data file and log file. We use the MAXSIZE to specificy the maximum size of the file and FILEGROWTH to set the increments by which the database will grow.

All numbers will default to megabytes (MB) unless we say otherwise. You can see in the LOG ON clause I specified MB. You could also ues KB if you wanted a small database. This would probably only be useful when SQL Server 2000 comes out for the Pocket PC.

You can get even more complicated when you create databases but this is enough for now. Check SQL Server Books Online for details. I hope this is a faboo as you were expecting :)