Tail Backup on Corrupted SQL2000 LDF File

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

General SQL Server Forums

Data Corruption Issues

Tail Backup on Corrupted SQL2000 LDF File

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 11:30:22

OK, spoke too soon regarding the successful tail backup of a rescued file. (Turns out that they first restored a virtual disk copy from December, that caused SQL Agent to run and create a TLog backup [as of December] ... then they attached the rescued MDF / LDF from yesterday ... so the TLog backup I was using was not up-to-the-moment like I thought :(

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155482

So:

Rescued MDF / LDF for a database has attached OK.

DBCC CHECKDB finds lots of errors

I tried a TLog backup:


BACKUP LOG MyDatabase2
	TO DISK = 'D:\DATA\MSSQL\BACKUP\MyDatabase2_TailLog.TRN' 
	WITH 
	DESCRIPTION = 'MyDatabase2 Tail Log Backup'
	, STATS = 10
	, NO_TRUNCATE

and I get:
"10 percent backed up.
20 percent backed up.
Server: Msg 3241, Level 16, State 40, Line 1
The media family on device 'D:\DATA\MSSQL\BACKUP\MyDatabase2_TailLog.TRN' is incorrectly formed. SQL Server cannot process this media family.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally."

any ideas please?

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 11:53:58

SQL Error log has this:


[This is the moment that the SQL Backup started]
2011-01-20 16:50:40.18 spid53    BlkHeader from strip 0 At a4ada00 ExpectedAt a15da00 Size f000 PrevSize f000
2011-01-20 16:50:40.18 spid53    BlkHeader from strip 0 At a15e000 ExpectedAt a15e000 Size 400 PrevSize 400
... hundreds of lines like this ...
2011-01-20 16:50:40.19 spid53    BlkHeader from strip 0 At a210800 ExpectedAt a210800 Size 1400 PrevSize 400
2011-01-20 16:50:40.19 spid53    BlkHeader from strip 0 At a211c00 ExpectedAt a211c00 Size 400 PrevSize 1400
2011-01-20 16:50:40.19 backup    BACKUP failed to complete the command BACKUP LOG MyDatabase2
...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 12:13:50

Are you trying to append to a backup file?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 12:53:30

Nope, brand new filename

(I thought it odd that it refers to the media + Backup filename, but maybe that's jsut how the error message is formed?

Got the same thing on a different database (which I could not attach) as per this other thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155482##611429

(I've changed the names to MyDatabase, by this thread and that one are two different databases, on same server)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 12:59:49

Could it be that the drive you're backing up to is damaged? Try something else, even a flash drive?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 13:34:23

I did try another drive on the same machine. Not quite sure how the drives are arranged, they might resolve to the same physicals! I'll check that, but AFAIK the drives are brand new replacements

I think the IT guys did:

Restore from December's Virtual Machine Copy
Database files, which were rescued from the failed server's disks, were attached in place of the December ones

MDF/LDF of both the December and January versions are in "recovery folders" on the machine.

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 13:36:53

Sorry, another question:

What's your feeling about the nuance of the message

"The media family on device 'D:\DATA\MSSQL\BACKUP\MyDatabase2_TailLog.TRN' is incorrectly formed. SQL Server cannot process this media family."

I'm struggling with it referring to the backup file I'm making, and refering to the media family being incorrectly formed.

Ah ... possible thought.

MSDB is corrupted. I was leaving sorting that out until later, I wonder if that is the issue?

I propose just to restore an old backup - there have been no new JObs etc. created, so I reckon we will only lose Backup History.

Let me know what you think, but I'll try that anyway.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 14:10:38

Generally it means that the backup header is damaged.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 14:12:38

Thanks. I wondered if because MSDB was corrupted there was a problem saving backup history records there.

Kristen
Test

22859 Posts

Posted - 2011-01-20 : 14:55:54

Restoring MSDB didn't make any difference to the ability to take a Tail Log Backup from the corrupted databases

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-20 : 15:33:17

No it won't. That was an error that the backup header in the backup file is damaged, not the backup history in MSDB.

Me thinks database too damaged to take tail-log. Does happen. Hope you have real backups, unlike the other person with a suspect database today (who seems to only have copies of the mdf file)

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-01-21 : 06:00:31

For completeness: DBCC CHECKDB for this database is in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155537

deviprasad
Starting Member

5 Posts

Posted - 2011-12-28 : 07:42:10

You can get more details on fixing this error on this link [url]http://sqlserverlearner.com/2011/the-media-family-on-device-is-incorrectly-formed-sql-server-cannot-process-this-media-family-error-3241[/url]

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-28 : 09:25:32

That link has nothing whatsoever to do with the problem in this thread. From that link:
"When you try to restore a backup of the database you get the following error:"

Kristen is trying to take a backup, not restore a backup.

p.s. Your post is also wrong, the only time that is the message when restoring higher version to lower is when the version is SQL 2000 (and not even in all cases then). Otherwise the error is "The database was backed up on a server running version <source version>. That version is incompatible with this server, which is running version <destination version>. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server."

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources