Backing up database mail configuration and linked servers

Question

Backing up database mail configuration and linked servers

Adrian Strudwick

SSC Enthusiast

Points: 192
More actions
June 23, 2011 at 9:52 am

#145575

Hi all,
Environment is SQL 2005 SP3 (9.0.4053). I need to make sure that we have a backup of the database mail configuration and of the linked servers.
Is all of this stored in MSDB? If so, I presume that a simple restore of last good MSDB backup would recover all of this, in the event of a server failure(?)
Many thanks,
Adrian

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Yes. Data for both are stored in msdb. In addition, you can script linked servers in SSMS by right-clicking on one and selecting 'Script Linked Server as...'.

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

i htink you also need a backup of master just in case;

I thought the CREDENTIAL for any database mail username/pass used for the AUTH command are in master, and i think the same goes for a linked server that is using a specific username and password;

I reverse engineered database mail settings before,a nd found that was the one thing i couldn't script out...credentials.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

george sibbald SSC Guru Points: 104210 More actions · Answer 3

info on linked servers is held in master, so a restore of msdb would not recover those.

---------------------------------------------------------------------

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

george sibbald (6/23/2011)
info on linked servers is held in master, so a restore of msdb would not recover those.

So it is. I guess I was fooled when I saw catalog views in msdb.

Greg

Adrian Strudwick SSC Enthusiast Points: 192 More actions · Answer 5

Thanks guys - will make sure we have both backed up then (think we do already - certainly MSDB is).

I was aware of scripting out the linked server creation but wanted to avoid the risk of someone forgetting to do that if they add another one. Maybe I'll do both approaches as a belt-and-braces. Presumably a job with some sort of loop may be able to export all linked servers to a script every night? I could then add that script to our backups...

Cheers,

Adrian

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Adrian Strudwick (6/24/2011)
Thanks guys - will make sure we have both backed up then (think we do already - certainly MSDB is).
I was aware of scripting out the linked server creation but wanted to avoid the risk of someone forgetting to do that if they add another one. Maybe I'll do both approaches as a belt-and-braces. Presumably a job with some sort of loop may be able to export all linked servers to a script every night? I could then add that script to our backups...
Cheers,
Adrian

What? Scripting out? You mean your team doesn't make all server config changes via T-SQL scripts before checking those scripts into a version control repository? 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Adrian Strudwick SSC Enthusiast Points: 192 More actions · Answer 7

If only! One of the 'joys' of having your boss wearing a second hat & third hat as a developer and also retaining dba rights. Hard to rein in... 😉

Edie Richardson Newbie Points: 9 More actions · Answer 8

Adrian, we need to do the same thing. Do you mind sharing your code that scripts out the linked servers and email settings?

Thanks, don't care to recreate the wheel if I don't have to.:-)

Adrian Strudwick SSC Enthusiast Points: 192 More actions · Answer 9

Edie,

For linked server, you can right click, then click "script linked server as" followed by "Create to" then your destination (new query, clipboard etc).

Not worked out how to parse through the list and do this dynamically for all linked servers. As mentioned above, looks like the best way to avoid losing for email settings is simply back up master and msdb.

Adrian