Restore database from earlier Transaction Log

Hi Folks

I have full backup of database around 2:00 pm in last week Monday and transaction log backup of every 20 minutes. I want to restore database from the backup of Monday and restore transactional Logs since 8:00 am until 2:00 pm of full backup database. somehow I have lost the full backup before 2:00 pm on Monday.

so I have to restore available full backup which 2pm on Monday and Restore Monday's transaction logs from 8:00 am to 2:00 pm. How do i do it ...Please help it is critical...


Thanks in Advance......

Is your situation like this?
(simplified)

Full backup - D1
Log backup - L1
Log backup - L2
Full backup - D2
Log backup - L3
Log backup - L4
.. crash...

You need to restore up to L4, but you've lost D2, and only D1 is available..?
If this is the case, you can do that *providing* that you have access to *all* log backups since D1 (that is L1-L4)

The log chain isn't broken just because a full backup is done, you can start a restore from any full backup. But that also requires that you still have all log backups still available. The logbackups is what makes up the chain - those are the important ones.
The full backup you restore only servs as 'starting point', deciding how far back in time the first log applied has to be.

/Kenneth

Based your info we predict and give the answers...

Post your full backup and tlog backup time lines and available backup info to get the right answer...


MohammedU.
Moderator
SQL-Server-Performance.com

Thanks a lot Folks...

no doubt .. It is situation...I want to restore the transaction logs before full backup o the same day (Monday). Like this..

Monday 8 AM..
Log backup - L1
Log backup - L2
.
.
Full backup - D2 (Monday 2 PM)
Now want to restore L1, L2, L3.... logs between 8 am to 2 pm

It is not crash case guys... need data between this time and unfortunately, don,t have full backup before 8 am.

KEWIN... can I do this (according to your explanation)???????

Full backup - D1 (NOT AVAILABLE)
Log backup - L1
Log backup - L2
Full backup - D2 (AVAILABLE)
Log backup - L3
Log backup - L4


Restore D2 and apply L1, L2, L3, L4 and so on....

I hope that I have explain the situation clearly...

Waiting for reply and Thanks again guys for your help....

I have created the same situation using Northwind database.
like this...

quote:
RESTORE DATABASE north FROM DISK='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP
orthwind.bak2'
WITH NORECOVERY,
MOVE 'northwind' TO 'crogram FilesMicrosoft SQL ServerMSSQLackup
orth.mdf',
MOVE 'northwind_log' TO 'crogram FilesMicrosoft SQL ServerMSSQLackup
orth_log.ldf'

Restore LOG files backups before the full backup...
RESTORE LOG north FROM DISK='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP
orthwind.trn'
WITH NORECOVERY, FILE=1
GO
RESTORE LOG north FROM DISK='Crogram FilesMicrosoft SQL ServerMSSQLBACKUP
orthwind.trn'
WITH RECOVERY, FILE=2

Error....
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 27000000044700001, which is too early to apply to the database. A more recent log backup that includes LSN 28000000007000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 28000000003200001, which is too early to apply to the database. A more recent log backup that includes LSN 28000000007000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Full Backup 'northwind.bak2' taken after the Transaction Logs that I tried to restore cos I want to be database in prior stage.

Please help folks ...
Thanks in Advance

Without Monday 8 AM full backup D1 you can't restore L1, L2 log backups.
I don't see any other options are available in this case.

You better find D1 backup...if you guys are backing to tape then check tape backups...


MohammedU.
Moderator
SQL-Server-Performance.com

Thanks for rapid answer Mohammed U...

This is the only regret I have that we don't have the D1 backup. We have only D2 backup and we want to take database to L1, L2..stage, any other which way would be appreciated.

Thanks in advance...

I can guess one option which may not be correct... but here it is...

1. Restore D2 backup
2. Use third party tools like LOG EXPLORER and attach your log backup files after L1 AND L2 log backups...generate roll back scripts...
3. Run the generated roll back scripts in step 2 on to the database restored in step1.

Log Explorer for SQL Server
http://www.lumigent.com/products/le_sql.html


MohammedU.
Moderator
SQL-Server-Performance.com

Thanks for your valuable suggestions and quick replies.. I always count on you guys. At least now, I am 100% sure that it is not possible until I have D2 backup..
Guys...I will try Lumigent option and lets see what happen..(end of the day, it would be learning curve anyway.)


Thanks,
sonny

Hi Folks

I just went through a situation where a question raised. I setup job to backup transaction logs every 15 minutes and logs are appending into same log file rather create separate log file for each log backup after 15 minutes..

am I missing something here? r how can make it correct that every log backup after every 15 minutes will backedup in separate log file.

Thanks,
sonny

