Chapter 1

Definition

Chapter 2

Download

Chapter 3

Install

Chapter 4

Running

Chapter 5

Entering

Chapter 6

Components

Chapter 7

Settings

Chapter 8

Tutorial

In the following, we are going to define SSMS from 8 different perspectives to have a better insight into this application tool.

The SQL Server Management Studio program, which is abbreviated as SSMS, is the SQL Server 2019 management tool.

SSMS is one of the SQL Server management tools, regardless of your location, used for designing queries and managing databases and data warehouses via personal computer or Cloud.

In reality, SSMS is an integrated environment that provides tools for configuring, monitoring, and administering SQL Server instances and databases.

SSMS also provides a complete and comprehensive tool that combines a vast group of graphical tools with script editors to provide access to SQL Server for database administrators and developers at all levels.

This tool includes many script editors (like T-SQL, MDX, DMX, and XML) and graphical tools that work with SQL Server objects and features.

This program is equal to Enterprise Manager and Query Analyzer in SQL Server 2000.

When you want to perform operations related to a database (implementing, developing, and managing) without coding and only by a few clicks of the mouse, you use this method.

For example, to create a database, you only have to right-click on the Database tool and select the “New Database” option.  In the opened window, you can write the database’s name and create a new database easily.

To create other objects, we nearly act like this.

Isn’t this wonderful?

From its 2005 version onward, this tool is known as the primary development tool for SQL Server, and the Query Editor T-SQL programming tool is also available in this SSMS tool.

SSMS is a free tool that you can easily download from Microsoft’s website via the address below.

The latest version available to the public is the 18.8 version.

If you are using an older version, it is recommended to always download and use the latest version to benefit from more facilities.

Download the latest versions of SQL Server Management Studio (SSMS) from Microsoft site (Indirect Download):

Figure 1: SQL Server Installation Center Page for Installing Management Tools

Figure 2: Download SSMS Management Tool

 the SSMS management tool must be installed separately.

Therefore, you must download it separately and then install it on the SQL Server database engine to manage the database and its objects via Wizard or Coding.

Installing this tool is very simple.

3 Steps to Install SSMS:

After receiving and executing the file, the following window will open.

In this step, you can change the SSMS installation path if necessary and then click on the Install option for installation.

The installation steps are easy and short and are illustrated in the below pictures, respectively.

Figure 3. SQL Server Management Tools Installation Window

Figure 4. SQL Server Management Tools Installation Progress Window

Figure 5. SQL Server Management Tools Installation Completed Window

After executing the above steps, the SSMS management tool will be installed.

If you want to see how to install SQL Server 2019 in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

Reference Article: How To Install SQL Server 2019: The Definitive Guide (2022)

It is clear that to make changes and configure SSMS, you first need to run it on your computer.

To run SSMS, in the Windows 10 environment, easily

If you are using an older version of Windows,

After running the program, you need to connect to it.

In this part, we want to describe how to connect to a SQL Server instance in the SSMS environment.

After running SSMS using one of these two methods, the SQL Server instance connection window opens up as illustrated in the figure below, and you can connect to a SQL Server instance.

Figure 6: “Connect to a SQL Server Instance” Window

If this window is not opened automatically, you can open it up manually from the Object Explorer > Connect > Database Engine path:

Figure 7: Manual Opening of the “Connect to Server” Window

After running the above program, you need to select which SQL Server you want to connect to from the Server Type expanding list.

If you want to access OLTP databases, you must select the Database Engine option.

And if you want to use OLAP databases, select the Analysis Services option.

If you want to learn about SSAS, I suggest you read the following reference article.

Everything you need to learn about SSAS is available in this article:

Reference Article: SQL Server Analysis Services (SSAS): The Definitive Guide (2022)

The name of the service provider’s instance will be determined in the Server Name section.

If you want to connect to a service provider installed on this computer, use the default server and do not change it.

The authentication method (determine User Name and Password to the service provider) is determined in the Authentication section. By default, when SQL Server is installed, it only accepts connections that use Windows Authentication (meaning that your Windows account is used for entering).

Since you are the user who has installed SQL Server, you have full access to the SQL Server instance.

You can change the default options by clicking on Options.

For example, in this section,

you can determine stuff such as which database to connect to by default,
what protocol to use for connecting to a network service provider,
what is the maximum waiting time (based on seconds) for connecting to a service provider,
and maximum waiting time (based on seconds) for running commands.

Now, Based on the above factors, follow these steps to connect to SSMS:

You will connect to your desired SQL Server instance by entering the server address and user information.

Note: In times that you want to connect to your outside database server or Host, you need to enter the server’s IP.

If you have successfully connected to the SQL Server instance, you will see the Object Explorer panel recommending multiple methods of database management to you.

Object Explorer will provide a tree view of all the database objects of a server.

Figure 8: Object Explorer Window

To make sure that your connection to SQL Server is done properly, try to expand the database objects using the Object Explorer window (click on the + alongside them).

Do not forget that these objects will be different based on the server type that you chose for connection.

Figure 9: Expanding Database Objects

To make sure that your connection to SQL Server is done properly, try to expand the database objects using the Object Explorer window (click on the + alongside them).

Do not forget that these objects will be different based on the server type that you chose for connection.

By doing this, you will enter the SSMS environment.

You can also use this environment (after entering SSMS) and the Object Explorer panel to disconnect from your current connection or connect to another instance.

Follow these steps to connect to a SQL Server instance using SSMS:

• Click on the Connect menu from the Object Explorer panel.
• Select the first option (Database Engine).
• “Connect to SQL Server Instance” window will appear once again.
• Like before, enter the needed information and click on the Connect.

Another method for connecting to another database or instance is to select the Explore Connect Object option from the File menu and enter the connection’s info.

Note: As mentioned before, there are two methods for connecting to a SQL Server instance (SQL Authentication and Windows Authentication) and we have used Windows Authentication in the examples presented in this article.

Nevertheless, SQL Azure DB does not support the Windows Authentication method and you can only use the SQL Authentication there.

From its 2005 version onward, this tool is known as the primary development tool for SQL Server, and the Query Editor T-SQL programming tool is also available in this SSMS tool.

Thus far, you must have run SSMS and connected to it through the “Connect to Server” window.

Like other Microsoft software environments, this tool has a set of components that we must get familiarized with them first.

In this section, we are going to get familiarized with some important SSMS components and configurations.

After connecting to SSMS, you will see a window like this:

Figure 10: SSMS Environment

As you see in the above figure, the SSMS window is created from multiple parts that we will describe them in the next section:

In this window, you can see a tree view of the database objects including:

• SQL Server Database Engine,
• SQL Server Analysis Serveries,
• SQL Server Reporting Services
• and SQL Server Integration Services of a server.

Object Explorer includes all the information related to all the servers that you have connected to.

if you have installed your SQL Server by following the orders of this article, you will see this section similar to the figure below.

Figure 11: Object Explorer

By clicking on the New Query option from the icons window (toolbar), a new environment opens up that can be used for writing T-SQL codes and is known as the Query Window.

This window includes two sections:

• in the top section, you can enter T-SQL queries
• and in the bottom section of the same window, you can see the results of the queries.

For example, by writing the below T-SQL command in the top section, all the records from the Databases’ table are retrieved and its output (retrieved rows) is shown in the bottom section:

When you open the Query window, you can also see the “Properties” window in which you can see the basic features of queries.

For example in this window, after the execution of a query, you can see the number of returned records and the connection details (figure 8).

Figure 12: Properties Window

In this window, you see a set of pre-built templates related to T-SQL (codes written in T-SQL).

You can perform many different operations on databases (like creating a database, working with indexes, and creating backups) by making slight changes in codes.

For example, by expanding the Backup option and selecting Backup Database, you can see the code needed for creating a backup from databases.

Here, only enough to make the needed changes in this code and use it based on your needs;

for example, insert the name of the database that you want to back up or change the backup directory.

Figure 13: Template Browser Window

This window shows more details in comparison with the Object Explorer.

You can use this window for manipulating different objects simultaneously.

For example, in this window, you can select multiple databases and delete them at the same time or run a script on them.

You cannot select multiple databases at the same time in the Object Explorer window.

Figure 14: Object Explorer Details Window

As you know, each software has its own primary settings that the user can change based on need.

You must use the “Options” settings box to apply the most SQL Server settings.

To view this box, just click on “Options” from the “Tools” menu to appear the following figure:

Figure 15: Options Settings Window

Now that you have access to the main settings section of SQL Server Management Studio, you can apply your intended changes in the program.

We will talk about some of the useful and practical settings.

Of course, remember that you can change many elements and options of SQL Server from the Options window. The following are just some of the needed settings to make your Coding process more pleasant based on your taste.

One of the features that can be changed in any software environment is the font and color used in that environment.

Probably each user is comfortable with a specific font or color. Selecting a comfortable font or color is rather important, especially for programmers who spend a lot of time coding.

If the default fonts and colors are not pleasant for you, you can change them by opening the Options section and selecting your desired Fonts and Colors.

You can change the text display style in the Management Studio by changing the different options of this section (figure 10).

Figure 16: Options Window (Font and Color Settings)

In the “Show Settings for” section, you can select that part of the “Management Studio” that you will change the texts displayed in it.

For example, Text Editor, as the section in which the codes are written, is the most used among the other options.

You can select your desired font from the Font section and change the text size from the Size section.

Notably, the default text size is very small and unreadable; you can increase it to your desired size in this section.

You can change the foreground and background colors from the Foreground and Background sections, respectively.

After applying the changes, if you want to revert everything to the default state, enough to click the “Use Default” option.

By default, you cannot see each line’s number beside it, but you can easily activate this feature from the Options window.

For this, select the “Text Editor” option from the left part of the window, then open “All Languages,” and then click on “General.”

In the end, you can activate this feature by ticking the “Line Numbers” option (figure 4.12).

Notably, viewing the line numbers is helpful in troubleshooting and tracking program errors.

Figure 17: Showing Line Numbers

In the “Show Settings for” section, you can select that part of the “Management Studio” that you will change the texts displayed in it.

For example, Text Editor, as the section in which the codes are written, is the most used among the other options.

You can select your desired font from the Font section and change the text size from the Size section.

Notably, the default text size is very small and unreadable; you can increase it to your desired size in this section.

You can change the foreground and background colors from the Foreground and Background sections, respectively.

After applying the changes, if you want to revert everything to the default state, enough to click the “Use Default” option.

IntelliSense is a feature that presents a Drop Down List to the users to complete their T-SQL command using similar sentences and helps the users find their intended command.

In simpler words, the system presents the commands matching what you have typed, and you can select your intended command from among the suggested commands.

In this situation, you no longer need to type the command entirely.

IntelliSense feature is only available in SQL Server 2008 services and the later versions and is always recommended to the users as a helpful tool.

This feature is active by default, but if for any reason you do not want to use it, you can easily deactivate it from the Options window.

For accessing the “IntelliSense” option, first, select the “Text Editor” option from the left window, then open the “Transact-SQL” option and select “IntelliSense.” Then you can deactivate this feature by removing the “Enable IntelliSense” tick.

Figure 7: Activating or Deactivating the IntelliSense Feature

As was said before, when you write and execute T-SQL codes in the “Query Window,” you will see the results (retrieved records) in the bottom part of this window.

Now a situation arises where you need to transfer the results of that query to software like Excel.

As you know, the query output will be in the form of a series of rows (records) and columns (field names):

Figure 7: Query Output

As you can see in the above example, this query output has five records and some fields (including NAME, DATABASE_ID, etc.).

Surely, in such a situation you would like to transfer the column names (fields) alongside the output records. Unfortunately, the capability of showing the columns’ names has not been provided in “Management Studio” by default, but you can simply activate it.

For this, open the Query Results option from the left-side list of the Options window, then select SQL Server, and finally, the Result to Grid option.

For showing the header name (fields names) in columns, activate the “Include column headers when copying or saving the results” option.

By doing this, when you copy query results, the column headers will also be copied.

