DB restore - Just a scenario, for record..

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 2005 Forums

Transact-SQL (2005)

DB restore - Just a scenario, for record..

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2010-11-26 : 04:11:11

DB: xyz : full recovery mode

0100 hrs : take full backup
0100 till 0200 hrs : 100 transactions
0200 hrs : take transaction log backup..
0200 till 0245 hrs: 100 trasactions
0246 hrs : DB crashes ..MDF file is corrupted, cannot recover db what so ever

Can I get the data I had in the db from 0200 till 0245 hrs, for which I dont have any transaction log backup, but just have it in the ldf, which is still intact-not corrupted.

As I said, this is not any issue I am facing now, but just for a record.

Thanks
Sam

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 04:59:14

You can try a backup log with no_truncate.
Have a look in bol.

It backs up the log without affecting the database so works with a corrupt database - of course it depends a bit on why the database is corrupt.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xpandre
Posting Yak Master

212 Posts

Posted - 2010-11-26 : 05:22:54

I tried what was in BOL

If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.

BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }

Since my DB is corrupt and cannot be brought online, it gives me error :

Msg 942, Level 14, State 3, Line 1
Database 'abc_3' cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 05:34:00

You can try bringin the database online without recovery
Not sure if the emergency mode was on option in alter database with v2005
otherwise try patching the system table
Think it's master..sysdatabases - set status = 32768 for the database.
Might be worth checking that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-26 : 07:21:53

System tables cannot be updated on SQL 2005. Also, sysdatabases isn't a table any longer.

Alter database <db name> Set Emergency, then try to take the log backup.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources