Tag Archives: Verify Recovery Model

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/