You can create a new database by using Object Explorer or the Transact-SQL language. As the name suggests, you also use Object Explorer to explore the objects within a server. From the Object Explorer pane, you can inspect all the objects within a server and manage your server and databases. The existing tree contains, among other folders, the Databases folder. This folder has several subfolders, including one for the system databases and one for each new database that is created by a user.
To create a database using Object Explorer, right-click Databases and select New Database. In the New Database dialog box (see Figure 3-5), type the name of the new database in the Database Name field and then click OK. 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 pages (property groups):
- Change Tracking
- Extended Properties
- Transaction Log Shipping
The General page of the Database Properties dialog box (see Figure 3-6) displays, among other things, the database name, the owner of the database and its collation. The properties of the data files that belong to a particular database are listed in the Files page and 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.
The Filegroups page of the Database Properties dialog box displays the 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).
The Options page of the Database Properties dialog box enables you to display and modify all database-level options. There are several groups of options: Automatic, Containment, Cursor, Miscellaneous, Recovery, Service Broker, and State. For instance, the following four options exist for State:
- 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 MULTI_USER.)
- Encryption Enabled – Controls the database encryption state. (The default value is False.)
The Extended Properties page displays additional properties of the current database. Existing properties can be deleted and new properties can be added from this dialog box.
If you choose the Permissions page, the system opens the corresponding dialog box and displays all users and roles along with their permissions.
The rest of the pages (Change Tracking, Mirroring, and Transaction Log Shipping) describe the features which are related to data availability and are therefore explained in detail in coming chapters.
Modifying Databases Without Using Transact-SQL
Object Explorer can also be used to modify an existing database. Using this component, you can modify files and filegroups that belong to the database. To add new data files, right-click the database name and choose Properties. In the Database Properties dialog box, select Files, click Add, and type the name of the new file. You can also add a (secondary) filegroup for the database by selecting Filegroups and clicking Add.
To delete a database using Object Explorer, right-click the database name and choose Delete.