Backup fails due to damaged MSDB database

Question

Post reply

Backup fails due to damaged MSDB database

David Betteridge

Hall of Fame

Points: 3264
More actions
March 4, 2008 at 11:02 am

#154172

We have a test machine (SQLServer 2000 on Windows 2000) which due to a previous disk error is reaching the end of it's life.
The transaction log for the test database had grown in excess of 17GB due to all backups failing on the server. I have now changed the recovery model to simple as full recovery isn't a requirement.
However, I then tried to take a full backup of the database and it failed due to an checksum error in the msdb database. I am assuming that this is occuring at the end of the backup when it tries to record the history in the msdb database.
I had a quick attempt at rebuilding the system databases but that failed with a -3 code - i haven't invesitgated further, but it might be I'm using the wrong version of sqlserver cd!
More out of interest than anything, what is the best way to continue when the msdb is damaged?
Ideally, I would a take a full backup of the test database instead trying to move a 17GB ldf across the network to a new box
Thanks
David

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Two suggestions

1. Shrink its log file;

2. Backup msdb again;

Or

1. Export data;

2. Rebuild msdb;

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

I assume other backups work (for user databases)?

I'm not sure what you can do other than rebuild msdb from scratch. Or restore from a previous backup.

I'd script out jobs, alerts, packages. Everything else (backup history, job history), probably isn't important.

Then either reinstall SQL, install it on a VM and steal msdb.mdf/ldf, and the run your scripts.

David Betteridge Hall of Fame Points: 3264 More actions · Answer 3

Thanks for the replies.

However I don't think I explained the issue very well 🙂

It's not the backing up of the MSDB which is the issue, it's the fact I can't back up the user database.

When I try to backup a user database the progress bar moves along nicely, but towards the end I get a message saying that a torn page error is reported in the msdb database and no backup is created.

What I am wondering is... is it possible to do a backup without it trying to record the history in the msdb database?

( After the disk errors were first reported, the physical machine did go virtual - but this took the errors and all!)

Thanks again,

David

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Do you have a backup of MSDB?

If you recreate the database, you'll loose all your jobs, DTS packages and a few other things. They're all stored in MSDB.

If you don't have a backup, run

DBCC CHECKDB ('MSDB') WITH No_INFOMSGS

and see what it says.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

That is what I mean.

Two suggestions

If it is the disk issue

1. Shrink its log file;

2. Backup msdb again;

Or, if it a disk or datbase corruption issue

1. Export data (you can export data to a table in other database);

2. Rebuild msdb;

3. Import data back.

If you cannot export data, and cannot backup the database, you may lose your data.

Donn Polix Default port Points: 1453 More actions · Answer 6

There's no option to skip writing to msdb backuphistory in the backup database command, so I'm afraid you can't get away with it. So, you'll have to go with what they suggested to restore/rebuild msdb as you have a torn page detected in it. Or you may just detach the db and move it, which takes longer, if you're not interested in msdb and saving its data.

_____________
Donn Policarpio

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

If you use SQL 7, you may try to expand your database. There is a bug there. But first of all, you had better try your best without changing your existing datbase.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

I dealt with a similar issue last fall and had to recreate the msdb database. There were no backups, I was not aware of the SQL Server, so I also had to recreate all the jobs. Needless to say I am monitoring this SQL Server now.:D

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question