Create, drop and rename a database

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>

One thought on “Create, drop and rename a database

  1. Tracey says:

    Just wanted to say thknas for this script, it helped me out today. Just to add to the discussion, I made a couple of easy modifications that made it more helpful for me.1. Limit results to one database name instead of by table name2. Order by last execution time Shows recently executed stored procedures for a given databaseSelect Object_Name(dest.objectid, dest.[dbid]) As CalledprocName’ , Max(deqs.last_execution_time) As last_execution’From sys.dm_exec_query_stats As deqsCross Apply sys.dm_exec_sql_text(deqs.sql_handle) As destWhere dest.[dbid] Is Not Null exclude ad-hocs and DB_Name(dest.[dbid]) = YourDatabaseNameHere’Group By db_name(dest.[dbid]) , Object_Name(dest.objectid, dest.[dbid])Order By last_execution descOption (MaxDop 1);I thought about putting it into a function for easy call but it’s a fairly short script so may not be necessary. Anyway, thknas again.

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>