Monday, October 1, 2007

RESTORE LOG file and WITH FILE behaviour

Recently I have been visited one of the our clients and have been asked the following question.
"What if I have 3 fileid within a backup file and when restore the log being done, I do not specify WITH FILE option,so do I get latest Log's data?". To answer the question I decide to conduct some test script.


CREATE DATABASE demo
GO
ALTER DATABASE demo SET RECOVERY FULL
GO
CREATE TABLE demo..t1(c1 INT NOT NULL PRIMARY KEY)
--insert one row
insert demo..t1 VALUES (1)
--BACKUP the data
BACKUP DATABASE demo TO DISK = 'c:\temp\demo.bak' WITH INIT
GO
insert demo..t1 VALUES (2)
--start BACKUP log data
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH INIT
GO
insert demo..t1 VALUES (3)
--- BACKUP log FILE at this time WITH FILE =2
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH NOINIT
GO
insert demo..t1 VALUES (4)
--- BACKUP log FILE at this time WITH FILE =3
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH NOINIT

--Lets do RESTORE and see what is going on
RESTORE DATABASE demo FROM DISK = 'c:\temp\demo.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG demo FROM DISK = 'c:\temp\demo_log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG demo FROM DISK = 'c:\temp\demo_log.bak' WITH RECOVERY ----Do you expect the last one?

GO
SELECT * FROM demo..t1
c1
-----------
1
2
It did not return rows 3 and 4.

So that means if you DO NOT specify WITH FILE in RESTORE LOG file you DO NOT get latest FILEID but only the first one.

No comments: