DBA Hub

📋Steps in this guide1/15

MS SQL SERVER – Restore & Recover Database without Tail Backup

MS SQL SERVER – Restore & Recover Database without Tail Backup

sqlserver backupintermediate
by MSSQL
15 views
1

Overview

MS SQL SERVER – Restore & Recover Database without Tail Backup Pre-requisites 0. Verify Recovery Model
2

Section 2

0. Verify Recovery Model 1. Take Full Database Backup 2. Create Table DBA and Insert some data
Step 2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases where name = 'TEST'
GO

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
3

Section 3

2. Create Table DBA and Insert some data 3. Take T-LOG Backup 4. Insert into table DBA
Step 3

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 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');

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

Section 4

4. Insert into table DBA 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
Step 4

Code/Command (click line numbers to comment):

1
2
3
4
5
6
INSERT INTO DBA VALUES ('SREERAM','INFRA');

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
5

Section 5

6. Insert into table DBA 7. Take T-LOG Backup
Step 5

Code/Command (click line numbers to comment):

1
2
3
4
5
6
INSERT INTO DBA VALUES ('PAVAN','HADOOP');

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

Section 6

8. Insert into table DBA 9. Take Differential Database Backup
Step 6

Code/Command (click line numbers to comment):

1
2
3
4
5
6
INSERT INTO DBA VALUES ('SRINIVAS','MW');

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
7

Section 7

10. Insert into table DBA 11. Capture Table DBA total rows — 10 Rows 12. Take T-Log Backup
Step 7

Code/Command (click line numbers to comment):

1
2
3
INSERT INTO DBA VALUES ('RAVI','DEV');
INSERT INTO DBA VALUES ('RAMESH','DBA');
INSERT INTO DBA VALUES ('SRINIVAS','DBA');
8

Section 8

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 Backup Validation 13. List Backup
Step 8

Code/Command (click line numbers to comment):

1
2
3
4
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
9

Section 9

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
Step 9

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 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

--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';
10

Section 10

15. Check contents of backup – RESTORE HEADERONLY RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\FULL_DB_BACKUP_TEST_261120.bak'; RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DIFF_DB_BACKUP_TEST_261120.bak'; RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_NEW\DB_BACKUP_TEST_TLOG_261120.bak'; 16. Using SSMS (GUI)
Step 10

Code/Command (click line numbers to comment):

1
2
3
4
5
6
--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';
11

Section 11

16. Using SSMS (GUI) 17. Using T-SQL Drop database TEST; Restore & Recover Database
Step 11

Code/Command (click line numbers to comment):

1
Drop database TEST;
12

Section 12

Restore & Recover Database 18. Restore & Recover Database using SSMS (GUI) 19. Restore Database using T-SQL (WITH NO RECOVERY)
Step 12

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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
13

Section 13

-- 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)
Step 13

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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
14

Section 14

-- 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. Caution:
Step 14
15

Section 15

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!