How to Create a Database in SQL Server 2005
Creating a database is a basic task for a database administrator or for a sql developer. In order to create database, you have to be granted required permissions. Or you should be system administrator on the related MS SQL Server 2005 database instance.
MS SQL Server DBA 's can perform creating database task using the Microsoft SQL Server Management Studio which supplies a GUI tool for managing SQL Server.
Or database DBA 's can create database using t-sql CREATE DATABASE command.
Create Database using Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio is one of the tools SQL Server administrators, developers and database professionals is using for managing SQL Server.
SQL Server Management Studio is a graphical user interface for managing SQL Server and developing on SQL Server and tracing activities and performance of SQL Server.
Shortcut to SSMS (SQL Server Management Studio) GUI is in the Microsoft SQL Server 2005 menu group.
Open SSMS, then connect to the MS SQL Server 2005 instance.
On the Object Explorer windows (if not displayed by default, use F8 to open), open context menu by a right click on the "Databases" node of the SQL instance.
Select "New database..." menu item.
The New Database dialog wizard is displayed. The screen has three tabs which enables users to define a new sql database and configure it in detail.
But creating a databae is as simple as just typing the database name in the database name text area.
You can further define the initial sizes of your new database files, the file path of each sql data file or log file.
It is in the same screen where auto-growth properties and growth size can be declared.
You should check the Options tab to see what other sql database properties can be set using the GUI SSMS.
When configuring database properties is finished database creation will be processed when you click on the "OK" button.
"Create Database" Transact-SQL Command
CREATE DATABASE tsql command is used generally to create databases in MS SQL Server 2005.
Executing CREATE DATABASE will create data files and also database log files.
Each database has at least one data file (single primary data file with .mdf extension) and a log file (.ldf).
If you have additional data files these secondary data files will have .ndf file extension.
Here is the simplest usage of t-sql CREATE DATABASE syntax from MSDN.
The below tsql command will create a sql database on the SQL Server 2005 instance with default values for data file names, log file names, sql file paths, collation names, etc.
CREATE DATABASE [database_name]
If you execute the above tsql statement for creating a database in SQL Server, if you have the required permission the response of the SQL engine will probably as follows:
Command(s) completed successfully.
Basic Database Naming Rules :
The database name can not be more than 128 characters.
Better to avoid using spaces in SQL database names. But if you have to use, you should place the database name between "[" and "]" characters.
If you want more control over the database parameters like file path and file name, size, growth size, max size, etc. you should execute an extended CREATE DATABASE t-sql command.
This generally the case if you want to create a high performance sql database in your production area. You might want to place the data files and the log files to different disks and file groups, etc.
Here is an other example using a more general syntax of CREATE DATABASE sql command.
CREATE DATABASE MyTestDB
ON
PRIMARY
(
NAME = MyTestDB_PrimaryDataFile,
FILENAME = 'C:\SQLDatabases\MyTestDB_PrimaryDataFile.mdf'
, SIZE = 3 MB
, MAXSIZE = 10 MB
, FILEGROWTH = 10 %
),
(
NAME = MyTestDB_SecondaryDataFile,
FILENAME = 'C:\SQLDatabases\MyTestDB_SecondaryDataFile.ndf'
, SIZE = 1 MB
, MAXSIZE = 10 MB
, FILEGROWTH = 10 %
),
(
NAME = MyTestDB_SecondaryDataFile2,
FILENAME = 'C:\SQLDatabases\MyTestDB_SecondaryDataFile2.ndf'
, SIZE = 1 MB
, MAXSIZE = 10 MB
, FILEGROWTH = 10 %
)
LOG ON
(
NAME = MyTestDB_LogFile,
FILENAME = 'C:\SQLDatabases\MyTestDB_LogFile.ldf'
, SIZE = 2 MB
, MAXSIZE = 15 MB
, FILEGROWTH = 3 MB
)
COLLATE Latin1_General_CI_AS;