Create Database in MS SQL Server

Databases in Microsoft SQL Server are crucial for managing data, categorized into system databases, which are auto-created and user databases, created by users. In this article, We will learn about the basics of system and user databases along with methods for creating and managing them using T-SQL and SQL Server Management Studio (SSMS).

Create Database in SQL Server

• Creating a database in MS SQL Server is a fundamental task that involves setting up a structured environment to store, manage and retrieve data efficiently.
• Databases can be created using Transact-SQL (T-SQL) scripts or through the user-friendly interface of SQL Server Management Studio (SSMS), allowing users to customize their databases according to specific requirements.

Prerequisites

Databases are collections of objects like tables, views, stored procedures, functions, etc. In MS SQL Server, two sorts of databases are available.

1. System databases
2. User Databases

1. System Databases

System databases are created automatically once we install the MS SQL Server. Below is a list of system databases:

1. Master
2. Model
3. MSDB
4. Tempdb
5. Resource (Introduced in the 2005 version)
6. Distribution (It’s for the Replication feature only)

2. User Databases

User databases are created by users (DBAs, and testers who have access to create databases). To create a database, the below methods could be used:

1. SQL Server Management Studio.
2. Transact-SQL.

Method 1: Using T-SQL Script or Restore Database

The following can be the basic syntax for creating a database in MS SQL Server.

Restoring a database using a T-SQL script can be done in SQL Server Management Studio (SSMS) by executing a series of SQL commands. Here are the steps to restore a database using T-SQL script:

1. Open a new query window in SSMS by clicking on “New Query” in the toolbar.
2. Type the following command to restore a database from a backup file.
3. Replace “DatabaseName” with the name of the database you want to restore, and “C:\Path\To\BackupFile.bak” with the path to the backup file on your server.

Syntax:

Create database <yourdatabasename>

Method 2: Using SQL Server Management Studio (SSMS)

Microsoft offers SQL Server Management Studio (SSMS) as a tool for managing SQL Server databases. You can use it to carry out a number of tasks, including managing security, writing and running SQL queries, and creating and modifying tables.

The following are some fundamental actions to use SQL Server Management Studio:

1. Connect to a SQL Server instance: Launch SSMS and enter the server’s login information to establish a connection to a SQL Server instance. In accordance with your SQL Server configuration, you can select the authentication method.
2. Making a Database: In Object Explorer, select “New Database” from the context menu when you right-click on the “Databases” folder. Give the option a name, then set the other parameters to meet your needs.
3. Creating Tables: After creating a database, select “New Table” from the context menu when right-clicking on the Tables folder. Give the table a name and specify the columns and data types for it.
4. Writing and running SQL queries: SSMS’s Query Editor can be used to create SQL queries. By selecting “New Query” from the toolbar, you can open a new query window where you can enter your SQL code and hit “Execute” to run it.
5. Managing Security: You can control the security of your SQL Server instance using SSMS. You can manage security policies, add users and roles, and set permissions.

Steps for Using SQL Server Management Studio 

1. Start SQL Server Management Studio. The first time you run SSMS, the Connect to Server window opens. If it doesn’t open, you can open it manually by selecting Object Explorer > Connect> Database Engine.

 

2. Right-click Databases, and then click New Database.

3. After filling in all fields, select Connect. You can also select Options to change additional connection options. Examples of connection options include the database to connect to, connection timeout value, network protocol, and so on. This article uses default values ​​for all fields.

 

4. To verify that the SQL Server connection was successful, examine Object Explorer by expanding objects that display the server name, SQL Server version, and user name. These objects vary by server type.

 

5. To create the database with default values, click New Query.

 

Otherwise, continue with the following optional steps.

Query:

USE master
GO
IF NOT EXISTS (
   SELECT name
   FROM sys.databases
   WHERE name = N'TutorialDB'
)
CREATE DATABASE [TutorialDB]
GO

6. Execute the query by selecting Execute or selecting F5 on your keyboard.

 

Limitations

1. On a SQL Server instance, a maximum of 32,767 databases can be specified.

Recommendations

1. Whenever a user database is created, altered, or deleted, the master database should be backed up.
2. Create your database with the most extensive possible data files based on the maximum amount of data you anticipate storing there.

Using Transact-SQL

1. Connect to the Database Engine.
2. Open New Query.
3. The following example should be copied and pasted into the query window before choosing Execute. The database Sales is created in this example. The first file (Sales_dat) becomes the primary file because the keyword PRIMARY isn’t used. The Sales_dat file uses MB and is allocated in megabytes because MB or KB aren’t specified in the SIZE parameter for the file. Because the MB suffix is expressly stated in the SIZE parameter, the Sales_log file is allocated in megabytes.

Conclusion

Effective database management in MS SQL Server requires understanding both system and user databases. By using T-SQL scripts or SSMS, users can efficiently create and manage databases, adhering to best practices to ensure scalability and data integrity.