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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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 ]
    [ FOR LOAD | FOR ATTACH ]
    < 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

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    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


















  • Thanks for your registration, follow us on our social networks to keep up-to-date