Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 22, 2005

Creating databases in SQL Server 2000 - Page 2

By Steven Warren

Creating your first database

With SQL Server 2000, you can create databases using one of these three methods:

  • The Create Database Wizard
  • The SQL Server Enterprise Manager console
  • Transact SQL (T-SQL)

About the Create Database Wizard

If you are new to SQL Server, the best way to create a new database is to use the Create Database Wizard. Once you are comfortable with using SQL Server Enterprise Manager and/or T-SQL, you can move beyond this wizard to more advanced ways of creating databases.

To use the Create Database Wizard:

  1. Open the SQL Server Enterprise Manager from the Start menu.
  2. Click on the SQL Server instance where you want to create your new database and expand it (using the plus sign to the left).
  3. From the Tools menu, choose Wizards.
  4. Expand Database and highlight Create Database Wizard (Figure A) and click OK.

Figure A

  • When the "Welcome to the Create Database Wizard" screen appears, click Next, type a database name, and browse to the location where you want to place your database file and transaction logs (Figure B).

    Figure B

    1. Click Next and enter the initial size of your primary database (.mdf) file (Figure C). You can add secondary files as well; they will be given an .ndf extension.

    Figure C

    1. Click Next and define how you want your database growth to occur (Figure D).

    Figure D

    1. Click Next and enter the initial size of your transaction logs.
    2. Click Next and define your transaction log database growth.
    3. Click Next, review your database settings, and click Finish.

    Manually create a database in Enterprise Manager

    To manually create a database using SQL Server Enterprise Manager:

    1. Open Enterprise Manager and select and expand your SQL Server instance.
    2. Right-click on Databases and choose New Database.
    3. In the General tab, type a database name (Figure E).

    Figure E

    1. In the Data Files tab, you can configure the auto growth size, database log location, and the secondary log location (Figure F).

    Figure F

    1. In the Transaction Log tab, you can configure the auto growth size, database log location, and secondary location.
    2. Click OK, and your database is created.

    Creating a database using T-SQL

    In addition to the two methods mentioned above, you can use T-SQL to create a database. The syntax is as follows:

    CREATE DATABASE database_name
    [ ON
        [ < filespec > [ ,...n ] ]
        [ , < filegroup > [ ,...n ] ]
    [ LOG ON { < filespec > [ ,...n ] } ]
    [ COLLATE collation_name ]
    < filespec > ::=
    [ PRIMARY ]
    ( [ NAME =logical_file_name, ]
        FILENAME ='os_file_name'
        [ , SIZE =size ]
        [ , MAXSIZE = { max_size | UNLIMITED } ]
        [ , FILEGROWTH =growth_increment ] ) [ ,...n ]
    < filegroup > ::=
    FILEGROUP filegroup_name < filespec > [ ,...n ]

    Let's say, for example, that we wanted to create a database that contains a primary data file called DDAT and one transaction log called DDAT_LOG. We would create this database using the T-SQL syntax shown in Figure G.

    Figure G

    Now, we will take it one step further and add a primary file, two secondary files, and two transaction logs (Figure H),

    Figure H

    In this article, we have shown you how to configure databases in SQL Server using Enterprise Manager and T-SQL. This is a good beginning to working with SQL Server 2000 databases and you can build your skill set from here.

    » See All Articles by Columnist Steven S. Warren

    MS SQL Archives

    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM