Using VBScript to make a new SQL Server Database

By Bill Graziano on 2 August 2000 | 0 Comments | 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 :)

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Using Dynamic SQL in Stored Procedures (7 March 2011)

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

Aggregating Correlated Sub-Queries (23 October 2007)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Returning a week number for any given date and starting fiscal month (2 May 2007)

Other Recent Forum Posts

Msg 4104 (40 Replies)

incremented updated (4 Replies)

How to represent SQL procedure via JDBC and SQLJ (0 Replies)

article gdbet333 (0 Replies)

2014-07-27 11-05-19????? (0 Replies)

AlwaysOn FCI + Transaction Log Shipping (3 Replies)

mULTI LANGUAGE SUPPORT IN SQL SERVER (4 Replies)

MySQL root Pwd (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -