SQL EXPLORER: Disaster Recovery

Showing posts with label Disaster Recovery. Show all posts

Thursday, May 19, 2016

Copy only full db backup affect the lsn sequence of log backup not the differential backup

--Take the Full backup without copy_only

BACKUP DATABASE [DBA_Test] TO DISK = N'F:\Backup\DBA.bak' WITH init ,STATS = 10, CHECKSUM

--,copy_only

--Initiate the transaction for the T-og backup

use DBA_Test

create table t4

(

id int

)

insert into t4 values (1)

go 100

--Take the T-log backup

BACKUP LOG [DBA_Test] TO DISK = N'F:\Backup\dba1.trn' WITH NOFORMAT, INIT, NAME = N'DBA_Test-Transaction Log Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

--Before we perform the copy_only full backup , let's initiate another 100 records so we can check last T-log backup able to recover this contents and

--also it confirms copy_only full backup don't affect the log sequence

insert into t4 values (1)

go 100

--Take the full backup copy_only

BACKUP DATABASE [DBA_Test] TO DISK = N'F:\Backup\DBA1.bak' with STATS = 10, CHECKSUM,copy_only

--initiate the transaction again

insert into t4 values (1)

go 100

--Take another log backup

BACKUP LOG [DBA_Test] TO DISK = N'F:\Backup\dba2.trn' WITH NOFORMAT, NAME = N'DBA_Test-Transaction Log Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

---Now recover the database

--i.e -1. Initial Full backup

--2. T-log 1 (expected to recover 100 records)

--3. T-log 2 (expected to recover 200 records without affecting lsn sequence)

--see the attached recovery option

--Conclusion ... Copy only full db backup does affect the lsn sequence when it comes to regular backups but it won't affect differential backup.

Monday, June 23, 2014

Who truncated the table and what time

SET NOCOUNT ON;

DECLARE @ObjectName SYSNAME

DECLARE @TransactionID NVARCHAR(500)

SET @ObjectName = 'dbo.tablename'

SELECT @ObjectName AS ObjectName

, [Transaction Name]

, SUSER_SNAME([Transaction SID]) AS UserName

, [Begin Time]

, Operation

, [Transaction ID]

FROM fn_dblog(NULL, NULL)

where [Transaction Name] LIKE 'TRUNCATE%'

AND Operation = 'LOP_BEGIN_XACT'

Thursday, April 25, 2013

Recover the Marked Transaction for different users

This article describes the simulation process how to recover the DML/DDL Data while during the Deployment Process.

create database test

use test

create table employee

(

empno int primary key ,

empname varchar(10),

location varchar(10)

)

---Full backup available from Maintenance Plan

BACKUP DATABASE [test] TO DISK = N'D:\Full_Backup\test.bak' WITH NOFORMAT,

NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Full backup taken for the required database as shown as below.

First transaction screenshot as shown as below

Second transaction screenshot as shown as below

Third transaction screenshot as shown as below

Fourth transaction screenshot as shown as below

---Now take the log backup

---we assume all the transaction executed by the users accidentally

---In order to recover we will be taking the log backup.

Restore full backup operation screenshot as shown as below

Restore the log backup in to newly database named called TestReplicate screenshot as shown as below