You have to create seperate file when you run the log backup...<br />YOu can make use of the following script if you want...<br /><br />create Procedure Sp_BackUpTransactionLog <br />@Sys_Database Sysname, @BackupPath Sysname ,@FileDelDays int = 2<br />As<br />Begin<br />-- Sp_BackUpTransactionLog @Sys_Database = 'pubs' , <br />@BackupPath = '\servernamee$TransLogs'<br /><br />SET NOCOUNT ON <br />Declare@Vch_FileNameVarchar(255),<br /> @Dt_DateAndTimeVarchar(20),<br /> @Vch_SqlStringVarchar(255)<br /><br />if right(@BackupPath,1) &lt;&gt; ''<br />select @BackupPath = @BackupPath+''<br /><br />select @Dt_DateAndTime = convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')<br /><br />SELECT @Vch_FileName=@BackupPath+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_' + @Dt_DateAndTime +'.TRN'<br />-- SELECT @Vch_FileName<br />SELECT @Vch_SqlString='BACKUP LOG '+ @Sys_Database + ' TO DISK = '''+ @Vch_FileName +''''<br />SELECT @Vch_SqlString=@Vch_SqlString + ' WITH INIT , STATS = 10 '<br />--SELECT @Vch_SqlString<br />EXECUTE(@Vch_SqlString)<br />declare @cmd Varchar(1000)<br />select @cmd = 'del '+@BackupPath+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_'+convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate()-@FileDelDays, 112)+'*.trn'<br /><br />EXEC master.dbo.xp-cmdshell @cmd, no_output<br />end<br /><br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com

quote:Originally posted by satya

Mohammed
I guess it may not work as LSN is mismatched or am I going wrong way round with LUmigent.

As long as user has all tlog backups, I believe rollback script can be generated using lumigent log explorer and run against restored database to move the database to the required point...
I don't think we need to worry about LSN in this case.

MohammedU.
Moderator
SQL-Server-Performance.com

Thanks again....
Is there any way I can do at time of configuration of backup job for transaction log from EM? so, log file will backuped separately each time if not then how can see the details of transactional logs with individual file.

I have tried dbbc log, dbcc loginfo ... commands but no individual file details shown up.

You can create maintenance plan for log backup for individual files...

http://www.databasejournal.com/features/mssql/article.php/3530486


MohammedU.
Moderator
SQL-Server-Performance.com

Thanks again...

I have tried Lumigent Log Explorer (SQL LOG RESCUE from Red Gate as well) and good news is that it is possible to retrieve data from prior backed up Transaction Logs into preceded full backed up database. It's create text file from "Salvage Dropped/Truncated Data" window and then you run the script from Query Analyser.

from the Lumigent help file...

quote:
you may lose all data in a SQL table due to an unintentional DROP TABLE or TRUNCATE TABLE command. Although SQL Server records DROP and TRUNCATE operations in the transaction log, it does not log the deleted data. Therefore, you cannot use the standard Log Explorer functions to restore the deleted data from the log. If you do not have any backup files, the salvage data mechanism is the only option available to you for salvaging dropped or truncated data. When a table is dropped or truncated, SQL Server places all data pages that were holding the data on a free page list. If a page is not reused, it still holds the original table data. If the salvage data method is chosen, Log Explorer searches the free list for old data pages that have not been reused yet. Then, Log Explorer creates a SQL script file that reconstructs the original data from these pages. Log Explorer is able to determine the number of original rows that have been dropped. On completion, it reports that number along with the number of rows that it actually recovered. This information indicates to the user whether dropped data was recovered fully or partially.

Secondly... I have tried the maintenance plan and it does create separate log file for each backup.. so I wonder that how come we can not do transaction log backup in individual log file using through T-SQL (code).. OR is there any way we can script the 'Maintenance plan'???? so script can be extracted.

Thank,
sonny

Apologies for the late reply.

No, you can (as I tried to explain =;o) only apply logs from the earliest *full* backup and forward in time, as long as you have an unbroken logchain available.

That is, if D1 is AVAILABLE, and D2 is NOT AVAILABLE, you may start at D1 and apply L1-L4.
However, if it is like you say below, then D2 is your earliest starting point.

There is no way for the native RESTORE to apply L1 and L2 from D2.
You have to try 'faking' it, as suggested by others, by extracting and replayng the L1-L2 logs or something similar.

/Kenneth

quote:Originally posted by sonnysingh


KEWIN... can I do this (according to your explanation)???????

Full backup - D1 (NOT AVAILABLE)
Log backup - L1
Log backup - L2
Full backup - D2 (AVAILABLE)
Log backup - L3
Log backup - L4


Restore D2 and apply L1, L2, L3, L4 and so on....

I hope that I have explain the situation clearly...

Waiting for reply and Thanks again guys for your help....

Thanks....

I have tried the maintenance plan and it does create separate log file for each backup.. so I wonder that how come we can not do transaction log backup in individual log file using through T-SQL (code).. OR is there any way we can script the 'Maintenance plan'???? so script can be extracted.

sonny

You can use the following code and can be modified as needed...<br /><br />create Procedure Sp_BackUpTransactionLog @Sys_Database Sysname, @BackupPath Sysname ,@FileDelDays int = 2<br />As<br />Begin<br />-- Sp_BackUpTransactionLog @Sys_Database = 'pubs' , @BackupPath = '\servernamee$BackupTransLogs', @FileDelDays = 2<br /><br />SET NOCOUNT ON <br />Declare@Vch_FileNameVarchar(255),<br /> @Dt_DateAndTimeVarchar(20),<br /> @Vch_SqlStringVarchar(255)<br /><br />if right(@BackupPath,1) &lt;&gt; ''<br />select @BackupPath = @BackupPath+''<br /><br />select @Dt_DateAndTime = convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')<br /><br /><br />SELECT @Vch_FileName=@BackupPath+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_' + @Dt_DateAndTime +'.TRN'<br />-- SELECT @Vch_FileName<br />SELECT @Vch_SqlString='BACKUP LOG '+ @Sys_Database + ' TO DISK = '''+ @Vch_FileName +''''<br />SELECT @Vch_SqlString=@Vch_SqlString + ' WITH INIT , STATS = 10 '<br />--SELECT @Vch_SqlString<br />EXECUTE(@Vch_SqlString)<br />declare @cmd Varchar(1000)<br />select @cmd = 'del '+@BackupPath+replace (@@servername, '', '$')+'_'+@Sys_Database+'_tlog_'+convert(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, getdate()-@FileDelDays, 112)+'*.trn'<br /><br />EXEC master.dbo.xp-cmdshell @cmd, no_output<br /><br />end<br /><br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com

Thanks a lot.... I really appreciate all the efforts you guys added to my curiousities..

Warm Regards,
sonny