To Create a Database Using SQL Server 2000, Perform the Following Steps

Lab Procedures:

Unit 5, Ex – 1

To create a database using SQL Server 2000, perform the following steps:

1. Click StartProgramsMicrosoft SQL ServerEnterprise Manager. The SQL Server Enterprise Manager appears.

2. Expand the Microsoft SQL Servers node, as shown in the Figure. Click the Server Groupsnode. Click Windows NT. Right-click Databases. From the pop-up menu, click New Database.

SQL Server Enterprise Manager

The Database Properties dialog box appears, as shown in the Figure. Type Inventory in the Name text box.

Database Properties Dialog Box

4. Click OK. The Inventory database is created.

To create a new table in the Inventory database, perform the following steps:

1. Right-click Inventory. Click New, and then click Table, as shown in the Figure.

Creating a New Table

2. The New Table dialog box appears. Create a new table called Item Master with

the required fields, as shown in the Table.

Item Master Table

3. Insert a record with the values shown below:

To make a backup of the database, perform the following steps:

1. Expand the Management folder, select Backup, and then click New Backup Device, as shown in the Figure:

New Backup Device Option

2. The Backup Device Properties dialog box appears, as shown in the Figure. Type Inventory_backup in the Name text box. Click OK.

Backup Device Properties Dialog Box

3. Right-click the Inventory database. From the pop-up menu, click All Tasks, and then click Backup Database, as shown in the Figure.

Backup Database

4. The SQL Server Backup dialog box appears, as shown in the Figure. Type Full backup of inventory in the Description text box. Click Add to add

Inventory_backup to the Backup list.

SQL Server Backup Dialog Box

5. Click OK to backup the database. The Progress dialog box for the backup process appears and a message box confirming successful backup is shown.

6. Insert another record into the table Item Master with the values shown below:

To make a differential backup to back up the new record, perform the following steps:

1. Right-click Inventory. From the pop-up menu, choose All Tasks, and then click

Backup Database. The SQL Server backup dialog box appears, as shown in the Figure:

SQL Server Backup Dialog Box

2. Click the Database—differential option button. In the Destination list, the Inventory_backup device name is shown, as the previous database backup you created is already using it. Type Differential Database backup 1 in the description text box.

3. Click the Options tab. Select the Verify backup upon completion check box, as shown in the Figure:

Options Tab

To restore the backed up databases, perform the following steps:

1. Right-click the Inventory database. From the pop-up menu, click All Tasks, and then select Restore Database, as shown in the Figure:

Restoring a Database

Backing Up and Restoring Tasks

The Restore database dialog box appears, as shown in the Figure:

Restore database Dialog Box

2. Clear the second item in the list of the backups you had already created. This will restore only the first backup, which means only the first record is backed up. Select the contents of the table ItemMaster. Only the first record is displayed, as shown in the Figure:

Table Contents

3. Perform the restore operations for both the backups that you had created. Repeat Step 3. When the Restore database dialog box displays, do not clear any items in the backup list, as shown in the Figure:

Restore database Dialog Box

4. Click OK to restore both the records.

Conclusion

A new database named Inventory, with a table named ItemMaster, is created successfully. The database is successfully backed up and restored.

1

Lab Handout for IT390

Business Database Administration