Figure 7: Showing Column Headers while Copying the Query Results

When you are going to view and edit all the records of a table, you will face a limitation.

By default, just the first 1000 records of a table are shown, and you can only edit the first 200.

However, by changing the settings, you will be able to view and edit all the table records.

For this, you only have to open the SQL Server Object Explorer option and then click on Commands.

Then, by changing the “Table and View Options” values to zero, you will be able to view and edit all the records of a table.

Figure 7: Viewing and Editing all the Records of a Table

After performing these changes, you can right-click on a table and select “Edit All Rows” to view and edit all the records of that table.

SQL Server databases maximize the chance of tracing potential problems and documenting databases by recording different events and saving them as Logs.

Logs include all needed information about people who log in to the system, records added to the system, and all of their time and dates.

Although this information can be helpful in database owners, sometimes they can make the databases too large if there are space limitations in subscription host services.

If you are also using subscription-based host services and do not want Logs to make your database too large and lead to the suspension of the host service, you can simply change the Recovery Model option to Simple and minimize the volume of created logs.

For this, after connecting to the intended database, right-click on the databases and select Option, then change the Recovery Model option to Simple, and finally, press the Save option to implement desired changes.

By doing this, your database will create fewer logs and will not save all the events.

Be careful that some hosting companies do not allow this on their database servers; therefore, you need to change the database on your computer to Simple.

Figure 7: Needed Options for Minimizing Logs

In this section, we want to talk about changing the SSMS environment layout (including how to move windows).

To move a window, click and hold on its title and then Drag it around and Drop it on your intended location.

To pin or unpin a window, select the Pushpin icon available in the window’s title bar.

Figure 7: Pinning or Unpinning a Window

Every window has a Drop-Down Menu that allows you to manipulate that window in different ways.

Figure 7: Manipulating SSMS Environment Window

When two or multiple query windows are opened, they can be arranged horizontally or vertically, so that all of them could be visible at the same time.

For viewing the arranged windows, right-click on the title of the New Query window and select the arrangement option (horizontal or vertical).

Finally, to revert all of these options to their default state, enough to select the “Reset Window Layout” option from the Window menu:

Figure 7: Resetting the Made Changes to the Default State

As you know, SQL Server 2019 is a relational database management system that can have multiple instances.

Each instance can have multiple relational databases, and each database may consist of various tables; each table can also have a rational relation with other tables.
Using SQL Server Management Studio, we can access the available databases and objects in a SQL Server instance.

Anything inside a database, such as tables, views, indexes, saved procedures, etc., are all database objects.

In this section, we are going to describe the methods of creating databases and tables as well as how to create a connection between tables.

Nevertheless, before that, you need to get familiarized with different types of SQL Server databases.

SQL Server has two types of databases:

• Master: A fundamental systemic database that saves system-level information about users, different configuration settings, and even information about other SQL Server databases.
• Model: This is a template database; therefore, it includes default settings that will be implemented in every created database.
• MSBD: This database is used for timing operations and alerts using other tools such as SQL Server Management Studio, Service Broker, and Database Mail.
• Tempdb: This is temporary exclusive memory for SQL Server that can maintain temporary objects such as tables, saved procedures, etc.

Figure 7: Systemic Databases

These types of databases are those either come, as instances, with SQL Server (like pubs, northwind, and AdventureWorks) or are created by users based on their needs. (We will create a number of this type of database, later on.)

The first job in implementing the database system of a project is database creation.

After creating the database, you can make the other needed objects (like tables) inside it.

In the following, we want to describe the steps of creating a database.

In this section, we will implement a university-related database (called University) besides its three tables.

Follow these steps to create the University database:

Step 1: Run SQL Server Management Studio

Before creating the database, run SQL Server Management Studio.

Step 2: Right-Click on the Database Folder

Step 3: Select the “New Database…”

After running SQL Server Management Studio, right-click on the Database folder from the Object Explorer window and select the “New Database…” option from the opened menu.

If the Object Explorer window is not visible, select the “Option Explorer” option from the View menu (figure 21).

Figure 7: Creating a New Database

Step 4: Enter the name of your intended database 

Step 5: Click the OK 

The New Database page appears, set on the General tab by default.

Enter the name of your intended database in the Database name section and then click the OK button to create the database.

The list of the files related to this database is shown in the Database File section.

There are two files created by default:

• An MDF file for available information in the database
• and an LDF file (the Log file) for saving different events.

There needs to be at least one Log file in each database.

This file is a retrieval file and keeps the Log information required to retrieve a database after occurring an error.

Using the Add and Remove buttons at the bottom of this window, you can add or delete new files to the database.

The initial size of a file (while loading) is determined in the Initial Size section. By default, the data files size is 8 megabytes. If you think your information volume is higher, you can grow this initial size.

Of course, the size of the files will grow automatically by locating the information in them. You can determine the amount and manner of growth in the “Autogrowth” section. After performing the needed changes, click on OK to create the database.

Figure 7: Creating a New Database

As you can see in the picture below, a database named University is added to the bottom of the Databases list (in the Object Explorer window).

If you have not viewed the database’s name after creating it, right-click on the Databases option and select Refresh (figure 23).

Figure 7: University Database

Note:

Default values while creating a database are taken from the Model database in the System Database section.

You can right-click on the Model database and select the Properties option to change these features. This means that by changing the attributes of the Model database, the default values will also change for defining the database you created.

Sometimes you want to have more control over the database that you have created. For example, you can determine stuff like the maximum size that the database can grow into or even the address (folder) of the primary data files and Logs. The main arguments that you can use are shown in Table 4-1:

After creating a database, now it is time to create tables in that database so that you can store data in them.

In other words, a database is a method of categorizing tables.

For example, we create a database for the university and insert all the tables needed for this system in that database. Then, we create another database for an online sales system and insert all the tables required for this system in that database.

This makes the management of tables easier because the tables related to each project are inserted in their specific database, so avoid any kind of interference.

In this section, we are going to create

• the Student,
• Course,
• and SC tables

in the University database.

Remember that all the tables related to this example are created in the same database (the University database).

Follow these steps to create the Student, Course, and SC tables inside the University database:

• In the Object Explorer window, expand the Databases option (right-click on the + sign near this option) to view the list of created databases.
• To see the object available in the University database (including tables), click on the + sign next to University.
• To create a new table, right-click on the Tables option, select New, and finally click on Table (figure 24).

Figure 7: Creating a New Table

• First, you need to determine the table name.
  The table name is determined in the Properties window. If you cannot view the Properties window, select the Properties window option from the View menu (the F4 key).
  If you have not determined the table’s name, after defining the table and when saving this new table, the program will ask for a table name and you can determine the table name at this section.
• Double-click on the Name option (in the Properties window) and type Student.

Figure 7: Determining the Table Name in the Properties Window

• Click on the Column Name (in the middle window opened for creating a table) to define this table’s first field (column).
• The first field is the student number that we will name as StudentID. Enter the StudentID phrase.
  Note: While naming columns, try to avoid blank spaces. Either keep column names without blank spaces or use the underline (_) character instead of such spaces. Using blank spaces is completely allowed, but if they contain any spaces, when coding in T-SQL, you must surround the names with brackets that is an arduous task.
• The next step is determining the data type entered to this field.
  Click on the Data Type column. An expanding list of all the different data types is one of the first sections of SQL Server that is presented to you for creating tables.
  Because of this, you do not need to memorize all the different data types available in SQL Server. By having all the listed necessary values, choosing the most appropriate one is simple (after finishing this exercise, you can see all the different data types available in SQL Server).
• Select the Int data type.
• Each field has another important characteristic (“Allow Null” field). In some fields, this checkbox is ticked but in some fields, it is not ticked. If it is ticked, the field will be allowed to store the Null value inside itself.
  By default, each defined field can receive the Null value (meaning that no data is stored in it).
  To better understand this concept, consider the Amazon website for example. To register on this website, a form is presented to you with some mandatory fields (e.g. name and last name) and some optional fields (e.g. phone number). Here, the database designer has removed the Null tick for first name and surname fields but left it there for the phone number field. Since in this table (Students Table) there must be a student number value then the Allow Nulls characteristic should be equal to No (Click on the Allow Nulls check box to remove the tick from the square in front of it).
  Note: We will completely discuss the matters related to the constraints that can be implemented on the fields of a table (including Nullable and Un-nullable Primary and Foreign Keys, Index, etc.) in Chapter 5 (T-SQL).
