Some RnD in Microsoft Sql Server 2008R2

Many new Sql Server DBAs would need to do their RnD with the MSS database. In previous blog I have given few options available while creating database.

In this blog we would discuss more operations once the database is created.

1:-   Rename a database

To rename the database, we have GUI and command line. First we discuss the GUI method which is really simple.

Using Management Studio (GUI), Select the database you want to rename and in option select
"RENAME"

Another method is run query in query area.
Alter database <nm> modify name =  sample3

Third method is to use sp_renameDB procedure to achive same.

Execute sp_renameDB 'oldnm','newnm'

2:-   Drop a database
Before we proceed with dropping the database. Following point should be kept in mind otherwise
our DROP statement would fail.

Database should be running in sigle user mode. No other user should be connected to it otherwise
database would not be dropped.

Once database is running in Single user mode. We have following options to drop it.
1:- Using management studio, select the database you want to drop. Right click on it and select delete
option.

2:- On Query window write following command while connected to MASTER or any other database but the database
being dropped.

Drop database '<Name of the Database>';

Note:- If users are connected with database being dropped than run following command to bring it in single user
mode.
Alter Database <Name of the database> Set single_user with rollback immediate;

Drop database <Name of the database>;

We cannot system databases.


Any correction,suggestion are welcome.