This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Creating SQL Server Databases Part 1

The third part of the SQL Server Programming Fundamentals tutorial investigates the creation of new databases. This first task in any new database project requires the careful consideration of several key configuration options.

Recovery Model

SQL Server provides three recovery models that determine how a database's log file is used. Each model has advantages and disadvantages, as explained below:

  • Full. The full recovery model is used to minimise the chance of data loss in a disaster situation. In this model, all transactions are logged to the log file in detail. The log file must be backed up along with all data files. In a recovery situation, the data files and log files can be restored to a specific point in time and usually no data is lost. It is possible that the log file can be damaged in which case operations since the last log file backup must be re-run. This model can lead to very large log files if they are not backed up and truncated often. The full recovery model is useful for business-critical systems.
  • Simple. In the simple recovery model the log file is automatically cleared by the system to keep it small. There is no requirement, or benefit, to backing up the log file. Instead, only the data files are backed up. This limits recovery operations to restoring to the point at which the database was last backed up. The simple model is useful for development and test databases, or for non-critical live applications.
  • Bulk Logged. The bulk-logged model is similar to the full model except that most bulk operations are logged in minimal detail. The lowers the size of the log files at the cost of not being able to recover to a specific point in time. The transaction logs must be backed up when using this option. The bulk-logged model is useful for small periods of bulk transactions. Following these periods, the database can be backed up and switched back to the full model.

As the JoBS database is to be used for example purposes only, choose the simple recovery model.

Compatibility Level

The Management Studio can be used to create databases for earlier versions of SQL Server. If an earlier version is to be targeted, an alternative option can be selected from the Compatibility Level drop-down list. Choosing an earlier version will mean that less functionality is available.

For the JoBS database, ensure that the SQL Server 2005 option is selected.

Other Options

The final area of the New Database dialog box contains a set of further configuration options that can be adjusted as required. These are beyond the scope of this article and should be left at their default settings for the JoBS database.

Creating the New Database

Now that the entire configuration for the new database is complete, it can be generated. Click the OK button to complete the process. You will notice that the status at the bottom-left of the dialog box changes briefly to "Executing" whilst the model database is duplicated and the configuration settings are applied. Once completed, the dialog box will close and the Object Browser will have a new tree node named "JoBS" beneath the Databases branch.

Congratulations, you have now created the new database!

8 June 2008