You can rename user databases in SQL Server Management Studio and with the ALTER DATABASE MODIFY NAME statement. With the database in single-user or offline mode, right-click the database name in SQL Server Management Studio and select Rename from the shortcut menu. Then type the database name and press Tab.

To use T-SQL to put the database in single-user mode and change the name, complete the following steps:

In SQL Server 2005, dropping and deleting a database are the same thing. When you drop a database, you remove the database and its associated files from the server. After you drop a database, it is permanently deleted, and you cannot restore it without using a backup. To delete references to a database without removing the database files, use sp_detach_db, as described later in this section.

You cannot drop system databases, and you cannot drop databases that are currently in use by SQL Server or other users. A database can be dropped regardless of its state. However, any replication or database snapshots on a database must be stopped or dropped before the database can be deleted. Furthermore, if the database is configured for log shipping, you should remove log shipping before dropping the database. Also note that a dropped database can be re-created only by restoring a backup. After you drop a database, you should back up the master database.

You can drop a database by completing the following steps:

You can also delete a database with the DROP DATABASE command. The syntax and usage for this command are shown in Example 7-4.

DROP DATABASE { database_name | database_snapshot_name} [,..n]

use master
ALTER DATABASE Customer
SET single_user
GO
DROP DATABASE "Customer"
GO

The attach and detach operations are designed primarily to move database files or disable databases without deleting their files. When you detach a database, you remove references to the server in the master database, but you do not delete the related database files. Detached databases are not displayed in SQL Server Management Studio, and they are not accessible to users. If you want to use the database again, you can reattach it. Attaching a database creates a new database that references data stored in existing data and log files.

Before you can detach a database, you must ensure that none of the following conditions are true:

Usually, attaching a database places it in the same state that it was in when it was detached. However, SQL Server 2005 disables cross-database ownership chaining and sets the TRUSTWORTHY option to OFF when a database is attached. You can re-enable these features if necessary as discussed in the section titled "Managing Cross-Database Chaining and External Access Options" earlier in this chapter.

When you attach a database, all primary and secondary data files must be available. If any data file has a different path than it had when the database was first created or last attached, you must specify the file’s current path.

Detaching a Database

When you detach a database, you can specify if you want to update the statistics before the database is detached. Updating statistics makes the database easier to use with read-only media; otherwise, you really do not need the update. To update statistics, set the skipchecks flag to TRUE.

Because full-text catalogs are associated with databases in SQL Server 2005, you can also control whether they are maintained or dropped during the detach operation. By default, full-text catalogs are maintained as part of the database. To drop catalogs, set the keepfulltextindexfile flag to FALSE.

You detach a database using sp_detach_db, as shown in Example 7-5.

Example 7-5. sp_detach_db Syntax and Usage

Syntax

sp_detach_db [ @dbname= ] "dbname"
    [ , [ @skipchecks= ] "skipchecks" ]
    [ , [ @KeepFulltextIndexFile= ] "KeepFulltextIndexFile" ]

Usage

exec sp_detach_db "sample", "true"

Tip

You cannot detach system databases, and you can only detach user databases when they are not in use. Furthermore, before detaching a user database, you may want to close all current connections, put the database in single-user mode, and then run the detach operation.

CREATE DATABASE database_name
    ON <filespec> [ ,...n ]
    FOR { ATTACH [ WITH <service_broker_option> ]
        | ATTACH_REBUILD_LOG }
[;]


<filespec> ::=
{
[ PRIMARY ]
(
    [ NAME = logical_file_name , ]
    FILENAME = "os_file_name"
        [ , SIZE = size [ KB | MB | GB | TB ] ]
        [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
        [ , FILEGROWTH = growth_increment  [ KB | MB | % ] ]
) [ ,...n ]
}

CREATE DATABASE Customer
ON (FILENAME = "c:datacustomer_data.mdf")
FOR ATTACH
GO

CREATE DATABASE database_name
    ON <filespec> [ ,...n ]
    FOR ATTACH_REBUILD_LOG }
[;]

<filespec> ::=
{
[ PRIMARY ]
(
    [ NAME = logical_file_name , ]
    FILENAME = "os_file_name"
        [ , SIZE = size [ KB | MB | GB | TB ] ]
       [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
        [ , FILEGROWTH = growth_increment [ KB | MB | % ] ]
) [ ,...n ]
}

CREATE DATABASE Customer
ON (FILENAME = "c:datacustomer_data.mdf")
FOR ATTACH_REBUILD_LOG
GO