Creating a SQL Server Database
Posted On September 14, 2016 by Shruthi S filed under Database
This article covers how to create a SQL Server database, how to set database options, how to work with the Transaction Log, and how to attach to an existing database.
Databases are at the heart of every SQL Server system. They contain the tables, database diagrams, views, stored procedures, functions, and triggers that comprise the system. This article covers:
- How to create a SQL Server database
- How to set database options
- How to work with the Transaction Log
- How to attach to an existing database
Creating the Database
Before you can build tables, views, stored procedures, triggers, functions, and other objects, you must create the database in which they will reside. A database is a collection of objects that relate to one another. An example would be all the tables and other objects necessary to build a sales order system. To create a SQL Server database, follow these steps:
- Right-click the Databases node and select New Database. The New Database dialog box appears (see Figure-1).
FIGURE-1 The New Database dialog box enables you to create a new database.
- Enter a name for the database.
- Scroll to the right to view the path for the database.
- Click the Ellipsis button. The Locate Folder dialog box appears.
- Select a path for the database (see Figure-2).
FIGURE-2 You can opt to accept the default path, or you can designate a path for the database.
5. Click OK to close the Locate Folder dialog box.
6. Click to select the Options page and change any options as desired (see Figure-3).
FIGURE-3 The Options page of the New Database dialog box enables you to set custom options for the database.
7. Click OK to close the New Database dialog box and save the new database. The database now appears under the list of databases (see Figure-4) under the Databases node of SQL Server Management Studio. If the database does not appear, right-click the Databases node and select Refresh.
FIGURE-.4 The new database appears under the list of databases in the Databases node.
Defining Database Options
In the previous section, you created a new SQL Server database. You accepted all the default options available on the General page of the New Database dialog box. Many important options are available on the General page. They include the Logical Name, File Type, Filegroup, Initial Size, Autogrowth, Path, and File Name (see Figure-5).
FIGURE-5 Several important features are available on the General page of the New Database dialog box.
The logical name is the name that SQL Server will use to refer to the database. It is also the name you will use to refer to the database when writing programming code that accesses it.
The File Type is Data or Log. As its name implies, SQL Server stores data in data files. The file type of Log indicates that the file is a transaction log file.
The initial size is very important. You use it to designate the amount of space you will allocate initially to the database.
Related to the initial size is the Autogrowth option. When you click the build button (ellipsis) to the right of the currently selected Autogrowth option, the Change Autogrowth dialog box appears (see Figure-6).
FIGURE-6: The Change Autogrowth dialog box enables you to designate options that affect how the database file grows.
The first question is whether you want to support autogrowth at all. Some database designers initially make their databases larger than they ever think they should be and then set autogrowth to false. They want an error to occur so that they will be notified when the database exceeds the allocated size. The idea is that they want to check things out to make sure everything is okay before allowing the database to grow to a larger size.
The second question is whether you want to grow the file in percentage or in megabytes. For example, you can opt to grow the file 10% at a time. This means that if the database reaches the limit of 5,000 megabytes, then 10% growth would grow the file by 500 megabytes. If instead the file growth were fixed at 1,000 megabytes, the file would grow by that amount regardless of the original size of the file.
The final question is whether you want to restrict the amount of growth that occurs. If you opt to restrict file growth, you designate the restriction in megabytes. Like the Support Autogrowth feature, when you restrict the file size, you essentially assert that you want to be notified if the file exceeds that size. With unrestricted file size, the only limit to file size is the amount of available disk space on the server.
One great feature of SQL Server is that you can span a database’s objects over several files, all located on separate devices. We refer to this as a file group. By creating a file group, you improve the performance of the database because multiple hardware devices can access the data simultaneously.
The Transaction Log
SQL Server uses the transaction log to record every change that is made to the database. In the case of a system crash, you use the transaction log, along with the most recent backup file, to restore the system to the most recent data available. The transaction log supports the recovery of individual transactions, the recovery of all incomplete transactions when SQL Server is once again started, and the rolling back of a restored database, file, filegroup, or page forward to the point of failure. Specifying information about the transaction log is similar to doing so for a database. Follow these steps:
- While creating a new database, you can also enter information about the log file. To begin, enter a logical name for the database. I recommend you use the logical name of the database along with the suffix _log.
- Specify the initial size of the log file.
- Indicate how you want the log file to grow.
- Designate the path within which you want to store the database.
- Continue the process of creating the database file.
Attaching to an Existing Database
There are times when someone will provide you with a database that you want to work with on your own server. To work with an existing database, all you have to do is attach to it. Here’s the process:
- Right-click the Databases node and select Attach. The Attach Databases dialog box appears (see Figure-7).
- FIGURE-.7 The Attach Databases dialog box enables you to attach to existing .mdf database files.
- Click Add. The Locate Database Files dialog box appears (see Figure-8).
FIGURE-8 The Locate Database Files dialog box enables you to select the database to which you want to attach.
- Locate and select the .mdf to which you want to attach.
- Click OK to close the Locate Database Files dialog box.
- Click OK to close the Attach Databases dialog box. The database appears in the list of user databases under the Databases node of SQL Server Management Studio.
The ability to create a database is fundamental to working with SQL Server. The process of creating a database involves understanding what a log file is and how to configure it. After you have created both the database and the log file, you are ready to create and work with the other database objects.