3 4
At some point, you might want to remove a database. Keep in mind that this is a one-way street—once you delete a database, the only way you can retrieve it is by restoring a backup version. It is safest, then, to back up the database before you delete it, just in case you might need it again in the future. You can delete a database by using Enterprise Manager or T-SQL commands.
As mentioned in Chapter 8, Enterprise Manager allows you to perform administration on your databases as well as to view information. To permanently delete a database and all of its files by using Enterprise Manager, follow these steps:
NOTE
You cannot delete the master database.
Figure 9-14. The Delete Database message box.
You can also administer databases by using T-SQL commands. These can be run in Query Analyzer or in the command prompt window, as mentioned earlier. To delete a database using T-SQL, open either Query Analyzer, as described earlier, or a command prompt window, and make a connection to SQL Server through OSQL, as shown in the following example:
OSQL -U<username> -P<password> -S<servername>
Remember that deleting a database is a permanent action. The T-SQL command used for deleting a database is DROP DATABASE. The commands to delete the MyDB database and all of its files are shown here:
USE master You must use the master database to run the DROP GO -- database command. DROP DATABASE MyDB -- The only parameter is the name of -- the database to be removed. GO
After deleting a database, you should make a new backup of the master database so it will have the current information about user databases and so it doesn't include information about the database you just deleted. Also, note that a database cannot be deleted while users are accessing it. All users must disconnect from a database before it is deleted.