Time Required: 60 minutes
Objective: Create a new user database in Microsoft SQL Server Management Studio.
Description: Changing business needs typically dictate when a new database needs to be created.
In this activity, you will learn the steps involved in creating a new user database by launching the New Database dialog box from Object Explorer in SQL Server Management Studio. You will then use Object Explorer to automatically generate a SQL script to create a second user database that clones the configuration settings of the first database. Finally, you will write a simple SQL Table 4-1 Commonly used system data types
Data type Description Example
query in a Query Editor window using the CREATE database syntax to create a third database that uses all of the default settings from the model system database. You will use Windows Explorer to view the physical data and log files that will be created on the local operating system.
1. If necessary, start your computer and log on using an Administrator account.
2. Click the Start button, point to All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio. In the Connect to Server dialog box, select Database Engine as the server type, type LOCALHOST\SQLSERVERUA in the Server name text box, and select Windows Authentication from the Authentication list box. Click Connect.
3. In the Object Explorer navigation pane on the left of the SQL Server Management Studio window, expand the SQLSERVERUA folder by clicking the + symbol.
Right-click the Databases folder, and in the shortcut menu, click New Database (see Figure 4-6) to launch the New Database dialog box.
Figure 4-6 Databases folder shortcut menu
Microsoft SQL Server 2012
4. In the New Database dialog box, click in the Database name text box and type TestOne (using camel case) as your database name. Click in the Initial Size (MB) text box, and type 50 for the data file (first row) and type 20 for the log file (second row). Click the … icon in the Autogrowth/Maxsize column for the data file. In the Change Autogrowth for TestOne dialog box, click the Enable Autogrowth check box, and then type 10 in the File Growth In Megabytes text box. Keep the default maxi-mum file size set to unlimited. Click OK. Now Click the … icon in the Autogrowth/
Maxsize column for the log file. In the Change Autogrowth for TestOne_log dialog box, click the Enable Autogrowth check box, and then type 5 in the File Growth In Megabytes text box. Keep the default maximum file size set to unlimited. Click OK to return to the New Database dialog box. Note that this window gives you the ability to add additional data or log files by clicking the Add button. It also allows you to change the owner of the database and the path to the physical files. See Figure 4-7.
4
5. In the left navigation pane, click Options to open the Options page of database properties in the New Database dialog box. See Figure 4-8.
Figure 4-7 New Database dialog box—General page
Microsoft SQL Server 2012
Figure 4-8 New Database dialog box—Options page
Microsoft SQL Server 2012
This page allows you to view or modify the configuration options for the new database. Many of these options should be familiar to you because you read about them in Activity 4-1 when you accessed the “CREATE DATABASE (Transact-SQL)”
topic on Books Online for SQL Server 2012. Keep the default configuration options.
6. In the left navigation pane, click Filegroups to open the Filegroups page of database properties in the New Database dialog box. See Figure 4-9.
Figure 4-9 New Database dialog box—Filegroups page
Microsoft SQL Server 2012
7. This page allows you to view or modify the filegroups or filestreams for the database.
Every database must have a primary filegroup, which the database will use as its default data storage location. A filegroup contains one or more data files, which the database management system will fill in roughly equal proportions. Additional user-defined filegroups can be added to improve performance. For example, a nonprimary filegroup may be designated as the data storage location for a particular table. Filestreams are only needed if you intend to store unstructured data within the database. Click the Help button at the top of the dialog box to open the “Database Properties (Filegroups Page)” topic on Books Online for SQL Server 2012 in your Web browser. When you are finished reviewing the information, close your Web browser.
4
8. In the New Database dialog box, click OK to create the database. The New Database dialog box closes, and you are returned to Object Explorer.
The left navigation page of Object Explorer does not automatically refresh after a change is made to a schema object. You must manually refresh the user interface to see the changes.
9. Click the Databases folder in Object Explorer, and then click the Refresh button on the toolbar. If the Databases folder is not already expanded, click the + symbol to the left of the folder to expand. You should see your new database named TestOne listed beneath AdventureWorks2012. See Figure 4-10.
Figure 4-10 TestOne database is visible in the Databases folder of Object Explorer
Microsoft SQL Server 2012
10. In Object Explorer, right-click the TestOne database, and then click Properties to open the Database Properties dialog box and view the configuration settings of the database that you created in Step 8. See Figure 4-11. Close the Database Properties dialog box by clicking the X in the upper-right corner of the window.
11. Using Object Explorer, you will now instruct the database to generate a SQL CREATE DATABASE script that will open in a Query Editor window using the TestOne database as your template for the configuration. Right-click the TestOne database, point to Script Database as, point to CREATE To, and then click New Query Editor Window. See Figure 4-12.
12. The CREATE DATABASE script appears in a Query Editor window on your right. You are making a clone of the TestOne database, but you must use a different name because a database name is unique. On the menu bar of SQL Server Management Studio, click Edit, point to Find and Replace, and then click Quick Replace. See Figure 4-13.
13. In the Find and Replace dialog box, type TestOne in the Find what text box, and type TestTwo in the Replace with text box. Click Replace All. A dialog box opens informing you that you have replaced 38 occurrences of TestOne with TestTwo in the current document. Click OK, and then close the Find and Replace dialog box.
14. Review the SQL database CREATE script in the Query Editor window. Note that some of the occurrences of TestTwo are underlined in red. This is because the data-base management system does not yet recognize TestTwo as a valid datadata-base object (you are about to create it). Click the Execute button on the toolbar or press the F5 Figure 4-11 Database Properties dialog box for the TestOne database.
Microsoft SQL Server 2012
4
Figure 4-12 Menu options to automatically generate a CREATE DATABASE script in SQL
Microsoft SQL Server 2012
Figure 4-13 Use Quick Replace to replace TestOne with TestTwo
Microsoft SQL Server 2012
shortcut key to execute the query and create the TestTwo database. See Figure 4-14.
The message “Command(s) completed successfully” should appear in a pane at the bottom of Query Editor.
Figure 4-14 CREATE DATABASE script for the TestTwo database prior to execution
Microsoft SQL Server 2012
Figure 4-15 Find the file path of the data and log files using the Database Properties dialog box
Microsoft SQL Server 2012
15. In Object Explorer, click Databases, and then click the Refresh button on the toolbar.
Expand the Databases folder, right-click the TestTwo database, and then click Properties to open the Database Properties dialog box. Click Files in the left navigation menu. Scroll to the right, and make a note of the file path and filename for the data and log file in the Database files section. Copy the file path into your Clipboard by clicking the top cell in the Path column and pressing Ctrl+C. See Figure 4-15.
4
Figure 4-16 Navigate to the Data folder of the SQL server instance SQLSERVERUA
Microsoft SQL Server 2012
17. Note that this folder contains a data and log file for each of the system databases and the user database that you have created. For a production database server, it is not recommended that you use the same physical disk for both file types. Close Windows Explorer.
18. Return to SQL Server Management Studio and click the New Query button on the toolbar to launch an empty Query Editor window. Type the following SQL query in the Query Editor window: CREATE DATABASE TestThree. Press the F5 shortcut key to execute your SQL query. See Figure 4-17.
19. You have now created a new database called TestThree using the SQL programming language using the defaults from the model system database. You can view this in Object Explorer by clicking the Databases folder, clicking the Refresh button on the toolbar, and then expanding the Databases folder.