restore db from transaction log

[Amit]

I want to restore my data base from transaction log but i dont know how i can i done this job ..........and where i can find transaction log in sql 2005 . how can i enable transaction log ...kindly provide me all details about transaction log .....

Thanks,
Amit Sharma

[Admin]

Hi Amit,

To restore a database from transaction log, trasnsaction log backup must be scheduled on the database. You can check the MSDB backup history table for the transaction log backup.

Execute below query to check the backup history. If TLog backup backup is not scheduled, you can create a database maintenance plan and schedule it.

Declare @dbname as varchar(100)
set @dbname = 'write database name here'
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server ,
msdb.dbo.backupset.database_name ,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Diff. Database'
ELSE msdb..backupset.type
END AS Backup_Type ,
msdb.dbo.backupset.backup_start_date ,
msdb.dbo.backupset.backup_finish_date , msdb.dbo.backupmediafamily.physical_device_name,
-- msdb.dbo.backupset.description ,
DATEDIFF(mi,msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS 'Duration(Min)',
msdb.dbo.backupset.name AS 'backupset_Name' ,
msdb.dbo.backupset.backup_size ,
msdb.dbo.backupset.expiration_date ,
msdb.dbo.backupset.is_copy_only ,
msdb.dbo.backupmediafamily.logical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(DATETIME, msdb.dbo.backupset.backup_start_date, 102) >= getdate()-2)
AND msdb..backupset.type in ( 'D','I','L')
AND msdb.dbo.backupset.database_name =@dbName
ORDER BY 2,5 DESC


Let me know if you need more information.

Regards,
Jugal Shah