• We are going to define the StudentID field of the Student table as a Primary Key. We select the StudentID field and click on it, then select the “Set Primary Key” option. A yellow key symbol appears on the left side of this field. Therefore, the StudentID field becomes the Primary Key of the Student table.
• We enter the rest of the fields as follows.

Figure 7: Creating the Student Table

• Click on the Save button.
• Then create the Course and SC tables following the same steps. The field specifications of these two tables are as follows:

Figure 7: Creating the Course Table

Figure 7: Creating the SC Table

As you can see, we use the Student (for saving student information), Course (for saving course information), and SC (for creating a connection between the Student and Course tables) tables in this simple project.

Finally, when these aforementioned tables are created, their names must appear in the Tables (in the Object Explorer window) section (figure 4-29):

Figure 7: List of Available Tables in the University Database

Table 4-2 is not a complete list of SQL Server data types but you should focus on the data types that you might use in your own projects.

Note: Table 4-2 describes the different data types available in SQL Server 2019. Of course, these data types are available in the older versions of SQL Server as well.

To summarize Table 4-2, we only wrote the most commonly used data types under the “Data Type” header while some similar data types are explained under the “Description and Notes” header. Remember this list, but you need to have an idea about the available data types.

Table 4-2: Different SQL Server data types

Notes about Using Different Data Types:

• It is better to use Nchar and Nvarchar for saving variable data.
• It is better to use Char and Varchar for data with fixed values because this will be much faster.
• Text and Ntext are used for data with more than 8000 characters.
• Nchar, Nvarchar, and Ntext Unicode data types should be used just when special characters may present in the data.

In the previous sections, we finished the tables creation process, but this is not the end of the road.

If you look closely at the exercise, you can see that just three tables are created; each task is to save specified data.

For example, the Student table for saving the student’s personal information and the Course table for saving course details.

However, in the real world (university environment), these two entities are connected.

For example, it needs to be specified what courses each student has chosen (here, for simplicity, we only consider the choice of courses by students).

This means that if you want to design a database for a specific environment, you need to understand and know the connection(s) between tables and implement them.

We will create a new relationship between the Student and Course tables in the following exercise.

If the multiplicity of the relationship between two tables is N: N (Many-to-Many), we will need three tables.

In this example, we build separate tables for each of the Student and Course entities (Student table and Course table), and to make a relationship between them, create a third table (SC).

Then, put the Primary Keys of the Student and Course tables as the Foreign Keys in the third table; therefore, we create a relationship between the two tables (the SC table with the two previously created tables).

In the upcoming exercise, we want to analyze the method of creating a relationship between tables.

You can create multiple databases using the Database Diagram branch. The relationship between tables becomes clearer using these diagrams. Follow the following steps to create a relationship between the Student and Course tables:

• Expand the University database (click the + sign beside it).
• To create a new diagram, right-click on the Database Diagram option and select New Database Diagram.

Figure 7: Creating a New Diagram

• By doing so, a new window will be opened in which you must click on Yes.

• Now, a list of tables existing in the database appears. Select the Student, Course, and SC tables and click on Add (figure 31).

Figure 7: Selecting the Tables to Create the Connections among Them

• As you can see, the tables have been added to the diagram view (figure 32).

Figure 7: Tables in the Diagram View

By right-clicking on the diagram and selecting the New Text Annotation option, you can write text-based descriptions on the diagram.

Using this diagram, the database structure and the relationship between the tables become very clear. In this way, you can create multiple diagrams and show the relationship among them.

By right-clicking on the diagram and selecting New Table, you can add another table to this diagram and by clicking on each table and selecting the “Remove from Diagram” option, you can delete it from the diagram.

• To create a relationship between the tables, you must drag the Student table’s Primary Key (StudentID) using your mouse and drop it on the SC table.
  In this situation, a window similar to figure 33 opens up and asks you for determining the Primary Key (in this example, the StudentID field from the Student table) and the Foreign Key (StudentID field from the SC table).
  If the name of the Primary Key and the Foreign Key is the same in both the tables, they will be selected automatically.
  Remember that the Primary and Foreign Key fields can have different names but they must have the same data type.
  In this example, since the Primary Key is in the Student table, we select the Student table in the Primary Key Table and the SC table in the Foreign Key Table and click on OK (Figure 33).

Figure 7: Creating a connection between Student and SC tables

• Next, in the “Database Designer” section of the window, in the message-box related to the Foreign Key in which we have created the relationship, you can set some options for the Foreign Key.
  We will discuss all of these options later on (Figure 4.34):

Figure 7: Configuring Foreign Key Options

• Perform the operation on the Course and SC tables, same as what we have done on the Student and SC tables for creating relationship. With this difference that this time, drag the Course table’s Primary key (CourseID) towards the SC table (figures 4.36 and 4.37).

Figure 7: Setting the Delete Rule Option

Figure 7: : Setting the Update Rule Option

• First, you need to determine the table name.
  The table name is determined in the Properties window. If you cannot view the Properties window, select the Properties window option from the View menu (the F4 key).
  If you have not determined the table’s name, after defining the table and when saving this new table, the program will ask for a table name and you can determine the table name at this section.
• Double-click on the Name option (in the Properties window) and type Student.

Figure 7: : Creating a Relationship between the Course and SC Tables

Figure 7: Setting the Foreign Key Options

Finally, after performing the above operation, the relationship among the Student, Course and SC tables will be created (figure 4.39).

Figure 7: Final Diagram

Finally, click on the Save button to save the diagram.

Figure 7: Selecting a Name for the Diagram

As you can see, in addition to the tables, the diagram that you just created has been added to the University database (in the Database Diagrams section).

Now you can double-click on the diagram, to view it, add things to it, or change it based on your taste (figure 4-40).

Figure 7: The Diagram is Added to the Database Diagrams Section

Sometimes data may be destroyed because of different reasons such as hardware (or software) errors, programmer’s mistake, being hacked, etc. (And sometimes you need to move your database from one computer to another.)

Therefore, there must be a way to retrieve the latest version of it.

The best method is to make periodic backups from the data available in your database so that you can recover those data if some unexpected problems happen.

There are multiple methods to backup in SQL Server, we discuss them in the following (Table 3):

Table 4-2: Different SQL Server data types

Note: As was said before, when you create a database, two files are created by default; one is for data with an MDF suffix and the other is for LOGs with an LDF suffix.

It is possible to create more than one file for each of these.

Using more than one file in the situation of having multiple hard disks (alongside a proper design) can highly increase your processing speed.

When the files in your database are more than one, you can use the File/Filegroup and File/Differential backup methods.

Besides all the backup methods that have already been explained, this can be done manually or automatically.

Further on, we are going to discuss both of these methods in the format of exercises.

First, we will discuss manual backups and then automatic ones.

Follow these steps to manually backup:

Figure 7: Selecting the Back-Up Option to Make a Backup

• Databases: Determines the intended database for backup (here is University).
• Backup Type: Determines the backup type. Since in this example, we want to create a backup from the entire database, we select the Full option.
• Backup Component: We selected the intended component for backup. Here, we select the Database option.
• Backup To: Here you can determine the backup file save path. By default, the backup file will be saved in the SQL Server installation path inside the Backup file as a single file (of course, you can change this path). You can create the backup file in multiple separate-but-dependent files (you need all the files for restoration). You can delete the default backup path (by clicking on the Remove button) and then add a new path by clicking on Add… and determine the backup file save path by selecting the path and writing the file name in the File Name section.

Figure 7: General Options

As was said before, Select a Page has the two General and Options sections; we talked about the General section before. Now it is time to discuss the Options section. You only have to select Backup Options from the Select a Page section of the Back-Up Database window to view and set the options related to it:

• Name: Here you can select a name for the backup version. Of course, it has a default name that can be changed.
• Backup set will expire: Here we can determine an expiration date for the backup. This part has two options: If you want this backup to be expired after a specified number of days, you only have to write the number of days in the box in front of After (the default number is 0 meaning that the files will not be expired). If you want to determine the expiration as a date, you should select the On option.

Figure 7: Backup Options

You can create multiple databases using the Database Diagram branch. The relationship between tables becomes clearer using these diagrams. Follow the following steps to create a relationship between the Student and Course tables:

• Expand the University database (click the + sign beside it).
• To create a new diagram, right-click on the Database Diagram option and select New Database Diagram.

• By doing so, a new window will be opened in which you must click on Yes.

• Now, a list of tables existing in the database appears. Select the Student, Course, and SC tables and click on Add (figure 31).

• As you can see, the tables have been added to the diagram view (figure 32).

Figure 7: Tables in the Diagram View

By right-clicking on the diagram and selecting the New Text Annotation option, you can write text-based descriptions on the diagram.

Using this diagram, the database structure and the relationship between the tables become very clear. In this way, you can create multiple diagrams and show the relationship among them.

By right-clicking on the diagram and selecting New Table, you can add another table to this diagram and by clicking on each table and selecting the “Remove from Diagram” option, you can delete it from the diagram.

• To create a relationship between the tables, you must drag the Student table’s Primary Key (StudentID) using your mouse and drop it on the SC table.
  In this situation, a window similar to figure 33 opens up and asks you for determining the Primary Key (in this example, the StudentID field from the Student table) and the Foreign Key (StudentID field from the SC table).
  If the name of the Primary Key and the Foreign Key is the same in both the tables, they will be selected automatically.
  Remember that the Primary and Foreign Key fields can have different names but they must have the same data type.
  In this example, since the Primary Key is in the Student table, we select the Student table in the Primary Key Table and the SC table in the Foreign Key Table and click on OK (Figure 33).

Figure 7: Creating a connection between Student and SC tables

• Next, in the “Database Designer” section of the window, in the message-box related to the Foreign Key in which we have created the relationship, you can set some options for the Foreign Key.
  We will discuss all of these options later on (Figure 4.34):

Figure 7: Configuring Foreign Key Options

• Perform the operation on the Course and SC tables, same as what we have done on the Student and SC tables for creating relationship. With this difference that this time, drag the Course table’s Primary key (CourseID) towards the SC table (figures 4.36 and 4.37).

Figure 7: Setting the Delete Rule Option

Figure 7: : Setting the Update Rule Option

• First, you need to determine the table name.
  The table name is determined in the Properties window. If you cannot view the Properties window, select the Properties window option from the View menu (the F4 key).
  If you have not determined the table’s name, after defining the table and when saving this new table, the program will ask for a table name and you can determine the table name at this section.
• Double-click on the Name option (in the Properties window) and type Student.

Figure 7: : Creating a Relationship between the Course and SC Tables

Figure 7: Setting the Foreign Key Options

Finally, after performing the above operation, the relationship among the Student, Course and SC tables will be created (figure 4.39).

Figure 7: Final Diagram

Finally, click on the Save button to save the diagram.

Figure 7: Selecting a Name for the Diagram

As you can see, in addition to the tables, the diagram that you just created has been added to the University database (in the Database Diagrams section).

Now you can double-click on the diagram, to view it, add things to it, or change it based on your taste (figure 4-40).

Figure 7: The Diagram is Added to the Database Diagrams Section

In the last section, we review the database backup steps.

Now if we want to select the backed-up version (on that computer or another computer), we have to restore it.

Now, we are going to teach the method of restoring a backed-up version:

Figure 7: Selecting the “Restore Database…” Option

Figure 7: Restoring the Backup File

Figure 7: Adding the Backup File

Figure 7: Selecting the Backup File

Figure 7: Backup File Path

Figure 7: Last Step in Restoring the Backup File

Figure 7: Successful Restoration Operation

Figure 7: The Restored Database Added to the Database List

This article first appeared in Nill2Bill