Some
time we update the data accidentally in the production server. We have last old
backup but there is probability that some records has been inserted or
deleted from the last back up time. Here we will discuss how to restore the data at
specific poin of time.
Step 1: I'm creating a database, say ExactHelp:
CREATE DATABASE ExactHelp
Step 2: I'm
creating a table say tblUser and inserting some records into it:
USE ExactHelp
CREATE TABLE tblUser(
ntUserID BIGINT
PRIMARY KEY IDENTITY,
vcUserName VARCHAR(200),
vPassword VARCHAR(500),
btActive BIT
DEFAULT(0)
)
INSERT tblUser VALUES
('johnny_depp@exacthelp.com','sqlsecure',1),
('tom_hanks@exacthelp.com','sqlsecure',1),
('hahrukh_khan@exacthelp.com','sqlsecure',0),
('leonardo_di_caprio@exacthelp.com','sqlsecure',1),
('al_pacino@exacthelp.com','sqlsecure',1),
('jack_nicholson@exacthelp.com','secure01',1),
('kamal_haasan@exacthelp.com','secure01',0),
('tom_hiddleston@exacthelp.com','hiddleston',1)
3. Now table has following records:
SELECT * FROM tblUser
ntUserID
|
vcUserName
|
vPassword
|
btActive
|
1
|
johnny_depp@exacthelp.com
|
sqlsecure
|
1
|
2
|
tom_hanks@exacthelp.com
|
sqlsecure
|
1
|
3
|
hahrukh_khan@exacthelp.com
|
sqlsecure
|
0
|
4
|
leonardo_di_caprio@exacthelp.com
|
sqlsecure
|
1
|
5
|
al_pacino@exacthelp.com
|
sqlsecure
|
1
|
6
|
jack_nicholson@exacthelp.com
|
secure01
|
1
|
7
|
kamal_haasan@exacthelp.com
|
secure01
|
0
|
8
|
tom_hiddleston@exacthelp.com
|
hiddleston
|
1
|
Step 4: Now I'm going to create a full backup of the database ExactHelp:
--Data backup
BACKUP DATABASE ExactHelp
TO DISK = N'F:\DbBackup\ExactHelp.bak'
--Log Backup
BACKUP LOG ExactHelp
TO DISK = N'F:\DbBackup\ExactHelp_Log.bak'
Step 5: Now I'm going to insert some more
records in the table tblUser:
INSERT tblUser VALUES
('angelina_jolie@exacthelp.com','sqlsecure',1),
('charlize_theron@exacthelp.com','sqlsecure',1),
('marion_cotillard@exacthelp.com','sqlsecure',0),
('julia_roberts@exacthelp.com','sqlsecure',1)
Step 6: Now table tblUser has following records:
ntUserID
|
vcUserName
|
vPassword
|
btActive
|
1
|
johnny_depp@exacthelp.com
|
sqlsecure
|
1
|
2
|
tom_hanks@exacthelp.com
|
sqlsecure
|
1
|
3
|
hahrukh_khan@exacthelp.com
|
sqlsecure
|
0
|
4
|
leonardo_di_caprio@exacthelp.com
|
sqlsecure
|
1
|
5
|
al_pacino@exacthelp.com
|
sqlsecure
|
1
|
6
|
jack_nicholson@exacthelp.com
|
secure01
|
1
|
7
|
kamal_haasan@exacthelp.com
|
secure01
|
0
|
8
|
tom_hiddleston@exacthelp.com
|
hiddleston
|
1
|
9
|
angelina_jolie@exacthelp.com
|
sqlsecure
|
1
|
10
|
charlize_theron@exacthelp.com
|
sqlsecure
|
1
|
11
|
marion_cotillard@exacthelp.com
|
sqlsecure
|
0
|
12
|
julia_roberts@exacthelp.com
|
sqlsecure
|
1
|
Step 7: My client assigned me a task to execute this script:
UPDATE tblUser SET
vcUserName =
'kate_winslet@mfs.com',
vPassword =
'sqlsecure123',
btActive =
1
WHERE ntUserID = 4
But accidentally I
missed the WHERE cluase :( And executed it like this:
UPDATE tblUser SET
vcUserName =
'kate_winslet@mfs.com',
vPassword =
'sqlsecure123',
btActive = 1
Step 8: Due to my
bad luck table user has following records:
ntUserID
|
vcUserName
|
vPassword
|
btActive
|
1
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
2
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
3
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
4
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
5
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
6
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
7
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
8
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
9
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
10
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
11
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
12
|
kate_winslet@mfs.com
|
sqlsecure123
|
1
|
Now I want to get
back my old data otherwise he will kill me..
If I'll restore
the the last backup file I will get data which was at step 3 but I need data at step 6. Good news is
sql server support point in time backup :) Now I'm going to recover old records.
Step 9: Creating
the transaction log backup:
BACKUP LOG ExactHelp
TO DISK = N'F:\DbBackup\ExactHelp_Log_1.bak'
Step 10: Finding
the lsn number of accidental update operation. By this script we can get lsn number of the update operation:
SELECT DISTINCT
L.[Begin
Time] AS UpdateTime,
CAST(CAST(CONVERT(VARBINARY,PARSENAME(REPLACE(L.[Current LSN],':','.'),3),2) AS INT) AS VARCHAR ) +
RIGHT('0000000000' + CAST(CAST(CONVERT(VARBINARY,PARSENAME(REPLACE(L.[Current LSN],':','.'),2),2) AS INT) AS VARCHAR ),10) +
RIGHT('00000' +CAST(CAST(CONVERT(VARBINARY,PARSENAME(REPLACE(L.[Current LSN],':','.'),1),2) AS INT) AS VARCHAR ),5) AS LnsNumber,
L.[Transaction
Name] [Type],
ISNULL(PARSENAME(T.AllocUnitName,2),T.AllocUnitName) AS TableName,
SUSER_SNAME(L.[Transaction SID]) UpdatedBy
FROM sys.fn_dblog(NULL,NULL)
L INNER JOIN sys.fn_dblog(NULL,NULL) T
ON LEFT(L.[Current LSN],17) = LEFT(T.[Current LSN],17)
WHERE L.[Transaction Name] =
'UPDATE'
AND T.AllocUnitName LIKE '%tblUser%'
Output:
UpdateTime
|
LnsNumber
|
Type
|
TableName
|
UpdatedBy
|
2014/05/03 17:11:57:123
|
113000000016200001
|
UPDATE
|
tblUser
|
Ritesh-PC\Ritesh
|
We may get more
than row in the output. You will have to choose correct one manually. In this example my update
operation lsn number is: 113000000016200001
Step 11: Now I'm creating new
database which is copy of database ExactHelp by using last backup file (I have
created backup at step 4). New database name is ExactHelp_Copy:
--Data restore
RESTORE DATABASE ExactHelp_Copy
FROM DISK = N'F:\DbBackup\ExactHelp.bak'
WITH NORECOVERY ,
MOVE 'ExactHelp'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\ExactHelp_Copy.mdf',
MOVE 'ExactHelp_Log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\ExactHelp_Copy_log.ldf'
--Log file
restore
RESTORE DATABASE ExactHelp_Copy
FROM DISK = N'F:\DbBackup\ExactHelp_Log.bak'
WITH NORECOVERY
Note: Now table
tblUser has exectly same records which was at step 3. But we cannot fetch data
now since table is in the state of not recover.
Step 12: In this
step I'm going to restore the transaction log file which I had created at step 9
in the new database ExactHelp_Copy. In this case I will restore only those
operation which had happened before my update operation time 2014/05/03
15:42:21:710 that is lsn number 113000000016200001
RESTORE DATABASE ExactHelp_Copy
FROM DISK = N'F:\DbBackup\ExactHelp_Log_1.bak'
WITH NORECOVERY ,
STOPBEFOREMARK = 'lsn:113000000016200001'
Step 13: Now I'm going
to change the state of database ExactHelp_Copy from norecovery mode to recovery:
RESTORE DATABASE ExactHelp_Copy
WITH RECOVERY
Step 14: Now I'm
going to check data in the table tblUser in new database ExactHelp_Copy:
USE
ExactHelp_Copy
SELECT * FROM tblUser
ntUserID
|
vcUserName
|
vPassword
|
btActive
|
1
|
johnny_depp@exacthelp.com
|
sqlsecure
|
1
|
2
|
tom_hanks@exacthelp.com
|
sqlsecure
|
1
|
3
|
hahrukh_khan@exacthelp.com
|
sqlsecure
|
0
|
4
|
leonardo_di_caprio@exacthelp.com
|
sqlsecure
|
1
|
5
|
al_pacino@exacthelp.com
|
sqlsecure
|
1
|
6
|
jack_nicholson@exacthelp.com
|
secure01
|
1
|
7
|
kamal_haasan@exacthelp.com
|
secure01
|
0
|
8
|
tom_hiddleston@exacthelp.com
|
hiddleston
|
1
|
9
|
angelina_jolie@exacthelp.com
|
sqlsecure
|
1
|
10
|
charlize_theron@exacthelp.com
|
sqlsecure
|
1
|
11
|
marion_cotillard@exacthelp.com
|
sqlsecure
|
0
|
12
|
julia_roberts@exacthelp.com
|
sqlsecure
|
1
|
Yes, I got the old
data!!!
Step 15: Updating
the Exact_Help.tblUser from Exact_Help_Copy.tblUser by using key column ntUserID:
UPDATE tblUser SET
vcUserName = CU.vcUserName,
vPassword = CU.vPassword,
btActive = CU.btActive
FROM tblUser U INNER
JOIN ExactHelp_Copy.dbo.tblUser CU
ON U.ntUserID = CU.ntUserID
WHERE U.vcUserName = 'kate_winslet@mfs.com'
