SQL Server Database

SQL Server database is nothing but a logical container for set of related objects like tables, views, synonyms, indexes, indexed views, procedures, functions, triggers.

Any SQL Server Database contains two files. One is called data file(.mdb file and .ndf) and second file(.ldf file) is called log file. Data file contains actual data and log-file contains operations(transacions) performed on database.

When we install SQL server on machine, it installs default system databases master, model, msdb and tempdb.

Creating User database:

By Command

Syntax:

USE [master]
GO
CREATE DATABASE [pinnapa] ON PRIMARY
( NAME = N’pinnapa’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\pinnapa.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’pinnapa_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\pinnapa_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

By Graphical method.

1) Open Microsoft SQL Server Management Studio

SQL-Server-Database

2) Click on New Database and enter the database name. click ok

SQL-Server-Database1

3) Then, new database will appear in the databases tree.

Altering database
Alter database is nothing but modifying the structure of database.

Syntax:

alter database database_name
add file (data file specification)
add file (data file specification)
modify file (data file specification)
remove file (data file specification)

Example:

alter database pinnapa
add file(
name=pinnapa1,
filename=’filepath’
size=2GB,
maxsize=5GB,
filegrowth=2MB)

Change database name:

Set the database to single mode:
alter database pinnapa
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Try to rename the database
alter database pinnapa
modify name=[pinnapa2]
 
Set the database to Multiuser mode:
ALTER DATABASE pinnapa2
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Drop the database:
Once we drop the database all associated data files and log files removed from the SQL server.
 
Sytax:

drop database pinnapa

Powered by k2schools