Category Archives: SQL Server

Restore Database

MS SQL SERVER – Restore & Recover Database

Table of Contents
___________________________________________________________________________________________________

Pre-requisites

0. Verify Recovery Model
1. Take Full Database Backup
2. Create Table DBA and Insert some data
3. Take T-LOG Backup
4. Insert into table DBA
5. Take T-LOG Backup
6. Insert into table DBA
7. Take T-LOG Backup
8. Insert into table DBA
9. Take Differential Database Backup
10. Insert into table DBA
11. Capture Table DBA total rows — 10 Rows
12. Take T-Log Backup

Backup Validation

13. List Backup
14. Validate Backup – RESTORE VERIFYONLY
15. Check contents of backup – RESTORE HEADERONLY

Delete Database — Test Simulation – Don’t try in office machines

16. Using SSMS (GUI)
17. Using T-SQL

Restore & Recover Database

18. Restore & Recover Database using SSMS (GUI)
19. Restore Database using T-SQL (WITH NO RECOVERY)
20. Recover Database using T-SQL (WITH RECOVERY)
21. Verify Table DBA total rows – 10 Rows

___________________________________________________________________________________________________


Pre-requisites


0. Verify Recovery Model

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases where name = 'TEST'
GO



1. Take Full Database Backup

BACKUP DATABASE [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak' 
WITH NAME = N'FULL DATABASE BACKUP - TEST',STATS 
GO


2. Create Table DBA and Insert some data

-- create table DBA
CREATE TABLE DBA 
(
NAME	VARCHAR(10),
ROLE	VARCHAR(10)
);
GO
-- Insert data to table 
INSERT INTO DBA VALUES ('SUGI','DBA');
INSERT INTO DBA VALUES ('TEJA','DBA');
INSERT INTO DBA VALUES ('BALA','DBA');
INSERT INTO DBA VALUES ('SURENDAR','DBA');



3. Take T-LOG Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO


4. Insert into table DBA

INSERT INTO DBA VALUES ('SREERAM','INFRA');



5. Take T-LOG Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO


6. Insert into table DBA

INSERT INTO DBA VALUES ('PAVAN','HADOOP');



7. Take T-LOG Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO


8. Insert into table DBA

INSERT INTO DBA VALUES ('SRINIVAS','MW');



9. Take Differential Database Backup

BACKUP DATABASE [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak' 
WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP TEST DATABASE',STATS 
GO


10. Insert into table DBA

INSERT INTO DBA VALUES ('RAVI','DEV');
INSERT INTO DBA VALUES ('RAMESH','DBA');
INSERT INTO DBA VALUES ('SRINIVAS','DBA');


11. Capture Table DBA total rows — 10 Rows




12. Take T-Log Backup

BACKUP LOG [TEST] 
TO DISK = N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak' 
WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO


Backup Validation

13. List Backup

-- List all database backups from last week.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 



14. Validate Backup – RESTORE VERIFYONLY

--Verify Full Backup
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'; 
-- Verify Diff Backup
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'; 
-- Verify T-LOG Backup 
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak'; 


15. Check contents of backup – RESTORE HEADERONLY


--Check Contents - Full Backup
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'; 
--Check Contents - Diff Backup
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'; 
--Check Contents - T-LOG Backup 
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak'; 



Delete Database — Test Simulation – Don’t try in office machines


16. Using SSMS (GUI)








17. Using T-SQL

Drop database TEST;




Restore & Recover Database


18. Restore & Recover Database using SSMS (GUI)


















19. Restore Database using T-SQL (WITH NO RECOVERY)

-- drop database test
DROP DATABASE TEST;





-- Restore Database from FULL DB Backup
RESTORE DATABASE TEST 
FROM DISK = N'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'
WITH FILE = 1,
NORECOVERY,
NOUNLOAD,
STATS = 5
GO



-- Restore Database from LAST DATABASE DIFFERENTIAL BACKUP
RESTORE DATABASE TEST 
FROM DISK = N'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'
WITH
NORECOVERY,
NOUNLOAD,
STATS = 5
GO


20. Recovery Database using T-SQL (WITH RECOVERY)

-- Recover Database using T-LOG Which was taken after LAST DB DEFFERENTAIL Backup 


-- DB Differential Backups always Cumulative (It will take backup from Last Full backup only, Not from Last Cumulative Backup)

No need to use T-LOG FILES 1,2 and 3 for recovery, because we took DB Differential Backup before T-LOG (4th time backup). Hence we need T-LOG File 4 only required for recovery. T-LOG 1,2 and 3 No more required.



RESTORE LOG TEST 
FROM DISK=N'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak'
WITH FILE = 4,
RECOVERY,
NOUNLOAD,
STATS = 5
GO


21. Verify Table DBA total rows – 10 Rows


Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Database Backup-FULL-DIFFERENTIAL-TLOG

MS SQL – Database Backup

Table of Contents
___________________________________________________________________________________________________

1. What is Full Database Backup

2. What is Differential Database Backup

3. What is Transaction Log Backup

4. Verify Recovery Model for Database

5. How to Take Full Database Backup

a) Using SSMS (GUI)
b) Using T-SQL

6. How to Take Differential Database Backup

a) Using SSMS (GUI)
b) Using T-SQL

7. How to Take Transaction Log Backup (T-LOG)

a) Using SSMS (GUI)
b) Using T-SQL

8. List Backup

9. Validate Backup – RESTORE VERIFYONLY

10. Check contents of backup – RESTORE HEADERONLY

___________________________________________________________________________________________________


1. How to Take Full Database Backup

This backs up the whole database. 
In order to have further differential or transaction log backups you have to create the full database backup first.

Syntax:

BACKUP DATABASE [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TEST.bak' WITH NAME = N'FULL DATABASE BACKUP - DBNAME',STATS 
GO


2. What is Differential Database Backup

Differential database backups are cumulative. This means that each differential database backup backs up the all the changes from the last Full database backup and NOT last Differential backup.

Syntax:

BACKUP DATABASE [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TEST.bak' WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP DBNAME DATABASE',STATS 
GO


3. What is Transaction Log Backup

It takes complete transaction log file 

Syntax:

BACKUP LOG [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TLOG.bak' WITH NAME= N'T-LOG BACKUP FOR DATABASE DBNAME',STATS 
GO


4. Verify Recovery Model for Database

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases
GO



5. How to Take Full Database Backup

a) Using SSMS (GUI)

      


b) Using T-SQL

BACKUP DATABASE [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak' WITH NAME = N'FULL DATABASE BACKUP - TEST',STATS 
GO




6. How to Take Differential Database Backup

a) Using SSMS (GUI)










b) Using T-SQL

BACKUP DATABASE [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak' WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP TEST DATABASE',STATS 
GO




7. How to Take Transaction Log Backup (T-LOG)

a) Using SSMS (GUI)















b) Using T-SQL

BACKUP LOG [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak' WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO




8. List Backup

-- List all database backups from last week.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 




9. Validate Backup – RESTORE VERIFYONLY

RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak'; 
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak'; 


10. Check contents of backup – RESTORE HEADERONLY

RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak'; 
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak'; 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

 

SQL Server – Recovery Models

MS SQL Server – Recovery models

Table of Contents
___________________________________________________________________________________________________

1. Types of Recovery Models

i) Simple recovery model            – Full and Differential backup
ii) Bulk-logged recovery model – Full, differential and log backup
iii) Full recovery model                 – Full, differential and log backup

2. Find recovery model
a) Using SSMS (GUI)
b) Using T-SQL

3. Change Recovery model
a) Using SSMS (GUI)
b) Using T-SQL

___________________________________________________________________________________________________


1. Types of Recovery Models

i) Simple recovery model

-- Simple recovery model every transaction will be logged into the transaction log file, but at regular intervals the transaction log file will be TRUNCATED whenever a CHECKPOINT operation occurs. 
-- Generally used in Development environment where Database Priority/Point-in-time priority is less
-- Not supported Transaction log backups
-- No Point-in-time recovery possible
-- Data loss chances are more


ii) Bulk-logged recovery model


-- In Bulk-logged recovery model every transaction will be logged into the transaction log file, but bulk insert operations are minimally logged.
-- Supports transaction log backups 
-- No automatic Truncate of Transaction log.
-- Chances of data loss if bulk insert operations fail
-- May or may not be possible to perform point-in-time recovery
-- Disk consumption will be high when normal transactions but in bulk Disk consumption will be low

iii) Full recovery model

-- In FULL recovery model every transaction will be logged into the transaction log file. 
-- This recovery model is generally used in Production databases
-- Supports transaction log backups 
-- No automatic Truncate of Transaction log.
-- Minimal/No Data Loss. 
-- Point-in-time Recovery
-- Performance Overhead and large transactions at times can take more time.
-- The downside of this model is that it can consume a lot of disk space very fast.
-- Make sure setup regular T-Log Backup, so that after T-Log backup it will truncate T-Log (space will be released)


2. Find recovery model

a) Using SSMS (GUI)

TEST (Database) -- Right Click --- Properties

b) Using T-SQL

USE master;
GO
select [name], DATABASEPROPERTYEX([name],'recovery') As RecoveryModel
from sysdatabases
where name  in ('master','model','tempdb','msdb','test')
GO


3. Change Recovery model

a) Using SSMS (GUI)


b) Using T-SQL

-- SET RECOVERY MODEL TO FULL
USE MASTER;
GO
ALTER DATABASE TEST SET RECOVERY FULL;
GO







-- SET RECOVERY MODEL TO SIMPLE
USE MASTER;
GO
ALTER DATABASE TEST SET RECOVERY SIMPLE;
GO

 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Authentication Method

Authentication Method

We recommend users to run their SQL Server with mixed authentication mode. To inspect the authentication mode of your SQL Server, follow screen shots below.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Allow Remote Connection

Allow Remote Connection

Your need to make sure that remote connection to your SQL Server is enabled. To verify this setting follow the screen shots below.


 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Enable TCP/IP Connection

Enable TCP/IP Connection

To check whether your server enabled to TCP/IP connections

Open SQL Server Configuration Manager and Follow screen shots.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Install Only SQL Server Management Studio

How to Install Only SQL Server Management Studio 2014

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Install Microsoft SQL Server Express 2014

Steps to Install Microsoft SQL Server Express 2014

Before you start, it’s highly recommend to check hardware and software requirements for Installing SQL Server 2014

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Create Database using GUI

Create Database using the SQL Server Management Studio GUI

SQL Server Version: 2016

1. Click on SQL Server Management Studio GUI



2. Login



3. Create New Database

Ensure that the correct server is expanded in the Object Explorer.









Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com