The SQL Guru Answers your Questions...
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 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 statement 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!
Answered by SQLGuru graz - SQLTeam.com
