SQL Commands - Create, Drop and Alter Database in SQL Server 2008


SQL Commands - Create, Drop and Alter Database in SQL Server 2008

Creating and Altering a Database

In previous article I explained about Difference between MS SQL 2005 & SQL Server 2000 - SQL Server Tutorials. Here in this article I will explain how to create, alter and drop database in SQL Server 2008.

A SQL Server database can be created, altered and dropped
  1. Graphically using SQL Server Management Studio (SSMS) or
  2. Using a Query

To Create the database using a Query
EgCreate Database Sample1

To Select the database using a Query
Use Sample1

Whether, you create a database graphically using the designer or, using a query, the following two files get generated:
  1. .MDF file - Data File (Contains actual data)
  2. .LDF file - Transaction log file (Used to recover the database)
sql basics, sql server interview questions, sql server management studio for sql server 2008,  SQL Commands, sql query tutorial,sql server 2008 tutorial

To Alter a database, once it's created
Egalter database sample1 modify name sample2

Alternatively, you can also use system stored procedure
Egsp_renamedb 'sample2','sample3'

Deleting or Dropping a Database

To Delete or Drop a database
Egdrop database sample3

Dropping a database, deletes the .LDF and .MDF files. 
You can't drop a database , if it is currently in use. You get an error starting - Cannot drop database <NewDatabaseName> because it is currently in use.

So, if other users are connected, you need to put the database in single user mode and them drop the database.
EgAlter database Sample3 Set Single_User with Rollback Immediate

With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.

Note: System databases cannot be dropped

SQL Commands - Create, Drop and Alter Database in SQL Server 2008 Reviewed by Ravi Kumar on 11:42 PM Rating: 5

1 comment:

All Rights Reserved by Etechpulse © 2012 - 2017

Contact Form


Email *

Message *

Powered by Blogger.