Creating Database in SQL Server 2008

Database can be explained as a just single file, which is used to store data properly and which gives us facility to retrieve data properly. And a system that manages that database is called Database Management System a.k.a. DBMS, Oracle, SQL Server, DB2, MySQL are few to name. Each DBMS presents data differently, may have different look or feature but in essence they are all same. I mainly work on SQL Server, MySQL and Oracle… out of which I felt that MySQL has been improved lot and main advantage is its open source and free. And advantage of SQL Server is very user friendly, great user community and being MSFT product it can be easily integrated with .NET (of course sometimes I only think of my self smile_zipit ) … and Oracle is an industry giant which requires little more effort to learn but perhaps the most stable one.

Cutting this babbling short, I am posting here basic commands to create database using SQL Server. I think its better to work on CLI sometimes because it gives you flexibility, think of a case where you need to create database more than once where you need to setup lots options … believe me you will get crazy if I tell you to do it for 5 times and yet there are chances that you will miss something !!! Even some DBA may have wish to remove SSMS (GUI for SQL Server) and just let have CLI to access database … no body will even try to port around smile_teeth …

Finally the code …

-----------------------------------------------------
/* Creating database with Default Options */
-----------------------------------------------------

create database test
go
select * from sys.master_files where name='test'
go
drop database test
go

-----------------------------------------------------
/* Creating database with User Defined Options */
-----------------------------------------------------

create database test ON
(Name= test, filename= 'C:\datafiles\test.mdf',size = 10, maxsize= 200,
filegrowth = 10%)
log on
(Name= test_log,filename= 'C:\logfiles\test.ldf',size = 5Mb,maxsize= 20,
filegrowth = 5Mb);
go
select * from sys.master_files where name='test'
go
drop database test;
go

-----------------------------------------------------
/* Creating database with Multiple Data/Log Files Options */
-----------------------------------------------------

create database test ON
(Name= test, filename= 'C:\datafiles\test.mdf',size = 10, maxsize = 200,
filegrowth = 10),
(Name = test2, filename= 'D:\test2.ndf',size=10Mb, maxsize = 200Mb,
filegrowth = 10Mb)
log on
(Name= test_log,filename= 'C:\logfiles\test.ldf',size = 5Mb,maxsize = 20,
filegrowth = 5Mb),
(Name= test_log2,filename= 'C:\logfiles\test2.ldf',size=5Mb,maxsize = 20,
filegrowth = 5Mb);
go
select * from sys.master_files where name='test'
go
drop database test;
go

-----------------------------------------------------
/* Creating database with Filegroup Options */
-----------------------------------------------------

create database test ON
Primary
(Name= test, filename= 'C:\datafiles\test.mdf',size = 10, maxsize = 200,
filegrowth = 10),
Filegroup FG1
(Name = test2, filename='C:\datafiles\test2.mdf',size=10Mb,maxsize=200Mb,
filegrowth = 10Mb)
log on
(Name= test_log,filename= 'C:\logfiles\test.ldf',size = 5Mb,maxsize= 20,
filegrowth = 5Mb);
go
select * from sys.master_files where name='test'
go
drop database test;
go

-----------------------------------------------------
/* Creating database with ATTACH Options */
-----------------------------------------------------
create database test on
(name = 'test',filename='C:\test.mdf')
log on
(name = 'test_log',filename='C:\test.ldf')
go
EXEC master.dbo.sp_detach_db @dbname = N'test'
go
create database test ON
      (FILENAME = 'C:\test.mdf')
      log on
      (FILENAME = 'C:\test.ldf')
       FOR ATTACH ;
go

Most of the script is self explanatory, first option is a vanilla script the DB is created with default options defined in Model DB but ofcourse its not good choice. In second one, we have defined location / size / file growth option of both data and log file. In third script I have created Filegroup (an option to create database file physically separate and hence improving performance). In final script, I have used Attach option to create new database with existing data & log files.

Of course here I have neglected best practices recommendation, like both data and log file should be on physically different drives or even filegroups should be on different drives. Or try to avoid %growth of files (as it will generate many logical files resulting fragmentation and performance degrade) …

This is it for today’s post …

It’s just a thought … fingerscrossed

Leave a Reply