November 23, 2013 at 9:07 am
HI,
I unfortunately i deleted data into these table in my msdb,
sysmail_servertype
sysmail_server
Then i trying to create new profile and account, i getting this error,
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__sysmail_s__serve__74444068". The conflict occurred in database "msdb", table "dbo.sysmail_servertype", column 'servertype'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
Can i restore new msdb from Resource db(I dont have backup of msdb) or what is default values in this sysmail_server table
November 24, 2013 at 1:24 pm
Always backup your system databases!
Before trying anything drastic, I would look at the data in those tables on a different instance and try to figure out what data needs to be reinserted to the tables.
November 25, 2013 at 2:21 am
If you restore the MSDB from the resource you'll also loose all history-information about backups and restores. Also all jobs and jobhistory will be lost. All this information is stored in MSDB, so only restore a database from its own backups.
You could try to recreate the deleted data like Roobert Davis mentioned in the previous post, but I think it will be very hard to do. In my opinion your best option is to first create a good back-up of the MSDB (and test this with a restore!). Second: Figure out the foreign key relation that throws the error when creating a new profile. Delete the corresponding rows in the related table(s). Try to recreate the mail profile after each delete.
November 25, 2013 at 6:35 am
HanShi (11/25/2013)
If you restore the MSDB from the resource you'll also loose all history-information about backups and restores. Also all jobs and jobhistory will be lost. All this information is stored in MSDB, so only restore a database from its own backups.You could try to recreate the deleted data like George Davis mentioned in the previous post, but I think it will be very hard to do. In my opinion your best option is to first create a good back-up of the MSDB (and test this with a restore!). Second: Figure out the foreign key relation that throws the error when creating a new profile. Delete the corresponding rows in the related table(s). Try to recreate the mail profile after each delete.
First of all, please read Robert's advice above about backing up system databases.
I think sysmail_servertype can be rebuilt by creating a single row for the SMTP server type.
As for sysmail_server, this is a list of your mail servers used by each account in sysmail_account. (sysmail_server.account_id = sysmail_account.account_id.) If you query sysmail_account, you'll get all the keys you need to recreate in sysmail_account. As for the values you need to specify in sysmail_server, without a backup, you're only going to be guessing, but your guess is going to be more educated than any of our guesses since we're not familiar with your database mail setup. I would also try querying another instance for clues and hope you implemented mail servers similarly in both instances. The more mail servers you were using will make this more difficult.
When you're done, plan out and implement a backup schedule for your system databases. Then, test your restores. Please don't skip this step. If you had a good backup of msdb, this wouldn't be such a big deal.
November 25, 2013 at 6:46 am
Robert Davis (11/24/2013)
Always backup your system databases!Before trying anything drastic, I would look at the data in those tables on a different instance and try to figure out what data needs to be reinserted to the tables.
+1 on this.... I can't imagine it would be too difficult to try to figure out what needs to be in these tables.
Good Luck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply