remidian.com

Create, rename and drop a database in sql server …

Create database

See syntax below for the CREATE DATABASE statement.

Create NORTHWIND database

1> use master
2> go
1> create database [NORTHWIND]
2>     on ( name        = N'NORTHWIND_dat',
3>          filename    = N'C:\\mssql\\data\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.mdf' ,
4>          size        = 10,
5>          filegrowth  = 1,
6>          maxsize     = 1024
7>        )
8> log on ( name        = N'NORTHWIND_log',
9>          filename    = N'C:\\mssql\\tlog\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.ldf' ,
10>         size        = 10,
11>         filegrowth  = 1,
12>         maxsize     = 1024
13>        )
14>  collate Latin1_General_CI_AS
15>
16> go
The CREATE DATABASE process is allocating 10.00 MB on disk 'NORTHWIND_dat'.
The CREATE DATABASE process is allocating 10.00 MB on disk 'NORTHWIND_log'.
1>

Drop database

A database can be dropped using the DROP DATABASE statement. All database datafiles and logfiles are removed from the os.

Dropping a database

1> use master
2> go
1> drop database [NORTHWIND]
2> go
Deleting database file 'C:\\mssql\\tlog\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.ldf'.
Deleting database file 'C:\\mssql\\data\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.mdf'.
1>

Rename database

A database can be renamed with the stored procedure SP_RENAMEDB. (VERY easy compared to oracle …)

Rename the NORTHWIND database to ‘WESTWIND’

1> use master
2> go
1> select dbid, left( name, 10) name
2>   from sysdatabases
3>  where name = 'NORTHWIND'
4> go
 dbid   name
 ------ ----------
     17 NORTHWIND
 
(1 row affected)
1>
2> exec sp_renamedb 'NORTHWIND', 'WESTWIND'
3> go
The database name 'WESTWIND' has been set.
1>
2> select dbid, left( name, 10) name
3>   from sysdatabases
4>  where dbid = 17
5> go
 dbid   name
 ------ ----------
     17 WESTWIND
 
(1 row affected)
1>