About me (CV)
Video: Introduction to SQL Server (Bulgarian)
August 03, 2022
Nikolay Kostov (Nikolay.IT)
SQL Server Services
Types of Databases
Authentication and Permissions
SQL Server Management Studio
Moving a SQL Server Database
Through Backups and Restore
By Detaching and Attaching
Video (in Bulgarian)
What is Microsoft SQL Server?
_MS SQL Server _ is a Relational Database Management System (RDBMS) from Microsoft
The main language supported in SQL Server is Transact SQL (T-SQL), an extension of SQL
Powerful, trustworthy, easy-to-use DB server
The most recent version is SQL Server 2012
Works only on Windows systems
A free distribution exists (SQL Server Express)
Services of SQL Server 2012
SQL Server – the database engine
Responsible for database management, data storage, queries, data manipulation, data integrity, transactions, locking, users, security
Executes SQL / T-SQL queries
SQL Server Agent – DB monitoring
Executes scheduled tasks
Monitors SQL Server
Sends notifications about problems
Distributed Transaction Coordinator (MSDTC)
Manages database transactions
Supports transactions that span multiple databases
Coordinates committing the distributed transaction across all the servers that are enlisted in the transaction
Implements 2-phase commit
SQL Server Databases
SQL Server has system and user databases
Maintain internal information about MS SQL Server as a system
Don’t play with them!
Databases created by users (developers)
Store user’s schemas and data
Use the system databases internally
– meta-database keeping data about
Configurable environment variables
System error messages
– a prototype for new databases
– storage for temporary tables and database objects
– alerts and scheduled tasks
SQL Server Databases
Each SQL Server database consists of two files:
Contains the core data in the database
Schema, tables data, and other database objects
Transaction log – keeps track of transactions
You need both these files to use the database
Connecting to SQL Server
Connecting to SQL Server requires
The name of the server (e.g.
The name of the DB instance (e.g.
The name of the database (e.g.
Username / password (if using SQL Server authentication)
Types of authentication in SQL Server
Windows (by using a Windows user credentials)
Mixed (both Windows and SQL Server)
SQL Server Users Permissions
Each user has certain permissions and roles for a database (Database User Account)
A role defines a group of users with the same permissions
There are 3 types of roles in MS SQL Server
Fixed server roles
Fixed database roles
User-defined database roles
Fixed Database Roles
– maintains all default permissions for users in a database
– performs any database role activity
– add or remove database users, groups, and roles
– add, modify, or drop database objects
– assign statement and object permissions
SQL Server Management Studio (SSMS)
SQL Server Management Studio (SSMS) is a powerful graphical DB management tool
Administrate databases (create, modify, backup / restore DB)
Create and modify E/R diagrams
View / modify table data and other DB objects
Execute SQL queries
Free and easy to use tool
Works with all SQL Server versions
SSMS Setting Server Account
You can use SSMS to create database user / give permissions to Windows users
Follow these steps:
Right click on the [Security / Login] folder in Object Explorer and choose “New Login…”
In the next dialog click the [Search] button
Select one of the Windows accounts in a typical Windows fashion
Leave the authentication method set to Windows authentication
Thus you create an SQL Server User account
Account permissions could be assigned later
Windows administrators already have access
SSMS Setting Database Account
Right click on the “Security” under some of the databases and choose “New” “User”
Enter username and select one of the Server accounts to use
Assign the roles for this user
Click [OK] to confirm
By selecting the [Name-of-Database] “Properties” “Permissions” you can also set specific permissions for the accounts
Using SQL Server Management Studio
SSMS can be used to visually edit the structure or data in a database
It can execute T-SQL queries
Select the database you want to work with in the Object Explorer
Click the [New Query] button
Write the query in the window to the right of Object Explorer
Click the [Execute] button
Moving an SQL Server Database
Necessary when we install a certain application at the customer environment
Ways of moving an SQL Server database:
backup and restore
Create backup and restore it on the other server
By _detaching and attaching _ the database files
The 2 servers must be the same versions!
By dumping the database as
Not supported in SSMS
Moving DB by Detaching and Attaching
On the source server:
Choose the database in SQL Server Management Studio
From the context menu we choose the Detach command
We copy the database files from the source server to the destination server:
Download and install SQL Server Express. Install also SQL Server Management Studio Express (this could take some effort but be persistent).
Connect to the SQL Server with SQL Server Management Studio. Use Windows authentication.
Create a new database
and create new login with permissions to connect to it. Execute the script
to populate the DB contents (you may need slightly to edit the script before).
Attach the database
(use the files
) to SQL Server and connect to it.
Backup the database
into a file named
and restore it as database named
Export the entire
database as SQL script. Use [Tasks] -> [Generate Scripts]. Ensure you have exported table data rows (not only the schema).
Create a database
and execute the script in it to create the database and populate table data.
Detatch the database
and attach it on another computer in the training lab. In case of name collision, preliminary rename the database.
Download and install MySQL Community Server + MySQL Workbench + the sample databases.
Export the MySQL sample database "
" as SQL script.
Modify the script and execute it to restore the database world as "
Connect through the MySQL console client and list the first 20 tons from the database "