SQL2K: Understanding Restore LOG

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL2K: Understanding Restore LOG

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-11-22 : 06:08:22

Hi there

We had a trasaction log occuring every 15 minutes starts between 4AM till midnight.

I did some testing by doing RESTORE TLOG NORECOVERY after RESTORE DATABASE NORECOVERY. Let say I did restore for 4.00AM, 4.15AM, 4.30AM and then I accidently without knowing straight to 5.45AM (miss the 4.45AM, 5.00AM, 5.15AM, 5.30). It seems that the restore still alowing this. I guess, corrrect if I am wrong, the restore is only restore what ever transaction in on the backup without knowing the sequence of history of the restore? Is this true?

Is there anyway that you can verify the sequence of the restore .. just in case I made a mistake cause as you know the 15 minutes backup is very lengthy process and it could be end up with a mistake unless I am thinking to create some sort of auto generate restore from restore_history view that I had.

Any comment/input?

Kristen
Test

22859 Posts

Posted - 2006-11-22 : 08:54:10

The Log Backup contains identifiers to make sure that what you restore is contiguous. You will get an error message if you try to restore out of sequence.

I think it is possible for Tlog backups to contain nothing - i.e. skipping a blank one would not cause an error, because the next-needed-batch is present in the following backup file.

You may be able to use RESTORE FILELISTONLY to see the batch numbers in the backup files.

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

dewacorp.alliances

452 Posts

Posted - 2006-11-22 : 14:57:11

quote:
The Log Backup contains identifiers to make sure that what you restore is contiguous. You will get an error message if you try to restore out of sequence.

Mmm ... I try this to restore out of sequence and it seems it's ok ?!?! The only explantation is if there is no tansaction activity during that time. Need to check out this. BTW ... how do I know if there is activity in the TLOG Backup? Is there any tools that can see what happening on TLog backup?
I think SQL backup TLog at certain file size eventhough there is no activity in the database.

quote:
I think it is possible for Tlog backups to contain nothing - i.e. skipping a blank one would not cause an error, because the next-needed-batch is present in the following backup file.

>>> This probably explain why I can skip few TLog backup when I restore.

dewacorp.alliances

452 Posts

Posted - 2006-11-23 : 15:07:23

How do I know if there is activity in the TLOG Backup? Is there any tools that can see what happening on TLog backup?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-23 : 21:12:44

quote:
Originally posted by valdyv

How do I know if there is activity in the TLOG Backup? Is there any tools that can see what happening on TLog backup?

Does it really matter?

When you restore from logs, just restore the logs in sequence from oldest to newest, and you won't have any problems.

CODO ERGO SUM

dewacorp.alliances

452 Posts

Posted - 2006-11-24 : 08:55:00

Just for curiousity.

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-24 : 09:13:43

Ones with no activity will be small, I guess.

Also the GUI in 2005 tells you the LSN numbers.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2006-11-24 : 09:28:54

Provided no-one has renamed your BAK files

then the data in MSDB should give you the answer:


SELECT TOP 100
--	database_name,
	backup_set_id,
	backup_start_date,
	backup_finish_date,
	type,
	name,
	user_name,
	first_lsn,
	last_lsn,
	database_backup_lsn
	, * 
FROM	msdb.dbo.backupset
WHERE	    database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
-- 	AND type='D' 
ORDER BY backup_start_date DESC

Kristen

dewacorp.alliances

452 Posts

Posted - 2006-11-24 : 20:22:31

Can some give some idea what is the first_LSN, last_LSN Checkpoint_LSN, Database_Backup_LSN in relation to find out the sequence of the backup (TLOG)?

The numbers is kinda weird:

first_LSN: 2948000000035000001
last_LSN: 2948000000035200001
checkpoint_LSN: 2948000000035000003
database_backup_LSN: 2942000000004200003

?!?!

Kristen
Test

22859 Posts

Posted - 2006-11-25 : 03:16:16

I'm with MVJ. It doesn't matter, just restore them in order.

msdb.dbo.backupset will tell you the names of the files, in order, if you need to know - but to be frank if you need to query that there is something wrong with either your file-naming policy, or your file management policy. Unless people are taking backups to their C: drives "because they can" then all the files you need will be in the Backup folder.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources