Restores concept
KDSSG B46 Level001 043 26Mar2024 Restores1
Restore Scenarios:-
1) Complete Database Restore (Point of Failure) ***
DB Crashed on Thursday 4:45PM IST
What is the backup strategy? What is your backup strategy?
Sun - Full 9AM, Daily - Diff 9AM, Tlog Backup every 1 hour.
Tail Log Backup - 04:45PM (45 Minutes of Log Records)
Sun - Full Backup 9AM, Thursday - Diff Backup 9AM, Tlog - 10AM, 11AM, 12PM, 1PM, 2PM, 3PM,
4PM,
Always take Tail Log Backup First - 04:45PM
2) Point In Time Restore
3) File/Filegroup Restore
4) Piece Meal Restore
5) Page Restore ***
1) Complete Database Restore (Point of Failure):
A complete database restore involves restoring a full database backup followed by restoring and
recovering a differential backup. Point of Failure restore can be done when database is crashed and
we will have to perform the restores with all available backups including tail log we have taken before
starting sequence of restores.
Full Backup - Sunday 9:00
Diff Backup - EveryDay 9:00
Tlog Backup - 1 Hour
Database Crashed on Wednesday at 3:45PM
1) Take Tail Log Backup (if successfull, last 45 minutes of log records can be backed up)
Full Backup - 1, Differential Backup - 1, Tlog Backups - 6, Tail Log Backup - 1
2) Restore Full Backup WITH NORECOVERY
3) Restore recent Diff Backup (Wednesday 9:00) WITH NORECOVERY
4) Restore all the Log Backups from 9:00 Wednesday to 15:00 WITH NORECOVERY
5) Restore Tail Log Backup WITH RECOVERY
Backup Steps
CREATE DATABASE FRIENDSHIP
USE FRIENDSHIP
GO
CREATE TABLE T1(Sno int, Sname varchar(50))
GO
INSERT INTO T1 VALUES (1,'T1')
GO 100001
--1,00,001 Records before Full Backup
BACKUP DATABASE Friendship
TO DISK=N'C:\Backups\Friendship_Full.bak'
INSERT INTO T1 VALUES (2,'T2')
INSERT INTO T1 VALUES (2,'T2')
--1,00,003 Records before Diff1 Backup, so Diff1 Backup takes --backup of two records
only.
BACKUP DATABASE Friendship
TO DISK=N'C:\Backups\Friendship_Diff1.bak'
WITH DIFFERENTIAL
INSERT INTO T1 VALUES (3,'T3')
INSERT INTO T1 VALUES (3,'T3')
--1,00,005 Records before Tlog1 Backup, so Log1 Backup takes --backup
BACKUP LOG Friendship
TO DISK=N'C:\Backups\Friendship_Log1.trn'
INSERT INTO T1 VALUES (4,'T4')
INSERT INTO T1 VALUES (4,'T4')
----1,00,007 Records -> Last two Log Records after Tlog1 and before Tlog2 Backup will
be backup up.
BACKUP LOG Friendship
TO DISK=N'C:\Backups\Friendship_Log2.trn'
INSERT INTO T1 VALUES (5,'T5')
INSERT INTO T1 VALUES (5,'T5')
BEGIN TRAN
INSERT INTO T1 VALUES (6,'T6')
INSERT INTO T1 VALUES (6,'T6')
--Corrupt the Database.
ALTER DATABASE Friendship SET OFFLINE
WITH ROLLBACK IMMEDIATE
Corrupted the MDF file @ OS level.
Delete database MDF file
ALTER DATABASE Friendship SET ONLINE
BACKUP LOG FriendShip
TO DISK='C:\Backups\FriendShip_Tail.trn' WITH NO_TRUNCAT
---:Restore Steps:---
RESTORE DATABASE [Friendship]
FROM DISK = N'C:\Backups\Friendship_Full.bak'
WITH MOVE N'Friendship' TO N'C:\Backup\Friendship.mdf',
MOVE N'Friendship_log' TO N'P:\Backup\Friendship.ldf',
NORECOVERY, REPLACE, STATS = 10
GO
RESTORE DATABASE [Friendship]
FROM DISK = N'D:\Backup\Friendship_Diff1.bak'
WITH NORECOVERY, STATS = 10
GO
RESTORE LOG [Friendship]
FROM DISK=N'D:\Backup\Friendship_Log1.trn'
WITH NORECOVERY, STATS = 10
GO
RESTORE LOG [Friendship]
FROM DISK=N'D:\Backup\Friendship_Log2.trn'
WITH NORECOVERY, STATS = 10
GO
RESTORE LOG [Friendship]
FROM DISK=N'D:\Backup\Friendship_Tail.trn'
WITH RECOVERY, STATS = 10
GO
RESTORE DATABASE [Friendship] WITH RECOVERY
Database Able to access
2) Point In Time Restore
KDSSG B46 Level001 044 27Mar2024 Restores2
KDSSG B46 Level001 043 26Mar2024 Restores1
Point in time restores can be done when database objects have been dropped and there is request to
restore specific objects. We will firt restore database and the object to another/same instance and
restore all backups using STOPAT, STOPBEFOREMARK and finally export/import objects to the
instance.
STEP:1 FIRST CREATE DATABASE
CREATE DATABASE QNXT_PLANDATA_UNV
GO
USE QNXT_PLANDATA_UNV
GO
STEP:2 CREATE TABLE AND INSERT SOME ROWS
CREATE TABLE QNXT_PLANDATA_UNV_PLAN_0 (sno int, sname varchar(50))
GO
USE QNXT_PLANDATA_UNV
GO
BEGIN TRAN
insert into QNXT_PLANDATA_UNV_PLAN_0 values (1,'Pit1')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (2,'Pit2')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (3,'Pit3')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (4,'Pit4')
COMMIT
STEP:3 TAKE FULL BACKUP
--12:34PM (Full Backup)
BACKUP DATABASE QNXT_PLANDATA_UNV TO DISK=N'D:\Backup\PIT_Full.bak'
--12:35PM 4 Inserts after Full
USE QNXT_PLANDATA_UNV
GO
BEGIN TRAN
insert into QNXT_PLANDATA_UNV_PLAN_0 values (5,'Pit5')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (6,'Pit6')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (7,'Pit7')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (8,'Pit8')
COMMIT
STEP:4 TAKE DIFF BACKUP
--12:36PM (Diff Backup)
backup database QNXT_PLANDATA_UNV to disk=N'D:\Backup\PIT_Diff.bak'
with differential
--12:37PM 4 Inserts after Diff
USE QNXT_PLANDATA_UNV
GO
BEGIN TRAN
insert into QNXT_PLANDATA_UNV_PLAN_0 values (9,'Pit9')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (10,'Pit10')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (11,'Pit11')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (12,'Pit12')
COMMIT
STEP:4 TAKE Tlog1 BACKUP
--12:39PM (TLog1 Backup)
backup log QNXT_PLANDATA_UNV to disk=N'D:\Backup\PIT_Tlog1.trn'
--12:40PM 4 Inserts after Tlog1
USE QNXT_PLANDATA_UNV
GO
BEGIN TRAN
insert into QNXT_PLANDATA_UNV_PLAN_0 values (13,'Pit13')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (14,'Pit14')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (15,'Pit15')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (16,'Pit16')
COMMIT
STEP:4 TAKE Tlog2 BACKUP
--12:41PM (TLog2 Backup)
backup log QNXT_PLANDATA_UNV to disk=N'D:\Backup\PIT_Tlog2.trn'
--12:42PM 4 Inserts after Tlog2
USE QNXT_PLANDATA_UNV
GO
BEGIN TRAN
insert into QNXT_PLANDATA_UNV_PLAN_0 values (17,'Pit17')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (18,'Pit18')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (19,'Pit19')
insert into QNXT_PLANDATA_UNV_PLAN_0 values (20,'Pit20')
COMMIT
STEP:5 --12:43PM Drop the Table.
USE QNXT_PLANDATA_UNV
GO
DROP TABLE QNXT_PLANDATA_UNV_PLAN_0
BEFORE DROP AFTER DROPPED TABLE
STEP:6 CREATE A NEW TABLE
USE QNXT_PLANDATA_UNV
GO
BEGIN TRAN
create table QNXT_PLANDATA_UNV_PLAN_0_PLAN (sno int, sname varchar(50))
insert into QNXT_PLANDATA_UNV_PLAN_0_PLAN values (1,'Pit1')
insert into QNXT_PLANDATA_UNV_PLAN_0_PLAN values (2,'Pit2')
insert into QNXT_PLANDATA_UNV_PLAN_0_PLAN values (3,'Pit3')
insert into QNXT_PLANDATA_UNV_PLAN_0_PLAN values (4,'Pit4')
COMMIT
--12:44PM STEP:7 (TLog3 Backup)
backup log QNXT_PLANDATA_UNV to disk=N'D:\Backup\PIT_Tlog3.trn'
After restoring based on timestamp provided by Application team member, if we restore all backups
and if Application Owner is not happy with the returned table and number of records in it. They may
provide one more estimated timeline. It is better to check from DBA end the exact LSN number of
Table Drop.
STEP:8 FIRST WE NEED TO RUN BELOW QUERY (WE WILL FIND WHO DROPPED TABLE)
select [Current LSN], [Operation], [Transaction ID], [Parent Transaction ID],
[Begin Time], [Transaction Name], suser_sname([Transaction SID]) Who_Dropped
from fn_dblog(null, null)
where [Operation] = 'LOP_BEGIN_XACT' and [Transaction Name]='DROPOBJ'
STEP:8 To find which table is dropped.
SELECT TOP (1) [Lock Information] FROM fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:000003d6'
AND [Lock Information] LIKE '%SCH_M OBJECT%'
STEP:9 If log file was truncated, then we can use fn_dump_dblog to retrieve
LSN information from Log Backup.
SELECT [Current LSN], [Operation], [Transaction ID], [Parent Transaction ID],
[Begin Time], [Transaction Name], [Transaction SID]
FROM fn_dump_dblog
(
DEFAULT, DEFAULT, DEFAULT, DEFAULT,
'D:\Backup\PIT_Tlog3.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
)
where OPERATION='LOP_BEGIN_XACT' and [Transaction Name]='DROPOBJ'
GO
STEP:10 For Point in Time recovery mention STOPAT parameter with appropriate
timeline (or) STOPBEFOREMARK with LSN Number.
USE [master]
RESTORE DATABASE [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Full.bak'
WITH
MOVE N'QNXT_PLANDATA_UNV' TO N'C:\Program Files\Microsoft SQL
Server\MSSQL16.SQL_SERVER_ENCRY\MSSQL\DATA\QNXT_PLANDATA_UNV_Copy.mdf',
MOVE N'QNXT_PLANDATA_UNV_log' TO N'C:\Program Files\Microsoft SQL
Server\MSSQL16.SQL_SERVER_ENCRY\MSSQL\DATA\QNXT_PLANDATA_UNV_Copy_log.ldf',
NORECOVERY, STATS = 5
RESTORE DATABASE [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Diff.bak' WITH
NORECOVERY
RESTORE LOG [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Tlog1.trn' WITH
NORECOVERY
RESTORE LOG [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Tlog2.trn' WITH
NORECOVERY
RESTORE LOG [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Tlog3.trn' WITH
RECOVERY,STOPAT = N'APRIL 05, 2024 15:30:17:860 PM'
***Last step export table QNXT_PLANDATA_UNV_COPY TO QNXT_PLANDATA_UNV***
--Restores to specific LSN Number.
RESTORE LOG [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Tlog3.trn'
WITH STOPBEFOREMARK = 'lsn:0x00000020:00000161:0001'
GO
--Restores to specific Time provided.
RESTORE LOG [QNXT_PLANDATA_UNV_Copy] FROM DISK = N'D:\Backup\PIT_Tlog3.trn'WITH
STOPATMARK = N'lsn:0x00000020:00000161:0001'
GO
--Prefix 'lsn:0x' along with the LSN number for hexadecimal format.
STEP:11 WE CAN FIND TABLE NAME
SELECT OBJECT_NAME ('901578250')
Page Restore
KDSSG B46 Level001 046 29Mar2024 Restores4
KDSSG B46 Level001 045 28Mar2024 Restores3
Restores one or more damaged pages. An unbroken chain of log backups must be
available, up to the current log file, and they must all be applied to bring the
page up to date with the current log file.
Step:1 create new database.
create database Pagerestore
Step:2 create one new table in a database.
create table page1(pname varchar(100))
Step:3 insert some data in existed table.
insert into page1 values('hi')
insert into page1 values('hello')
insert into page1 values('how are you')
insert into page1 values('namastey')
step:4 take full backup
backup database Pagerestore to disk='D:\backup\Pagerestore_full.bak'
Step:5 after full backup add some more data in existed table.
insert into page1 values('are you going any where')
insert into page1 values('where are you going')
insert into page1 values('i am going to hyd')
insert into page1 values('why are you going to hyd')
step:4 take diff backup
backup database Pagerestore to disk='D:\Backup\Pagerestore_diff.bak'with differential
Step:5 after diff backup add some more data in existed table.
insert into page1 values('i have a small work in hyd')
insert into page1 values('oh ok tell me what kind of work you have?')
insert into page1 values('none of your business')
insert into page1 values('if you dont mind tell me')
step:4 take log backup
backup log Pagerestore to disk='D:\Backup\pagerestore_log.bak'
step:5 find indexes
Use Pagerestore(DB name)
GO
select * from sys.indexes where OBJECT_NAME(object_id)='page1'(table name)
step:6 find page-id
DBCC IND('Pagerestore','page1',0)
DBCC IND('DBname','tablename',0)
Step:7 first run below query
DBCC TRACEON(3604,-1)
step:8 we will get table full information once ran below query.
DBCC PAGE(Pagerestore,1,376,3)
DBCC PAGE(dbname,1,pageno,3)
dbcc page ( {'Pagerestore' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
0 � print just the page header
1 � page header plus per-row hex dumps and a dump of the page slot array (unless its
a page that doesn�t have one, like allocation bitmaps)
2 � page header plus whole page hex dump
3 � page header plus detailed per-row interpretation
-------------------------------------------
Step:8 you should be put database Offline
ALTER DATABASE Pagerestore(DBname) SET OFFLINE
WITH ROLLBACK IMMEDIATE
Step:9 Corrupt the pages using HexEditor or DBCC WRITEPAGE commands
Hexeditor.iso
Click right click open hexeditore
Open file and we should go to databases MDF flie location.
We can see like this.
Click address button select decimal option use 8192*page number=total paste there.
After that we got in page full data-----corrupter the data just modified don’t
delete any data after that save and come out.
After that bring database online
use Pagerestore
go
select * from [dbo].[page1]
Run DBCC CHECKDB, to identify if just a single page is corrupt or if multiple
pages are corrupt.
DBCC CHECKDB('Pagerestore')with PHYSICAL_ONLY
DBCC CHECKDB('Pagerestore')
--Take Backup with NORECOVERY, this will put database in --RESTORING State.
BACKUP LOG Pagerestore
TO DISK=N'D:\Backup\Pagerestore_NoRecovery.trn'
WITH NORECOVERY
--WITH NO_TRUNCATE -> Taillog Backup
--WITH COPY_ONLY -> Copy Only Log Backup
--WITH NORECOVERY -> Database is kept in Restoring State for restoring any corrupt
pages
--Start the restore sequence.
Full restore only page
restore database Pagerestore PAGE='1:448'from
disk='D:\backup\Pagerestore_full.bak'with norecovery
Diff restore
restore database Pagerestore from disk='D:\Backup\Pagerestore_diff.bak'with norecovery
log restore
restore log Pagerestore from disk='D:\Backup\pagerestore_log.bak'with norecovery
Full restore
restore LOG Pagerestorefrom DISK=N'D:\Backup\Pagerestore_NoRecovery.trn'WITH RECOVERY
File/Filegroup Restore
KDSSG B46 Level001 044 27Mar2024 Restores2
Restore one or more damaged read-write/read-only files, without restoring the entire database. File
restore is available only if the database has at least one read-only file group
Create a database with two file groups and create two tables in each file group.
CREATE DATABASE [FFG]
ON PRIMARY
( NAME = N'FFG', FILENAME = N'D:\Backup\FFG.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG1]
( NAME = N'FG1', FILENAME = N'D:\Backup\FG1.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG2]
( NAME = N'FG2', FILENAME = N'D:\Backup\FG2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG3]
( NAME = N'FG3', FILENAME = N'D:\Backup\FG3.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'FFG_log', FILENAME = N'D:\Backup\FFG_log.ldf' , SIZE = 1024KB , FILEGROWTH
= 10%)
use FFG
GO
create table Table1
(Sno int, sname varchar(50))
on FG1
create table Table2
(Sno int, sname varchar(50))
on FG2
create table Table3
(Sno int, sname varchar(50))
on FG3
insert into Table1 values (1,'One')
insert into Table2 values (1,'Ek')
insert into Table3 values (1,'Ondu')
insert into Table1 values (2,'Two')
insert into Table2 values (2,'Do')
insert into Table3 values (2,'Eradu')
--Taking a full database backup of FFG.
BACKUP DATABASE FFG
TO DISK=N'D:\Backup\FFG_FULL.BAK'
--3 Records are being duplicated into both tables.
insert into Table1 values (3,'Three')
insert into Table2 values (3,'Teen')
insert into Table3 values (3,'Mooru')
--Differential Backup.
BACKUP DATABASE FFG
TO DISK=N'D:\Backup\FFG_DIFF1.BAK'
WITH DIFFERENTIAL
insert into Table1 values (4,'Four')
insert into Table2 values (4,'Char')
insert into Table3 values (4,'Nalakku')
--Log Backup of FFG database.
BACKUP LOG FFG
TO DISK=N'D:\Backup\FFG_TLOG1.TRN'
insert into Table1 values (5,'Five')
insert into Table2 values (5,'Paanch')
insert into Table3 values (5,'Aidu')
--Log Backup of FFG database.
BACKUP LOG FFG
TO DISK=N'D:\Backup\FFG_TLOG2.TRN'
Before database corruption DB should put in offline.
alter database FFG set online
For testing, corrupt the database MDF/NDF file.(delete MDF file)
----:RESTORE COMMANDS:----
Before the database restore we have to take TAIL log backup.
BACKUP LOG FFG
TO DISK=N'D:\Backup\FFG_Tail.trn'
WITH NO_TRUNCATE
USE [master]
RESTORE DATABASE [FFG]
FILEGROUP='PRIMARY'
FROM DISK = N'D:\Backup\FFG_FULL.BAK'
WITH NORECOVERY,REPLACE
GO
RESTORE LOG [FFG]
FROM DISK = N'D:\Backup\FFG_TLog2.trn'
WITH NORECOVERY
GO
RESTORE LOG [FFG]
FROM DISK = N'D:\Backup\FFG_Tail.trn'
WITH RECOVERY
GO
If we try to bring database online WITH RECOVERY option, it will error out. Because only Primary
filegroup was restored and remaining FG1 and FG2 filegroups were not restored. So it is mandatory
in File and Filegroup restore that all filegroups including Primary once restored only then database will
come online.
RESTORE DATABASE [FFG]
FILEGROUP='Primary',FILEGROUP='FG1',FILEGROUP='FG2'
FROM DISK = N'C:\Backups\FFG_FULL.BAK'
WITH NORECOVERY,REPLACE
GO
RESTORE LOG [FFG]
FROM DISK = N'C:\Backups\FFG_TLog1.trn'
WITH NORECOVERY
GO
RESTORE LOG [FFG]
FROM DISK = N'C:\Backups\FFG_Tail.trn'
WITH RECOVERY
GO
Now database comes online as all filegroups are restored.
File restore Restores a file or filegroup in a multi-filegroup database.
We must use file and filegroup backup and restore operations in conjunction with transaction log
backups. After you restore the files, you must then restore the transaction log backups that were
created since the file backups were created to bring the database to a consistent state.
Piecemeal restore Restores the database in stages, beginning with the primary filegroup and one or
more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the
PARTIAL option and specifying one or more secondary filegroups to be restored.
Piece Meal Restore
KDSSG B46 Level001 044 27Mar2024 Restores2
Piecemeal Restore is a feature of SQL Server 2005 and using Piecemeal restore we can restore
file/filegroups as per requirement of Application team. It is mandatory to restore Primary filegroup first
before restore all other filegroups.
CREATE DATABASE [FFG]
ON PRIMARY
( NAME = N'FFG', FILENAME = N'D:\Backup\FFG.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG1]
( NAME = N'FG1', FILENAME = N'D:\Backup\FG1.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG2]
( NAME = N'FG2', FILENAME = N'D:\Backup\FG2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB
),
FILEGROUP [FG3]
( NAME = N'FG3', FILENAME = N'D:\Backup\FG3.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'FFG_log', FILENAME = N'D:\Backup\FFG_log.ldf' , SIZE = 1024KB , FILEGROWTH
= 10%)
use FFG
GO
create table Table1
(Sno int, sname varchar(50))
on FG1
create table Table2
(Sno int, sname varchar(50))
on FG2
create table Table3
(Sno int, sname varchar(50))
on FG3
insert into Table1 values (1,'One')
insert into Table2 values (1,'Ek')
insert into Table3 values (1,'Ondu')
insert into Table1 values (2,'Two')
insert into Table2 values (2,'Do')
insert into Table3 values (2,'Eradu')
--Taking a full database backup of FFG.
BACKUP DATABASE FFG
TO DISK=N'D:\Backup\FFG_FULL.BAK'
--3 Records are being duplicated into both tables.
insert into Table1 values (3,'Three')
insert into Table2 values (3,'Teen')
insert into Table3 values (3,'Mooru')
--Differential Backup.
BACKUP DATABASE FFG
TO DISK=N'D:\Backup\FFG_DIFF1.BAK'
WITH DIFFERENTIAL
insert into Table1 values (4,'Four')
insert into Table2 values (4,'Char')
insert into Table3 values (4,'Nalakku')
--Log Backup of FFG database.
BACKUP LOG FFG
TO DISK=N'D:\Backup\FFG_TLOG1.TRN'
insert into Table1 values (5,'Five')
insert into Table2 values (5,'Paanch')
insert into Table3 values (5,'Aidu')
--Log Backup of FFG database.
BACKUP LOG FFG
TO DISK=N'D:\Backup\FFG_TLOG2.TRN'
Before database corruption DB should put in offline.
alter database FFG set online
For testing, corrupt the database MDF/NDF file.(delete MDF file)
----:RESTORE COMMANDS:----
Before the database restore we have to take TAIL log backup.
BACKUP LOG FFG
TO DISK=N'D:\Backup\FFG_Tail.trn'
WITH NO_TRUNCATE
----: RESTORE COMMANDS:----
Should be mentioned PARTIAL
RESTORE DATABASE FFG FILEGROUP='PRIMARY'FROM DISK=N'D:\Backup\FFG_FULL.BAK'WITH
REPLACE,NORECOVERY,PARTIAL
RESTORE LOG FFGFROM DISK=N'D:\Backup\FFG_TLOG1.TRN'WITH NORECOVERY
RESTORE LOG FFGFROM DISK=N'D:\Backup\FFG_TLOG2.TRN'WITH NORECOVERY
RESTORE LOG FFGFROM DISK=N'D:\Backup\FFG_TAIL.TRN'WITH RECOVERY
---Restoring FG2 seperately and then you can restore FFG3
and FFG1.
RESTORE DATABASE FFGFILEGROUP='FFG2'FROM DISK=N'D:\Backup\FFG_FULL.BAK'WITH
REPLACE,NORECOVERY
RESTORE LOG FFGFROM DISK=N'D:\Backup\FFG_TLOG1.TRN'WITH NORECOVERY
RESTORE LOG FFGFROM DISK=N'D:\Backup\FFG_TLOG2.TRN'WITH NORECOVERY
RESTORE LOG FFGFROM DISK=N'D:\Backup\FFG_TAIL.TRN'WITH RECOVERY
User can access Table 2
After that one by one do restore all files.
we can restore file/filegroups as per requirement of Application team.
