How to Attach and Detach Databases - SQL Server DBA Tutorial

In this video you will learn how to attach and detach databases using SQL Server Management Studio as well as using T-SQL script. After watching this video, you will also learn real time scenarios where it would be a good practice to attach and detach databases. 

Scripts used in this video

--Attach Script
USE [master]
GO

CREATE DATABASE [TestDB] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\DATA\TestDB.mdf' ),
( 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\DATA\TestDB_log.ldf' )
FOR ATTACH
GO


--Detach Script
USE [master]
GO

ALTER DATABASE [SalesOrders] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master]
GO

EXEC MASTER.dbo.sp_detach_db @dbname = N'SalesOrders'
GO

 
 
 
How to Attach and Detach Database in SQL Server

How to Create Database in SQL Server 2014 - SQL Server DBA Tutorial

In this video you will learn how to create database using SQL Server Management Studio as well as using T-SQL script. You will learn some of the best practices of creating SQL Server Databases and learn some of the important database options to consider while creating SQL Server Databases.


Scripts used in this video
USE [master]
GO

/****** Object: Database [SalesOrders] ******/
CREATE DATABASE [SalesOrders]
CONTAINMENT 
= NONE
ON  PRIMARY 
( NAME = N'SalesOrders', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\DATA\SalesOrders.mdf' , 
SIZE = 2048KB , MAXSIZE =UNLIMITED, FILEGROWTH = 1024KB )

LOG ON ( NAME = N'SalesOrders_log',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\DATA\SalesOrders.ldf' , 
SIZE = 1024KB , MAXSIZE =2048MB , 
FILEGROWTH = 10%)
GO


 
How to Create Database in SQL Server 2014

How to Shrink Database And Database Files - SQL Server DBA Tutorial

In this video you will learn how to shrink database, database files and database log files. It also demonstrates best practices of shrinking SQL Server Database data files, log files and database as a whole.

Scripts used in this video

--Shrink Database Script
USE [SalesOrders]
GO

DBCC SHRINKDATABASE(N'SalesOrders', 10 )
GO

--Shrink DB Files Scripts
USE [SalesOrders]
GO

DBCC SHRINKFILE (N'SalesOrders' , 3)
GO


 
Shrink Database files and Database in SQL Server

How to Take Database Offline and Bring it Online - SQL Server DBA Tutorial

In this video you will learn how to take database offline and bring database online using SQL Server Management Studio as well as using T-SQL script. It also demonstrates real time scenarios when it is best to attach and detach sql server databases.

Script used in this video

ALTER DATABASE [SalesOrders]
SET offline WITH ROLLBACK IMMEDIATE 
GO
ALTER DATABASE [SalesOrders] SET online;



 

How to Take Database Offline and Bring it Online

SQL Server 2014 Create Database Best Practices - SQL Server DBA Tutorial

In this video you will learn best practices of SQL Server Databases such as location of data and log files, initial size of the database, Auto growth options, collation, recovery models and some of the other important options of a database.


 
 Create Database Best Practices SQL Server

How to create Database Snapshot - SQL Server DBA Tutorial

In this video you will learn how to create sql server database snapshot using T-SQL Script, it also demonstrates how to view the snapshots using SQL Server Management Studio, how to restore the database from the snapshot, how to revert database using database snapshot. You will also learn some real time scenarios when it is best to use snapshot options.

Script used in this video

Create Snapshot
CREATE DATABASE SalesOrders_dbss ON( NAME = SalesOrders, FILENAME ='C:\Program Files\Microsoft SQL Server\SalesOrdersCurrent.ss' )
AS SNAPSHOT OF [SalesOrders];
GO

Restore Snapshot Script

USE MASTER;
RESTORE DATABASE [SalesOrders] 
FROM DATABASE_SNAPSHOT = 'SalesOrders_dbss';
GO


 
How to Create Database Snapshot in SQL Server

What are the different ways to set value of a Variable in TSQL

Variables are objects which hold the value in memory for us so we can use that value at different points in our program. TSQL also let us create variables like other programming languages.

Let's create a table and then insert few records for testing purpose, we will be using to test some scenarios how to set the value of variable from a table.

--Create Sample Table with Data  
CREATE TABLE [dbo].[VariableTestTable](
   
[id] [int] NULL,
   
[first_name] [varchar](50) NULL,
   
[last_name] [varchar](50) NULL,
   
[email] [varchar](50) NULL,
   
[country] [varchar](50) NULL
)
 
GO
 --Insert some Records 
SELECT *FROM   (SELECT N'1'                   AS [id],
               
N'Keith'               AS [first_name],
               
N'Welch'               AS [last_name],
               
N'kwelch0@auda.org.au' AS [email],
               
N'China'               AS [country]
        
UNION ALL
        
SELECT N'2'                 AS [id],
               
N'Nicholas'          AS [first_name],
               
N'Gomez'             AS [last_name],
               
N'ngomez1@yandex.ru' AS [email],
               
N'Peru'              AS [country]
        
UNION ALL
        
SELECT N'3'                 AS [id],
               
N'Phillip'           AS [first_name],
               
N'Bell'              AS [last_name],
               
N'pbell2@uol.com.br' AS [email],
               
N'Brazil'            AS [country]) t;

Create Variable:

To Create variable we have to use keyword Declare and then provide the name of variable and finally provide the data type of variable.

Declare @VariableName  DataType

Let's create a variable in which we can save First Name value. As we know that the First Name is string , so we will be declare a variable of Varchar ( String). Once we declare the variable , we will set the value by using SET or by using Select.

--Declare Variable 
  
DECLARE @FirstName VARCHAR(50) 
--SET the value of variable by using SET
  
SET @FirstName=(SELECT [first_name] FROM [dbo].[VariableTestTable]) 
--Print the value of Variable which is assigned by SET Keyword
  
PRINT @FirstName

  
--Set the value of variable by using SELECT
  
SELECT @FirstName=[first_name] FROM [dbo].[VariableTestTable]
 --Print the value of variable which is set by SELECT statement 
  
PRINT @FirstName


Let's run above statements and see what we get
Fig 1: Set the value of a Variable in TSQL 

As we can see that when we tried to set the value of variable by using SET keyword, it threw an error as the statement we used to set the value of variable was returning more than one value and we can not save those values in VARCHAR type variables.

When we used SELECT to save the value of variable, it kept executing the select and finally set the value of variable to last value it returned by Select statement which was Phillip in this case.

So we need to note two points: 
1- If we use SET and our query which is returning us value , will return more than one value, our statement will fail. Make sure we use correct query to return single value.

2-If we use Select then we should make sure , we use filters to return one required value, otherwise it will set the value to last value returned by Select statement.