Today's question comes from SPG:
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 or an ASP page. 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:
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
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
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
CREATE DATABASE statement actually has a number of parameters that you can set to customize
your database creation. A more involved example follows:
This example provides more detail on the creation of the database. We are naming both the data file and the
log file explicitly. The
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
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!