Managing Databases Using Object Explorer

Object Explorer allows you to inspect objects in a server and manage your server and databases. Get started in this excerpt from SQL Server 2005: A Beginner's Guide.

Microsoft SQL Server 2005: The Complete Reference The following was excerpted from Chapter 2, 'SQL Server 2005 Management Studio,' of the book SQL Server 2005: A Beginner's Guide written by Dusan Petkovic and published by McGraw-Hill Osborne Media. Click here for the book excerpt series.

Bonus: TechTarget members take 45% off the price of this book at McGraw-Hill's online bookstore. Just enter promotion code AETT511 on the homepage.

Managing Databases Using Object Explorer

You use Object Explorer to explore the objects within a server. This component of SQL Server Management Studio can be used after a connection to the server is established. From Object Explorer you can inspect all the objects within a server and manage your server and databases. The Object Explorer tree has the same form as in the previous versions of SQL Server, with one exception: The Database folder contains several subfolders, one for the system databases and one for each new database that is created by a user. (System and user databases are discussed in detail in Chapter 3.)

To create a database using Object Explorer, right-click Databases and select New Database. In the New Database dialog box (Figure 2-5), type the name of the new database and click OK. (As you can see in Figure 2-5, we use the New Database dialog box to create the sample database.) Each database has several different properties, such as file type, initial size, and so on. Database properties can be selected from the left pane of the New Database dialog box. There are several different property groups, including the following:

  • General
  • Files (appears only for an existing database)
  • Options
  • Filegroups
  • Permissions (appears only for an existing database)
  • Mirroring (appears only for an existing database)
  • Extended Properties

Figure 2-5: The New Database dialog box

General properties of the database (Figure 2-6) include, among others, the database name, the owner of the database, its collation, and size. The properties of the data files that belong to a particular database comprise the name and initial size of the file, where the database will be stored, and the type of the file (PRIMARY, for instance). A database can be stored in multiple files.

NOTE: SQL Server has dynamic disk space management. This means databases can be set up to automatically expand and shrink as needed. If you want to change the Autogrowth property of the Files option, click . . . in the Autogrowth column and make your changes in the Change Autogrowth dialog box. The Enable Autogrowth check box should be checked to allow the database to autogrow. Each time there is insufficient space within the file when data is added to the database, the server will request the additional space from the operating system. The amount (in megabytes) of the additional space is set by the number in the File Growth frame of the same dialog box. You can also decide whether the file can grow without any restrictions (the default value) or not. If you restrict the file growth, you have to specify the maximum file size (in MB).

Figure 2-6: The Database Properties dialog box: the General page

The Filegroups properties of a database contain name(s) of the filegroup(s) to which the database file belongs, the art of the filegroup (default or nondefault), and the allowed operation on the filegroup (read/write or read only).

All database-level options can be displayed and modified by choosing the Options properties. There are several groups of options: Automatic, Cursor, Miscellaneous, Recovery, and State. Three options concern the state of a database:

  • Database Read-Only Allows read-only access to the database. This prohibits users from modifying any data. (The default value is False.)
  • Database State Describes the state of the database. (The default value is Normal.)
  • Restrict Access Restricts the use of the database to one user at a time. (The default value is Multiple.)

If you choose the Permissions properties, SQL Server will display the corresponding dialog box with all users and roles along with their permissions. (For the discussion of permissions, see Chapter 12.)

Object Explorer can also be used to modify an existing database. Using Object Explorer, you can modify files and filegroups that belong to the database. To add new data files, right-click the database name, choose Properties, and select Files. In the Database Properties dialog box, click Add and type the name of the new file. (In this dialog box, you can also change the autogrowth properties and the location of each existing file.) You can also add a (secondary) filegroup for the database by selecting Filegroups and clicking Add. (A list of all properties that can be modified is given with the definition of the CREATE DATABASE statement in Chapter 4.)

NOTE: Only the system administrator or the database owner can modify the database properties mentioned above.

To delete a database using Object Explorer, right-click the database name and choose Delete.

Click for the complete book excerpt series.

Dig Deeper on SQL Server Database Modeling and Design