January 23, 2008 at 12:07 am
In SQL Server, is it possible to restore the database from last but one complete backup with all other transaction log backups. Skipping last complete backup is possible or not?
Example Scenario,
10/1/2008
8.00 – complete backup (B1)
9.00 – transaction log backup (t1)
10.00 – t2
11.00 – t3
11/1/2008
8.00 – complete backup(b2)
9.00- t4
10.00 –t5.
In this scenario, b2 got crashed. Then how to recover the data?
With Regards,
Senthil
January 23, 2008 at 12:24 am
I haven't tested that, but if you have all your log-backups, IMO it should be possible.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 1:29 am
Hi
R u saying that your backup file (.bak) crashed.
"Keep Trying"
January 23, 2008 at 1:51 am
you cant restore the last two transaction log backups taken after B2.
January 23, 2008 at 2:01 am
I haven't tried it, but it should be possible to recover all the way.
Full backups don't truncate the transaction log (only tran log backups do that) so you should be able to use the log backups from both before and after the full backup that doesn't work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2008 at 2:23 am
1. Restoring previous full backup(b1)
>>> restore database t1 from disk = 'd:\t1.bak' with file=1, Norecovery (full backup)
Processed 104 pages for database 't1', file 'T1_Data' on file 1.
Processed 1 pages for database 't1', file 'T1_Log' on file 1.
RESTORE DATABASE successfully processed 105 pages in 0.316 seconds (2.702 MB/sec).
2. Resorting tnx log back (t1)
>>> restore log t1 from disk = 'd:\t1.bak' with file = 2, norecovery (tnx backup)
Processed 1 pages for database 't1', file 'T1_Log' on file 2.
RESTORE LOG successfully processed 1 pages in 0.005 seconds (0.819 MB/sec).
2. Resorting tnx log back (t2)
>>> restore log t1 from disk = 'd:\t1.bak' with file = 3, norecovery (tnx backup)
Processed 1 pages for database 't1', file 'T1_Log' on file 3.
RESTORE LOG successfully processed 1 pages in 0.004 seconds (0.384 MB/sec).
3. Resorting tnx log back (t5)
>>> restore log t1 from disk = 'd:\t1.bak' with file = 5, recovery (tnxbackup)
Server: Msg 4330, Level 16, State 1, Line 4
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
Without applying last full backup(B2), not able to apply tnx log backup(t5).
Regards,
Senthil.
January 23, 2008 at 2:59 am
2. Resorting tnx log back (t2)
>>> restore log t1 from disk = 'd:\t1.bak' with file = 3, norecovery (tnx backup)
Processed 1 pages for database 't1', file 'T1_Log' on file 3.
RESTORE LOG successfully processed 1 pages in 0.004 seconds (0.384 MB/sec).
3. Resorting tnx log back (t5)
>>> restore log t1 from disk = 'd:\t1.bak' with file = 5, recovery (tnxbackup)
Server: Msg 4330, Level 16, State 1, Line 4
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
Where are Tlog 3 and 4
You need all logbackups.You cannot skip one !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 3:03 am
can you post the results of this query ?
select BS.backup_start_date
, BS.backup_finish_date
, BMF.physical_device_name
--, BMF.logical_device_name
, BS.backup_set_id
, BS.media_set_id
, BS.position
, BS.name
, BS.Description
, BS.first_lsn
, BS.last_lsn
, BS.type
-- , BMS.*
from msdb.dbo.backupset BS
inner join msdb.dbo.backupmediaset BMS
on BS.media_set_id = BMS.media_set_id
inner join msdb.dbo.backupmediafamily BMF
on BMF.media_set_id = BS.media_set_id
where BS.database_name = 'yourdbname'
-- and physical_device_name not like
--and 30000000046000001 between first_lsn and last_lsn
order by BS.first_lsn, BS.last_lsn;
GO
RESTORE headeronly
from DISK='your_filepath_and_name'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 3:19 am
2008-01-17 19:47:47.0002008-01-17 19:47:49.000d:\t1.bak63241NULLNULL50000000400000015000000040400001D
2008-01-17 19:58:55.0002008-01-17 19:58:55.000d:\t1.bak64242T1 backupNULL50000000400000015000000040800001L
2008-01-17 19:59:53.0002008-01-17 19:59:53.000d:\t1.bak65243T1 backupNULL50000000408000015000000041100001L
2008-01-18 16:01:40.0002008-01-18 16:01:40.000d:\t1.bak56245t1 backupNULL50000000408000015000000042700001L
2008-01-18 16:01:13.0002008-01-18 16:01:13.000d:\t1.bak55244t1 backupNULL50000000417000015000000041900001D
January 23, 2008 at 3:24 am
I have tried with t1,t2,t3 ,t4 & t5 with b1. till t3 working fine.
January 23, 2008 at 3:34 am
2008-01-17 19:59:53.000 2008-01-17 19:59:53.000 d:\t1.bak 65 24 3 T1 backup NULL 5000000040800001 5000000041100001 L
2008-01-18 16:01:40.000 2008-01-18 16:01:40.000 d:\t1.bak 56 24 5 t1 backup NULL 5000000040800001 5000000042700001 L
since these logbackups start with the same LSN (5000000040800001), I think your "3" is a copy_only logbackup and the "5" is a normal logbackup.
Don't use the "3" but use the "5".
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 4:56 am
Cool...working fine. Thanks a lot.
January 23, 2008 at 5:32 am
saveguard the query. :rolleyes:
There's some nice data in the box.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply