Powered by Blogger.

Saturday, February 15, 2014

Create a database by command in sql server 2008

In this post we will discuss how to create database by using command in sql server 2008. Also you can check my previous posts on:

- Data files and Log files in sql server 2008

- Error functions in sql server

- Show hide div using jQuery in asp.net

To create database sql server provides create databse command that has the following syntax.

Create database <dbname>

When you create a database using the above syntax one datafile and one logfile are automatically created for the database in default location of sql server i.e, C:\program files\Microsoft SQL server\MSSQL 10.MSSQL SERVER\MSSQL\DATA

Sql server provides a stored procedure named sp_helpdb that takes database name as argument and returns details of that database like datafile and logfile created for it and memory allocated for those files.
Ex: sp_helpdb’DB1’

To create a database by specifying datafile and logfile specification manual,use the following syntax.
Create database <dbname>
On(<datafile specification>)
Logon(<logfile specification>)

create database DB
on (name=db_dat,filename='d:\db_dat.mdf',size=4,filegrowth=2,maxsize=unlimited)
logon(name=db_log,filename='d:\db_log.ldf',size=2,file growth=1,maxsize=300)

Creating a database in GUI:

To create a database in GUI below are the steps.
Within Object Explorer right click on the "databases" category and choose new database that opens "new database" dialog box.

Then with in the dialog box provide a database name which will automatically create one datafile and one logfile with default specifications, which you can modify.

After making required changes to the specification of the files click on "Ok" button to complete creating database.