Database Maintenance Overview

There are several functions on the right-click menu for maintaining databases, including deleting databases, attaching and detaching databases, and backing up and restoring databases.

You can use Server Manager to delete a nonsystem database when it is no longer needed or if it is moved to another database or server. When a database is deleted, the files and their data are deleted from the disk on the server, and it is permanently deleted and cannot be retrieved without using a previous backup.

Data and transaction log files of a database can be detached and then reattached to another server or even to the same server. Detaching a database removes the database from Microsoft SQL Server but leaves the database intact within the data and transaction log files that compose the database. These data and transaction log files can then be used to attach the database to any instance of Microsoft SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached.

Detaching and attaching databases is useful if you want to move a database:

• From one computer to another without having to re-create the database and then restore the database backup manually.

• To a different physical disk, for example, when the disk containing the database file has run out of disk space and you want to expand the existing file rather than add a new file to the database on the other disk.

• To move a database, or database file, to another server or disk, you must detach the database, move the database files to another server or disk, and then attach the database specifying the new location of the moved files.

When you attach a database, the name and physical location of the primary data file must be specified. The primary file contains the information needed to find the other files comprising the database unless one or more of those files have changed location since the database was detached.

The backup and restore functions provide an important safeguard for protecting critical data stored in Microsoft SQL Server databases.

With proper planning, you can recover from many failures, including:

• Media failure

• User errors

• Permanent loss of a server

Additionally, backing up and restoring databases is useful for other purposes, such as copying a database from one server to another. By backing up a database from one computer and restoring the database to another, a copy of a database can be made quickly and easily.

In order to develop a successful backup and restore plan, you must understand when your data needs to be accessible and the potential impact of data loss on your business. Your overall backup strategy defines the type and frequency of backups and the nature and speed of the hardware required for them.

It is strongly recommended that you test your backup and recovery procedures thoroughly. Testing helps to ensure that you have the required backups to recover from various failures, and that your procedures can be executed smoothly and quickly when a real failure occurs.