Database Deletion

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.

Using Enterprise Manager

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:

  1. In Enterprise Manager, expand the SQL Server group and then expand the name of the server on which the database is installed.
  2. Expand the Databases folder to view the available databases.
  3. Right-click the name of the database you want to delete, and then choose Delete from the shortcut menu. The Delete Database message box appears, as shown in Figure 9-14. It asks if you want to delete the backup-and-restore history of this database as well as delete the database. If you select this option, all backup-and-restore information in the backup-and-restore history tables that reside in the msdb database will be deleted. If you want to keep this data, clear the check box. Click Yes to confirm the deletion.

    NOTE


    You cannot delete the master database.

    Figure 9-14. The Delete Database message box.

Using SQL

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.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net