Databases Wizard (SQL Server)

From DBArtisan
Jump to: navigation, search

Go Up to Microsoft SQL Server Object Wizards

The Database Wizard presents you with a different set of options based on your server version to create the database accurately on each platform.

Tip: Microsoft SQL Server recommends that you do not create any user objects, such as tables, views, stored procedures, or triggers, in the master database. The master database includes the system tables that store the system information used by SQL Server, such as configuration option settings.

To create a new database using a wizard:

  1. Open a creation wizard for a database. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Databases (SQL Server) - Options

When creating or editing a database, this tab/panel lets you work with the following settings:

Setting Description

Name

Provide a name for the database.

Attach existing OS files

To create a database from an existing set of operating system files, there must be a <filespec> entry for the first PRIMARY file. The PRIMARY filegroup contains all the database system tables. Primary files have a .mdf extension.

Compatible Level

Select a version compatibility level.

Properties group

Select the following settings: ANSI null default, ANSI nulls, ANSI padding, ANSI warnings, auto create statistics, auto update statistics, autoclose, autoshrink, concat null yields null, cursor close on commit, arithabort, db chaining, dbo use only, default to local cursor, merge publish, numeric roundabout, offline, published, quoted identifier, read only, recursive triggers, select into/bulkcopy/pllsort, single user, subscribed, torn page detection, and trunc log on chkpt.

Databases (SQL Server) - Placement

Indicate the file where you want the database to live. For example, a new Books database could include author and title filegroups.

By default, when you open the Wizard and click the Placement tab, a filegroup definition, using the name you provided for the database and default settings, is displayed. For each filegroup to be added, click the New button, provide a Device File Name for the filegroup, and use the File Group Properties and Device File Properties groups to provide the attributes of the filegroup.

Use the Delete button to delete a selected filegroup.

Databases (SQL Server) - Transaction Log

The transaction log file is a required file for each database. This file holds the log information to recover the database. There can be multiple log files for a database, but there has to be at least one. Traditionally the logfile extension has been .ldf.

By default, when you open the Wizard and click the Transaction Log tab, a transaction log file definition, using the name derived from the name you provided for the database and with default settings, is displayed. For each file to be added, click the New button, provide a Device File Name, and use the Log Device Properties group to provide the attributes of the file.

Use the Delete button to delete a selected file.

Note: As you complete the wizard, be aware that the Primary file contains startup information for the database and is also used to store data. The transaction log files hold the information used to recover the database.