Attempting to recover from tail-log backup

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)

Attempting to recover from tail-log backup

Author

Topic

maranbe
Starting Member

28 Posts

Posted - 2006-11-15 : 03:36:27

Hi folks,

I am trying to simulate last transaction log backup after a damaged db in our test server. These are the steps:

1. added new records to the test db
2. deleted the test db mdf file
3. attempted to ran the backup log with no_truncate but failed.
4. then tried to rename the test log file
4. created a similar db and log and changed recovery to 'full'
5. stopped the sql
6. replaced the currently created ldf file with the old ldf file
7. restarted sql. at this point the status of test db is now suspect. strangely, i have done these steps when i'm restoring db to a new server and worked perfectly fine. only this time that i am having difficulty finding the problem.

is there a way i can proceed to recover the last transaction if this is the scenario?

Others may have a better way of testing the tail-log backup, would appreciate your suggestions/comments.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 03:51:39

Would this do:

Take a LOG backup - not need to use it, it restricts the next Log Backup to "recent" stuff only

Take a safe copy of MDF file
Take full backup
Make additional test transactions
Stop SQL Service
Copy BACK the saved MDF file
Re-start SQL Service
Take "tail" Log backup

Restore Full backup
Restore "tail" TLog backup
Check that Test record is present

Kristen

maranbe
Starting Member

28 Posts

Posted - 2006-11-15 : 04:09:20

hi kristen,

so the scenario i decsribed earlier won't be able to backup the tail-log? actually, i'm coming from the scenario that suddenly our production db failed due to corruption and i need to recover the latest transactions...thanks for your immediate response.

Kindest regard,

SQL Server 2K (Clustered)/2K5
_________________________
Our life is frittered away by detail ... Simplify, simplify.

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 05:14:39

"so the scenario i decsribed earlier won't be able to backup the tail-log?

2. deleted the test db mdf file"

Well, after step 2 you won't be able to connect to the database to backup the TLog!

If your MDF got corrupted (i.e. real world corruption) you would probably still be able to connect to the database, and make your tail-log.

If you could NOT connect to the database then I would try:

Stop SQL Service (so file not in use. Do NOT use DETACH)
Take a Safe copy of LDF, or just RENAME it if time is critical)
Start SQL Service
Restore the latest FULL backup
Stop SQL Service, or Detach database
Rename the LDF and COPY the Safe-copy LDF in its place (do NOT just Rename your Safe copy - you need to keep that JustInCase!)
Start SQL Service, or Re-attach
Take the "tail" TLog backup

NOTE: This is only relevant if your Recovery model is FULL. If your recovery model is SIMPLE then your own recourse is your last full backup, or a Log Reader like Lugent/Lumiscent(sp?)

I would highly recommend that for a database in Full Recovery Model you should be taking TLog backups every 10 minutes, so that your maximum exposure for this sort of corruption is 10 minutes.

If you have a real-word corruption you may want to post in the Data Corruption forum
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=22
with a link back to this thread

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources