How to recover corrupt database mail profile in SQL2005

[ReK410]

Long story short - someone made changes to the SQL2005 dbase mail profile that one of our applications uses for sending mail.

No one knows what the settings should be changed back to and the app is tossing error messages out.

I do have backups of all the databases and the file system. So what do I restore in order to retreive these settings?

 

[ptheriault]

It only takes a couple of minutes to create a mail profile. I would just delete the profile and re-create it.

How do you know it is corrupt?
What is the error message you are getting?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ReK410]

well, that's the problem. I don't know what settings to use. I'm pretty sure that our business app uses profile named "whatever" with specific setting (like the reply to for example) and no one is sure what those settings are or what the profile's name was or even if it was a public or private profile with security.

 

[ptheriault]

Have you tried to send a test email with that account?
What is the error you receive?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ReK410]

The test email from within Management Studio works correctly.

The business app that needs it id failing when it tries to send it's automated emails (for example, if a customer orders more than what their credit limit allows, an email is sent to Accounting)

The problem is that the business app doesn't actually show errors. I only know it failed because it says there is an alert because email failed.

I know it has to do with the database mail profile because it only started happening after it was changed yesterday morning.

Spent some time on the phone with the tech support for the business app and they were stumped.

 

[mrdenny]

I beleive that the database mail settings are all stored in the msdb database. Backup the msdb database, then restore an old version of it (any job changes will be lost, as well as job history).

If the database mail works now, write the settings down. If it doesn't restore the backup that you just took.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ReK410]

Denny, thanks - I think I'm going to play it safe and restore the msdb to a development server's copy of SQL2005 so I don't accidentally effect the jobs. Hopefully once it's been restored I can simply open the management studio, expand database mail and just take some notes on how the configuration was setup

 

[mrdenny]

This is true, that should work.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ReK410]

man, this is a lot more complicated that I thought! I installed a new virual machine with WinServer 2k3 RC2 and then installed SQL2005 and tried to do a master dbase restore.... it failed saying that the SQL version was too old (I forgot to install the SQL2005 SP1)

So I'm installing SP1 now and it's tossing out errors left and right for files that are locked so I stop SQL and it continues and then throws and error saying it can't keep going because the services are locking files.....but I'm making progress!

 

[mrdenny]

You need to restore msdb not master.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ReK410]

Since I needed one anyway, I'm going to make a duplicate of our production server so I figured I might as well start from the ground up with both master and msdb

 

[mrdenny]

Ah. To restore master you have to start the SQL Server from the command line instead of from the service using the -m -c switches.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com