Sql Server Management Studio User Manual
- Sql Server Management Studio Free
- 2008 Sql Server Management Studio User Manual
- Download Sql Server Management Studio
- Using Sql Server Management Studio
- Microsoft Sql Server Management Studio Guide
- Sql Server Management Studio Guide
- Microsoft Sql Server Management Studi…
- MS SQL Server
SQL Documentation. Databases Manage relational data with SQL databases in your datacenter or on Azure. Use machine learning and analytics to gain insights on SQL data. Databases SQL Azure SQL Database. Azure SQL Data Warehouse. SQL Server 2017. SQL Server 2016. Develop using SQL Server Data Tools for Visual Studio; Run SQL Server in an Azure VM; Quickly get started with code samples on GitHub; Build an app using SQL Server; Integrate SQL Server 2017 into your DevOps pipeline; Connect to SQL Server and SQL Database using Node.js, Java, C#, PHP, Python, and Ruby applications. Mar 12, 2019 To add your own user account, open the SQL Server Management Studio and expand the Security Logins folder. You will see all the current logins including sa and you can add a new login. To add a new login, right-click on the Logins folder and click New Login.
- MS SQL Server Useful Resources
- Selected Reading
This chapter introduces SQL Server, discusses its usage, advantages, versions, and components.
What is SQL Server?
It is a software, developed by Microsoft, which is implemented from the specification of RDBMS.
It is also an ORDBMS.
It is platform dependent.
It is both GUI and command based software.
It supports SQL (SEQUEL) language which is an IBM product, non-procedural, common database and case insensitive language.
Usage of SQL Server
- To create databases.
- To maintain databases.
- To analyze the data through SQL Server Analysis Services (SSAS).
- To generate reports through SQL Server Reporting Services (SSRS).
- To carry out ETL operations through SQL Server Integration Services (SSIS).
Versions of SQL Server
Version | Year | Code Name |
---|---|---|
6.0 | 1995 | SQL95 |
6.5 | 1996 | Hydra |
7.0 | 1998 | Sphinx |
8.0 (2000) | 2000 | Shiloh |
9.0 (2005) | 2005 | Yukon |
10.0 (2008) | 2008 | Katmai |
10.5 (2008 R2) | 2010 | Kilimanjaro |
11.0 (2012) | 2012 | Denali |
12 (2014) | 2014 | Hekaton (initially), SQL 14 (current) |
SQL Server Components
SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server.
Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc.
Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc.
Instance of SQL Server
- An instance is an installation of SQL Server.
- An instance is an exact copy of the same software.
- If we install 'n' times, then 'n' instances will be created.
- There are two types of instances in SQL Server a) Default b) Named.
- Only one default instance will be supported in one Server.
- Multiple named instances will be supported in one Server.
- Default instance will take the server name as Instance name.
- Default instance service name is MSSQLSERVER.
- 16 instances will be supported in 2000 version.
- 50 instances will supported in 2005 and later versions.
Advantages of Instances
- To install different versions in one machine.
- To reduce cost.
- To maintain production, development, and test environments separately.
- To reduce temporary database problems.
- To separate security privileges.
- To maintain standby server.
SQL Server is available in various editions. This chapter lists the multiple editions with its features.
Enterprise − This is the top-end edition with a full feature set.
Standard − This has less features than Enterprise, when there is no requirement of advanced features.
Workgroup − This is suitable for remote offices of a larger company.
Web − This is designed for web applications.
Developer − This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.
Express − This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.
Compact − This is free embedded database for mobile application development. The maximum size of the database is 4 GB.
Datacenter − The major change in new SQL Server 2008 R2 is Datacenter Edition. The Datacenter edition has no memory limitation and offers support for more than 25 instances.
Business Intelligence − Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations.
Enterprise Evaluation − The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it.
2005 | 2008 | 2008 R2 | 2012 | 2014 |
---|---|---|---|---|
Enterprise | Yes | Yes | Yes | Yes |
Standard | Yes | Yes | Yes | Yes |
Developer | Yes | Yes | Yes | Yes |
Workgroup | Yes | Yes | No | No |
Win Compact Edition - Mobile | Yes | Yes | Yes | Yes |
Enterprise Evaluation | Yes | Yes | Yes | Yes |
Express | Yes | Yes | Yes | Yes |
Web | Yes | Yes | Yes | |
Datacenter | No | No | ||
Business Intelligence | Yes |
SQL Server supports two types of installation −
- Standalone
- Cluster based
Checks
- Check RDP access for the server.
- Check OS bit, IP, domain of server.
- Check if your account is in admin group to run setup.exe file.
- Software location.
Requirements
- Which version, edition, SP and hotfix if any.
- Service accounts for database engine, agent, SSAS, SSIS, SSRS, if any.
- Named instance name if any.
- Location for binaries, system, user databases.
- Authentication mode.
- Collation setting.
- List of features.
Pre-requisites for 2005
- Setup support files.
- .net framework 2.0.
- SQL Server native client.
Pre-requisites for 2008&2008R2
- Setup support files.
- .net framework 3.5 SP1.
- SQL Server native client.
- Windows installer 4.5/later version.
Pre-requisites for 2012&2014
- Setup support files.
- .net framework 4.0.
- SQL Server native client.
- Windows installer 4.5/later version.
- Windows PowerShell 2.0.
Installation Steps
Step 1 − Download the Evaluation Edition from http://www.microsoft.com/download/en/details.aspx?id=29066
Once the software is downloaded, the following files will be available based on your download (32 or 64 bit) option.
ENUx86SQLFULL_x86_ENU_Core.box
ENUx86SQLFULL_x86_ENU_Install.exe
ENUx86SQLFULL_x86_ENU_Lang.box
OR
ENUx86SQLFULL_x64_ENU_Core.box
ENUx86SQLFULL_x64_ENU_Install.exe
ENUx86SQLFULL_x64_ENU_Lang.box
Note − X86 (32 bit) and X64 (64 bit)
Step 2 − Double-click the “SQLFULL_x86_ENU_Install.exe” or “SQLFULL_x64_ENU_Install.exe”, it will extract the required files for installation in the“SQLFULL_x86_ENU” or “SQLFULL_x86_ENU” folder respectively.
Step 3 − Click the “SQLFULL_x86_ENU” or “SQLFULL_x64_ENU_Install.exe” folder and double-click “SETUP” application.
For understanding, here we have used SQLFULL_x64_ENU_Install.exe software.
Step 4 − Once we click on 'setup' application, the following screen will open.
Step 5 − Click Installation which is on the left side of the above screen.
Step 6 − Click the first option of the right side seen on the above screen. The following screen will open.
Step 7 − Click OK and the following screen pops up.
Step 8 − Click Next to get the following screen.
Step 9 − Make sure to check the product key selection and click Next.
Step 10 − Select the checkbox to accept the license option and click Next.
Step 11 − Select SQL Server feature installation option and click Next.
Step 12 − Select Database engine services checkbox and click Next.
Step 13 − Enter the named instance (here I used TestInstance) and click Next.
Step 14 − Click Next on the above screen and the following screen appears.
Step 15 − Select service account names and start-up types for the above listed services and click Collation.
Step 16 − Make sure the correct collation selection is checked and click Next.
Sql Server Management Studio Free
Step 17 − Make sure authentication mode selection and administrators are checked and click Data Directories.
Step 18 − Make sure to select the above directory locations and click Next. The following screen appears.
Step 19 − Click Next on the above screen.
Step 20 − Click Next on the above screen to the get the following screen.
Step 21 − Make sure to check the above selection correctly and click Install.
Installation is successful as shown in the above screen. Click Close to finish.
We have classified the architecture of SQL Server into the following parts for easy understanding −
- General architecture
- Memory architecture
- Data file architecture
- Log file architecture
General Architecture
Client − Where the request initiated.
Query − SQL query which is high level language.
Logical Units − Keywords, expressions and operators, etc.
N/W Packets − Network related code.
Protocols − In SQL Server we have 4 protocols.
Shared memory (for local connections and troubleshooting purpose).
Named pipes (for connections which are in LAN connectivity).
TCP/IP (for connections which are in WAN connectivity).
VIA-Virtual Interface Adapter (requires special hardware to set up by vendor and also deprecated from SQL 2012 version).
Server − Where SQL Services got installed and databases reside.
Relational Engine − This is where real execution will be done. It contains Query parser, Query optimizer and Query executor.
Query Parser (Command Parser) and Compiler (Translator) − This will check syntax of the query and it will convert the query to machine language.
Query Optimizer − It will prepare the execution plan as output by taking query, statistics and Algebrizer tree as input.
Execution Plan − It is like a roadmap, which contains the order of all the steps to be performed as part of the query execution.
Query Executor − This is where the query will be executed step by step with the help of execution plan and also the storage engine will be contacted.
Storage Engine − It is responsible for storage and retrieval of data on the storage system (disk, SAN, etc.,), data manipulation, locking and managing transactions.
SQL OS − This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure.
Checkpoint Process − Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk. Apart from this, it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.
It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each database individually. Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crashFailure.
Checkpoints in SQL Server
In SQL Server 2012 there are four types of checkpoints −
Automatic − This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval − Server Configuration Option.
Indirect − This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected, this will override the Recovery Interval specified for the server and avoid automatic checkpoint on such DB.
Manual − This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual checkpoint runs for your current database only. You can also specify the Checkpoint_Duration which is optional - this duration specifies the time in which you want your checkpoint to complete.
Internal − As a user you can’t control internal checkpoint. Issued on specific operations such as
Shutdown initiates a checkpoint operation on all databases except when shutdown is not clean (shutdown with nowait).
If the recovery model gets changed from FullBulk-logged to Simple.
While taking backup of the database.
If your DB is in simple recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
Alter database command to add or remove a datalog file also initiates a checkpoint.
Checkpoint also takes place when the recovery model of the DB is bulk-logged and a minimally logged operation is performed.
DB Snapshot creation.
Lazy Writer Process − Lazy writer will push dirty pages to disk for an entirely different reason, because it needs to free up memory in the buffer pool. This happens when SQL server comes under memory pressure. As far as I am aware, this is controlled by an internal process and there is no setting for it.
SQL server constantly monitors memory usage to assess resource contention (or availability); its job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers Lazy Writer to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.
If Lazy Writer is always active, it could indicate memory bottleneck.
Memory Architecture
Following are some of the salient features of memory architecture.
One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations.
Memory in windows can be called with Virtual Address Space, shared by Kernel mode (OS mode) and User mode (Application like SQL Server).
SQL Server 'User address space' is broken into two regions: MemToLeave and Buffer Pool.
Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during startup.
Buffer management is a key component in achieving I/O highly efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer pool to reduce database file I/O.
The buffer pool is further divided into multiple sections. The most important ones being the buffer cache (also referred to as data cache) and procedure cache. Buffer cache holds the data pages in memory so that frequently accessed data can be retrieved from cache. The alternative would be reading data pages from the disk. Reading data pages from cache optimizes performance by minimizing the number of required I/O operations which are inherently slower than retrieving data from the memory.
Procedure cache keeps the stored procedure and query execution plans to minimize the number of times that query plans have to be generated. You can find out information about the size and activity within the procedure cache using DBCC PROCCACHE statement.
Other portions of buffer pool include −
System level data structures − Holds SQL Server instance level data about databases and locks.
Log cache − Reserved for reading and writing transaction log pages.
Connection context − Each connection to the instance has a small area of memory to record the current state of the connection. This information includes stored procedure and user-defined function parameters, cursor positions and more.
Stack space − Windows allocates stack space for each thread started by SQL Server.
Data File Architecture
Data File architecture has the following components −
File Groups
Database files can be grouped together in file groups for allocation and administration purposes. No file can be a member of more than one file group. Log files are never part of a file group. Log space is managed separately from data space.
There are two types of file groups in SQL Server, Primary and User-defined. Primary file group contains the primary data file and any other files not specifically assigned to another file group. All pages for the system tables are allocated in the primary file group. User-defined file groups are any file groups specified using the file group keyword in create database or alter database statement.
One file group in each database operates as the default file group. When SQL Server allocates a page to a table or index for which no file group was specified when they were created, the pages are allocated from default file group. To switch the default file group from one file group to another file group, it should have db_owner fixed db role.
By default, primary file group is the default file group. User should have db_owner fixed database role in order to take backup of files and file groups individually.
Files
Databases have three types of files - Primary data file, Secondary data file, and Log file. Primary data file is the starting point of the database and points to the other files in the database.
Every database has one primary data file. We can give any extension for the primary data file but the recommended extension is .mdf. Secondary data file is a file other than the primary data file in that database. Some databases may have multiple secondary data files. Some databases may not have a single secondary data file. Recommended extension for secondary data file is .ndf.
Log files hold all of the log information used to recover the database. Database must have at least one log file. We can have multiple log files for one database. The recommended extension for log file is .ldf.
The location of all the files in a database are recorded in both master database and the primary file for the database. Most of the time, the database engine uses the file location from the master database.
Files have two names − Logical and Physical. Logical name is used to refer to the file in all T-SQL statements. Physical name is the OS_file_name, it must follow the rules of OS. Data and Log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems. There can be up to 32,767 files in one database.
Extents
Extents are basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages or 64KB. SQL Server has two types of extents - Uniform and Mixed. Uniform extents are made up of only single object. Mixed extents are shared by up to eight objects.
Pages
It is the fundamental unit of data storage in MS SQL Server. The size of the page is 8KB. The start of each page is 96 byte header used to store system information such as type of page, amount of free space on the page and object id of the object owning the page. There are 9 types of data pages in SQL Server.
Data − Data rows with all data except text, ntext and image data.
Index − Index entries.
TextImage − Text, image and ntext data.
GAM − Information about allocated extents.
SGAM − Information about allocated extents at system level.
Page Free Space (PFS) − Information about free space available on pages.
Index Allocation Map (IAM) − Information about extents used by a table or index.
Bulk Changed Map (BCM) − Information about extents modified by bulk operations since the last backup log statement.
Differential Changed Map (DCM) − Information about extents that have changed since the last backup database statement.
Log File Architecture
The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by Log Sequence Number (LSN). Each log record contains the ID of the transaction that it belongs to.
Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.
The steps to recover an operation depend on the type of log record −
- Logical operation logged.
- To roll the logical operation forward, the operation is performed again.
- To roll the logical operation back, the reverse logical operation is performed.
- Before and after image logged.
- To roll the operation forward, the after image is applied.
- To roll the operation back, the before image is applied.
Different types of operations are recorded in the transaction log. These operations include −
The start and end of each transaction.
Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
Every extent and page allocation or de allocation.
Creating or dropping a table or index.
Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. This reserved space is freed when the transaction is completed.
The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to a full recovery of the database. No part of the active log can ever be truncated. LSN of this first log record is known as the minimum recovery LSN (Min LSN).
The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.
The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size or number of virtual log files cannot be configured or set by administrators. The only time virtual log files affect system performance is if the physical log files are defined by small size and growth_increment values.
The size value is the initial size for the log file and the growth_increment value is the amount of space added to the file every time new space is required. If the log files grow to a large size because of many small increments, they will have many virtual log files. This can slow down database startup and also log backup and restore operations.
We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value. SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.
SQL Server Management Studio is a workstation componentclient tool that will be installed if we select workstation component in installation steps. This allows you to connect to and manage your SQL Server from a graphical interface instead of having to use the command line.
In order to connect to a remote instance of an SQL Server, you will need this or similar software. It is used by Administrators, Developers, Testers, etc.
The following methods are used to open SQL Server Management Studio.
First Method
Start → All Programs → MS SQL Server 2012 → SQL Server Management Studio
Second Method
Go to Run and type SQLWB (For 2005 Version) SSMS (For 2008 and Later Versions). Then click Enter.
SQL Server Management Studio will be open up as shown in the following snapshot in either of the above method.
A login is a simple credential for accessing SQL Server. For example, you provide your username and password when logging on to Windows or even your e-mail account. This username and password builds up the credentials. Therefore, credentials are simply a username and a password.
SQL Server allows four types of logins −
- A login based on Windows credentials.
- A login specific to SQL Server.
- A login mapped to a certificate.
- A login mapped to asymmetric key.
In this tutorial, we are interested in logins based on Windows Credentials and logins specific to SQL Server.
Logins based on Windows credentials allow you to log in to SQL Server using a Windows username and password. If you need to create your own credentials (username and password,) you can create a login specific to SQL Server.
To create, alter, or remove a SQL Server login, you can take one of two approaches −
- Using SQL Server Management Studio.
- Using T-SQL statements.
Following methods are used to create Login −
First Method – Using SQL Server Management Studio
Step 1 − After connecting to SQL Server Instance, expand logins folder as shown in the following snapshot.
Step 2 − Right-click on Logins, then click Newlogin and the following screen will open.
Step 3 − Fill the Login name, Password and Confirm password columns as shown in the above screen and then click OK.
Login will be created as shown in the following image.
Second Method – Using T-SQL Script
To create login name with TestLogin and password ‘P@ssword’ run below the following query.
Database is a collection of objects such as table, view, stored procedure, function, trigger, etc.
In MS SQL Server, two types of databases are available.
- System databases
- User Databases
System Databases
System databases are created automatically when we install MS SQL Server. Following is a list of system databases −
- Master
- Model
- MSDB
- Tempdb
- Resource (Introduced in 2005 version)
- Distribution (It’s for Replication feature only)
User Databases
User databases are created by users (Administrators, developers, and testers who have access to create databases).
Dell optiplex 990 mt user manual. Following methods are used to create user database.
Method 1 – Using T-SQL Script or Restore Database
Following is the basic syntax for creating database in MS SQL Server.
OR
Example
To create database called ‘Testdb’, run the following query.
OR
Note − D:backup is location of backup file and Testdb_full_backup.bak is the backup file name
Method 2 – Using SQL Server Management Studio
Connect to SQL Server instance and right-click on the databases folder. Click on new database and the following screen will appear.
Enter the database name field with your database name (example: to create database with the name ‘Testdb’) and click OK. Testdb database will be created as shown in the following snapshot.
Select your database based on your action before going ahead with any of the following methods.
Method 1 – Using SQL Server Management Studio
Example
To run a query to select backup history on database called ‘msdb’, select the msdb database as shown in the following snapshot.
Method 2 – Using T-SQL Script
Example
To run your query to select backup history on database called ‘msdb’, select the msdb database by executing the following query.
The query will open msdb database. You can execute the following query to select backup history.
To remove your database from MS SQL Server, use drop database command. Following two methods can be used for this purpose.
Method 1 – Using T-SQL Script
Following is the basic syntax for removing database from MS SQL Server.
Example
To remove database name ‘Testdb’, run the following query.
Method 2 – Using MS SQL Server Management Studio
Connect to SQL Server and right-click the database you want to remove. Click delete command and the following screen will appear.
Click OK to remove the database (in this example, the name is Testdb as shown in the above screen) from MS SQL Server.
Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log.
Backup database can be done using either of the following two methods.
Method 1 – Using T-SQL
Full Type
Differential Type
Log Type
Example
The following command is used for full backup database called 'TestDB' to the location 'D:' with backup file name 'TestDB_Full.bak'
The following command is used for differential backup database called 'TestDB' to the location 'D:' with backup file name 'TestDB_diff.bak'
The following command is used for Log backup database called 'TestDB' to the location 'D:' with backup file name 'TestDB_log.trn'
Method 2 – Using SSMS (SQL SERVER Management Studio)
Step 1 − Connect to database instance named 'TESTINSTANCE' and expand databases folder as shown in the following snapshot.
Step 2 − Right-click on 'TestDB' database and select tasks. Click Backup and the following screen will appear.
Step 3 − Select backup type (Fulldifflog) and make sure to check destination path which is where the backup file will be created. Select options at the top left corner to see the following screen.
Step 4 − Click OK to create 'TestDB' database full backup as shown in the following snapshot.
Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database.
The Restore database option can be done using either of the following two methods.
Method 1 – T-SQL
Syntax
Example
The following command is used to restore database called 'TestDB' with backup file name 'TestDB_Full.bak' which is available in 'D:' location if you are overwriting the existed database.
If you are creating a new database with this restore command and there is no similar path of data, log files in target server, then use move option like the following command.
Make sure the D:Data path exists as used in the following command for data and log files.
Method 2 – SSMS (SQL SERVER Management Studio)
Step 1 − Connect to database instance named 'TESTINSTANCE' and right-click on databases folder. Click Restore database as shown in the following snapshot.
Step 2 − Select device radio button and click on ellipse to select the backup file as shown in the following snapshot.
Step 3 − Click OK and the following screen pops up.
Step 4 − Select Files option which is on the top left corner as shown in the following snapshot.
Step 5 − Select Options which is on the top left corner and click OK to restore 'TestDB' database as shown in the following snapshot.
User refers to an account in MS SQL Server database which is used to access database.
Users can be created using either of the following two methods.
Method 1 – Using T-SQL
Syntax
Example
To create user name 'TestUser' with mapping to Login name 'TestLogin' in TestDB database, run the following query.
Where 'TestLogin' is the login name which was created as part of the Login creation
Method 2 – Using SSMS (SQL Server Management Studio)
Note − First we have to create Login with any name before creating a user account.
Let’s use Login name called 'TestLogin'.
Step 1 − Connect SQL Server and expand databases folder. Then expand database called 'TestDB' where we are going to create the user account and expand the security folder. Right-click on users and click on the new user to see the following screen.
Step 2 − Enter 'TestUser' in the user name field and click on ellipse to select the Login name called 'TestLogin' as shown in the following snapshot.
Step 3 − Click OK to display login name. Again click OK to create 'TestUser' user as shown in the following snapshot.
Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server.
To assign permissions either of the following two methods can be used.
Method 1 – Using T-SQL
Syntax
Example
To assign select permission to a user called 'TestUser' on object called 'TestTable' in 'TestDB' database, run the following query.
Method 2 – Using SSMS (SQL Server Management Studio)
Step 1 − Connect to instance and expand folders as shown in the following snapshot.
Step 2 − Right-click on TestUser and click Properties. The following screen appears.
Step 3 Click Search and select specific options. Click Object types, select tables and click browse. Select 'TestTable' and click OK. The following screen appears.
Step 4 Select checkbox for Grant column under Select permission and click OK as shown in the above snapshot.
Step 5 Select permission on 'TestTable' of TestDB database granted to 'TestUser'. Click OK.
Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc.
SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc.
Database status can be checked whether it is online or in any other state as shown in the following snapshot.
As per the above screen, all databases are in 'Online' status. If any database is in any other state, then that state will be shown as shown in the following snapshot.
MS SQL Server provides the following two services which is mandatory for databases creation and maintenance. Other add-on services available for different purposes are also listed.
- SQL Server
- SQL Server Agent
Other Services
- SQL Server Browser
- SQL Server Full Text Search
- SQL Server Integration Services
- SQL Server Reporting Services
- SQL Server Analysis Services
The above services can be availed using the following method.
Start Services
To start any of the services, either of the following two methods can be used.
Method 1 – Services.msc
Step 1 − Go to Run, type services.msc and click OK. The following screen appears.
Step 2 − To start service, right-click on service, click Start button. Services will start as shown in the following snapshot.
Method 2 – SQL Server Configuration Manager
Step 1 − Open configuration manager using the following process.
Start → All Programs → MS SQL Server 2012 → Configuration Tools → SQL Server configuration manager.
Step 2 − Select the service name, right-click and click on start option. Services will start as shown in the following snapshot.
Stop Services
To stop any of the services, either of the following three methods can be used.
Method 1 - Services.msc
Step 1 − Go to Run, type services.msc and click OK. The following screen appears.
Step 2 − To stop services, right-click on service and click Stop. The selected service will be stopped as shown in the following snapshot.
Method 2 – SQL Server Configuration Manager
Step 1 − Open configuration manager using the following process.
Start → All Programs → MS SQL Server 2012 → Configuration Tools → SQL Server configuration manager.
Step 2 − Select the service name, right-click and click Stop option. The selected service will be stopped as shown in the following snapshot.
Method 3 – SSMS (SQL Server Management Studio)
Step 1 − Connect to the instance as shown in the following snapshot.
Step 2 − Right-click on instance name and click Stop option. The following screen appears.
Step 3 − Click Yes button and the following screen will open.
Step 4 − Click Yes option on the above screen to stop SQL Server agent service. The services will be stopped as shown in the following screenshot.
Note
We cannot use the SQL Server Management Studio method to start the Services as unable to connect due to services already stopped state.
We cannot exclude stopping SQL Service agent service while stopping SQL Server service as SQL Server Agent Service is a dependent service.
High Availability (HA) is the solutionprocesstechnology to make the applicationdatabase available 24x7 under either planned or un-planned outages.
Mainly, there are five options in MS SQL Server to achievesetup high availability solution for the databases.
Replication
The source data will be copied to destination through replication agents (jobs). Object level technology.
Terminology
- Publisher is source server.
- Distributor is optional and stores replicated data for the subscriber.
- Subscriber is the destination server.
Log Shipping
The source data will be copied to destination through Transaction Log backup jobs. Database level technology.
2008 Sql Server Management Studio User Manual
Terminology
- Primary server is source server.
- Secondary server is destination server.
- Monitor server is optional and will be monitored by log shipping status.
Mirroring
The primary data will be copied to secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology.
Terminology
- Principal server is source server.
- Mirror server is destination server.
- Witness server is optional and used to make automatic failover.
Clustering
The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level technology. Windows Clustering setup is required with shared storage.
Terminology
- Active node is where SQL Services are running.
- Passive node is where SQL Services are not running.
AlwaysON Availability Groups
The primary data will be copied to secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without shared storage.
Terminology
- Primary replica is source server.
- Secondary replica is destination server.
Following are the steps to configure HA technology (Mirroring and Log shipping) except Clustering, AlwaysON Availability groups and Replication.
Step 1 − Take one full and one T-log backup of source database.
Example
To configure mirroringlog shipping for the database 'TestDB' in 'TESTINSTANCE' as primary and 'DEVINSTANCE' as secondary SQL Servers, write the following query to take full and T-log backups on Source (TESTINSTANCE) server.
Connect to 'TESTINSTANCE' SQL Server and open new query and write the following code and execute as shown in the following screenshot.
Step 2 − Copy the backup files to destination server.
In this case, we have only one physical server and two SQL Servers Instances installed, hence there is no need to copy, but if two SQL Server instances are in different physical server, we need to copy the following two files to any location of the secondary server where 'DEVINSTANCE' instance is installed.
Step 3 − Restore the database with backup files in destination server with 'norecovery' option.
Example
Connect to 'DEVINSTANCE' SQL Server and open New Query. Write the following code to restore the database with the name 'TestDB' which is the same name of primary database ('TestDB') for database mirroring. However, we can provide different name for log shipping configuration. In this case, let’s use 'TestDB' database name. Use 'norecovery' option for two (full and t-log backup files) restores.
Refresh the databases folder in 'DEVINSTANCE' server to see restored database 'TestDB' with restoring status as shown in the following snapshot.
Step 4 − Configure the HA (Log shipping, Mirroring) as per your requirement as shown in the following snapshot.
Example
Right-click on 'TestDB' database of 'TESTINSTANCE' SQL Server which is primary and click Properties. The following screen will appear.
Step 5 − Select the option called either 'Mirroring' or 'Transaction Log Shipping' which are in red color box as shown in the above screen as per your requirement and follow the wizard steps guided by system itself to complete configuration.
Report is a displayable component.
Usage
Report is basically used for two purposes - Company Internal Operations and Company External Operations.
Reporting Services
This is a service which is used to create and publish various kinds of reports.
Following are the three requirements necessary to develop any report.
- Business process
- Layout
- QueryProcedureView
The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are environment to develop reports.
Following are the steps to open BIDSSSDT environment to develop reports.
Step 1 − Open either BIDSSSDT based on the version from the Microsoft SQL Server programs group. The following screen will appear. In this case, SSDT has opened.
Step 2 − Go to file at the top left corner in the above screenshot. Click New and select project. The following screen will open.
Step 3 − In the above screen, select reporting services under business intelligence at the top left corner as shown in the following screenshot.
Step 4 − In the above screen, select either report server project wizard (it will guide you step by step through wizards) or report server project (it will be used to select customized settings) based on your requirement to develop the report.
Execution plan will be generated by Query optimizer with the help of statistics and Algebrizerprocessor tree. It is the result of Query optimizer and tells how to doperform your workrequirement.
There are two different execution plans - Estimated and Actual.
Estimated execution plan indicates optimizer view.
Actual execution plan indicates what executed the query and how was it done.
Execution plans are stored in memory called plan cache, hence can be reused. Each plan is stored once unless optimizer decides parallelism for the execution of the query.
There are three different formats of execution plans available in SQL Server - Graphical plans, Text plans, and XML plans.
SHOWPLAN is the permission which is required for the user who wants to see the execution plan.
Example 1
Following is the procedure to view the estimated execution plan.
Step 1 − Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name as shown in the following snapshot.
Step 2 − Click on New Query option on the above screen and write the following query. Before writing the query, select the database name. In this case, 'TestDB' is database name.
Step 3 − Click the symbol which is highlighted in red color box on the above screen to display the estimated execution plan as shown in the following screenshot.
Step 4 − Place the mouse on table scan which is the second symbol above the red color box in the above screen to display the estimated execution plan in detail. The following screenshot appears.
Example 2
Following is the procedure to view the actual execution plan.
Step 1 Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name.
Step 2 − Click New Query option seen on the above screen and write the following query. Before writing the query, select the database name. In this case, 'TestDB' is database name.
Step 3 − Click the symbol which is highlighted in red color box on the above screen and then execute the query to display the actual execution plan along with the query result as shown in the following screenshot.
Step 4 − Place the mouse on the table scan which is the second symbol above the red color box on the screen to display the actual execution plan in detail. The following screenshot appears.
Step 5 − Click Results which is on the left top corner on the above screen to get the following screen.
This service is used to carry out ETL (Extraction, Transform and Load data) and admin operations. The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are the environments to develop packages.
Download Sql Server Management Studio
SSIS Basic Architecture
Solution (Collection of projects) ---> Project (Collection of packages) ---> Package (Collection of tasks for ETL and admin operations)
Under Package, the following components are available −
- Control Flow (Containers and Tasks)
- Data Flow (Source, Transformations, Destinations)
- Event Handler (Sending of messages, Emails)
- Package Explorer (A single view for all in package)
- Parameters (User interaction)
Following are the steps to open BIDSSSDT.
Step 1 − Open either BIDSSSDT based on the version from the Microsoft SQL Server programs group. The following screen appears.
Step 2 − The above screen shows SSDT has opened. Go to file at the top left corner in the above image and click New. Select project and the following screen opens.
Step 3 − Select Integration Services under Business Intelligence on the top left corner in the above screen to get the following screen.
Step 4 − In the above screen, select either Integration Services Project or Integration Services Import Project Wizard based on your requirement to developcreate the package.
This service is used to analyze huge amounts of data and apply to business decisions. It is also used to create two or multidimensional business models.
In SQL Server 2000 version, it is called MSAS (Microsoft Analysis Services).
From SQL Server 2005, it is called SSAS (SQL Server Analysis Services).
Modes
There are two modes − Native Mode (SQL Server Mode) and Share Point Mode.
Models
There are two models − Tabular Model (For Team and Personal Analysis) and Multi Dimensions Model (For Corporate Analysis).
The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are environments to work with SSAS.
Step 1 − Open either BIDSSSDT based on the version from the Microsoft SQL Server programs group. The following screen will appear.
Using Sql Server Management Studio
Step 2 − The above screen shows SSDT has opened. Go to file on the top left corner in the above image and click New. Select project and the following screen opens.
Step 3 − Select Analysis Services in the above screen under Business Intelligence as seen on the top left corner. The following screen pops up.
Step 4 − In the above screen, select any one option from the listed five options based on your requirement to work with Analysis services.
-->This topic describes how to create a full database backup in SQL Server using SQL Server Management Studio, Transact-SQL, or PowerShell.
For information on SQL Server backup to the Azure Blob storage service, see SQL Server Backup and Restore with Azure Blob Storage Service and SQL Server Backup to URL.
Limitations and restrictions
- The
BACKUP
statement is not allowed in an explicit or implicit transaction. - Backups created by more recent version of SQL Server cannot be restored in earlier versions of SQL Server.
For an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.
Recommendations
- As a database increases in size, full database backups take more time to complete and require more storage space. For large databases, consider supplementing full database backups with a series of differential database backups.
- Estimate the size of a full database backup by using the sp_spaceused system stored procedure.
- By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you back up frequently, these success messages will accumulate quickly, resulting in huge error logs! This can make finding other messages difficult. In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).
Security
TRUSTWORTHY is set to OFF on a database backup. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).
Beginning with SQL Server 2012 (11.x), the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. You can still restore backups created with passwords.
Permissions
BACKUP DATABASE
and BACKUP LOG
permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Ownership and permission problems on the backup device's physical file can interfere with a backup operation. The SQL Server service must be able to read and write to the device which means that the account under which the SQL Server service runs must have write permissions to the backup device. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. As a result, problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.
Using SQL Server Management Studio
Microsoft Sql Server Management Studio Guide
Note
When you specify a backup task by using SQL Server Management Studio, you can generate the corresponding Transact-SQL BACKUP script by clicking the Script button and selecting a script destination.
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, and either select a user database or expand System Databases and select a system database.
Right-click the database that you wish to backup, point to Tasks, and then click Back Up...
In the Back Up Database dialog box, the database that you selected appears in the drop-down list (which you can change to any other database on the server).
In the Backup type drop-down list, select the desired backup type - the default is Full.
Important
You must perform at least one full database backup before you can perform a differential or a transaction log backup.
Under Backup component, select Database.
In the Destination section, review the default location for the backup file (in the ./mssql/data folder).
To backup to a different device, change the selection using the Back up to drop-down list. To stripe the backup set across multiple files for increased backup speed, click Add to add additional backup objects and/or destinations.
To remove a backup destination, select it and click Remove. To view the contents of an existing backup destination, select it and click Contents.
(optional) Review the other available settings under the Media Options and Backup Options pages.
For more information about the various backup options, see General page, Media options page, and Backup options page.
Click OK to initiate the backup.
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
Additional information
After creating a full database backup, you can create a differential database backup or a transaction log backup.
(optional) You can select the Copy-only backup checkbox to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. For more information, see Copy-Only Backups (SQL Server). A copy-only backup is not available for the Differential backup type.
The Overwrite media option is disabled on the Media Options page if you are backing up to a URL.
Examples
For the following examples, create a test database with the following Transact-SQL code:
A. Full back up to disk to default location
In this example, the SQLTestDB
database will be backed up to disk at the default backup location.
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up...Click OK.
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
B. Full back up to disk to non-default location
Sql Server Management Studio Guide
In this example, the SQLTestDB
database will be backed up to disk at a location of your choice.
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up...On the General page in the Destination section select Disk from the Back up to: drop-down list.
Select Remove until all existing backup files have been removed.
Select Add and the Select Backup Destination dialog box will open.
Enter a valid path and file name in the File name text box and use .bak as the extension to simplify the classification of this file.
Click OK and then click OK again to initiate the backup.
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
C. Create an encrypted backup
In this example, the SQLTestDB
database will be backed up with encryption to the default backup location.
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, expand System Databases, right-click
master
, and click New Query to open a query window with a connection to yourSQLTestDB
database.Execute the following commands to create a database master key and a certificate within the
master
database.In Object Explorer, in the Databases node, right-click
SQLTestDB
, point to Tasks, and then click Back Up...On the Media Options page, in the Overwrite media section select Back up to a new media set, and erase all existing backup sets.
On the Backup Options page in the Encryption section select the Encrypt backup check box.
From the Algorithm drop-down list, select AES 256.
From the Certificate or Asymmetric key drop-down list select
MyCertificate
.Select OK.
D. Back up to the Azure Blob storage service
The example below performs a full database backup of SQLTestDB
to the Azure Blob storage service. This example assumes that you already have a storage account with a blob container. This example creates a shared access signature for you; this example fails of the container has an existing shared access signature.
If you do not have an Azure blob container in a storage account, create one before continuing. For more information, see Create a general purpose storage account and Create a container.
After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree.
Expand Databases, right-click
SQLTestDB
, point to Tasks, and then click Back Up...On the General page in the Destination section select URL from the Back up to: drop-down list.
Click Add and the Select Backup Destination dialog box will open.
If you have previously registered the Azure storage container that you wish to use with SQL Server Management Studio, select it. Otherwise, click New container to register a new container.
In the Connect to a Microsoft Subscription dialog box, sign in to your account.
In the Select Storage Account drop-down text box, select your storage account.
In the Select Blob Container drop-down text box, select your blob container.
In the Shared Access Policy Expiration drop-down calendar box, select an expiration date for the shared access policy that you create in this example.
Click Create Credential to generate a shared access signature and credential in SQL Server Management Studio.
Click OK close the Connect to a Microsoft Subscription dialog box.
In the Backup File text box, modify the name of the backup file (optional).
Click OK to close the Select a backup destination dialog box.
Click OK to initiate the backup.
When the backup completes successfully, click OK to close the SQL Server Management Studio dialog box.
Using Transact-SQL
Create a full database backup by executing the BACKUP DATABASE
statement to create the full database backup, specifying:
- The name of the database to back up.
- The backup device where the full database backup is written.
The basic Transact-SQL syntax for a full database backup is:
BACKUP DATABASE databaseTO backup_device [ ,..n ][ WITH with_options [ ,..o ] ] ;
Option | Description |
---|---|
database | Is the database that is to be backed up. |
backup_device [ ,..n ] | Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option: { DISK TAPE } =physical_backup_device_name For more information, see Backup Devices (SQL Server). |
WITH with_options [ ,..o ] | Optionally, specifies one or more additional options, o. For information about some of the basic with options, see step 2. |
Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options, see BACKUP (Transact-SQL).
Basic backup set WITH options:
- { COMPRESSION NO_COMPRESSION }: In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.
- ENCRYPTION (ALGORITHM, SERVER CERTIFICATE ASYMMETRIC KEY): In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.
- DESCRIPTION= { 'text'@text_variable }: Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.
- NAME = { backup_set_name@backup_set_name_var }: Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
By default, BACKUP
appends the backup to an existing media set, preserving existing backup sets. To explicitly specify this, use the NOINIT
option. For information about appending to existing backup sets, see Media Sets, Media Families, and Backup Sets (SQL Server).
Alternatively, to format the backup media, use the FORMAT option:
FORMAT [ , MEDIANAME = { media_name@media_name_variable } ] [ , MEDIADESCRIPTION = { text@text_variable } ]
Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
Important
Use extreme caution when you are using the FORMAT clause of the BACKUP
statement because this destroys any backups that were previously stored on the backup media.
Microsoft Sql Server Management Studi…
Examples
For the following examples, create a test database with the following Transact-SQL code:
A. Back up to a disk device
The following example backs up the complete SQLTestDB
database to disk, by using FORMAT
to create a new media set.
B. Back up to a tape device
The following example backs up the complete SQLTestDB
database to tape, appending the backup to the previous backups.
C. Back up to a logical tape device
The following example creates a logical backup device for a tape drive. The example then backs up the complete SQLTestDB database to that device.
Using PowerShell
Use the Backup-SqlDatabase cmdlet. To explicitly indicate that this is a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.
Note
These examples require the SqlServer module. To determine if it is installed, run Get-Module -Name SqlServer
. To install this module, run Install-Module -Name SqlServer
in an administrator session of PowerShell.
For more information, see SQL Server PowerShell Provider.
Important
If you are opening a PowerShell window from within SQL Server Management Studio to connect to an installation of SQL Server, you can omit the credential portion of this example as your credential in SSMS is automatically used to establish the connection between PowerShell and your SQL Server instance.
Examples
A. Full backup (local)
The following example creates a full database backup of the <myDatabase>
database to the default backup location of the server instance ComputerInstance
. Optionally, this example specifies -BackupAction Database.
For the full syntax and additional examples, see Backup-SqlDatabase.
B. Full backup to Azure
The following example creates a full backup of the database <myDatabase>
on the <myServer>
instance to the Azure Blob Storage service. A stored access policy has been created with read, write, and list rights. The SQL Server credential, https://<myStorageAccount>.blob.core.windows.net/<myContainer>
, was created using a Shared Access Signature that is associated with the Stored Access Policy. The PowerShell command uses the BackupFile parameter to specify the location (URL) and the backup file name.