August 2, 2010 at 8:10 am
From a Disaster Recovery perspective, is there a way to backup and restore linked server definitions in SQL Server 2008?
One of our customers has a linked server requirement, and I am looking for a standardized way to make sure these definitions get backed up and are available offsite if we need them. At this point, I am not sure where the information is stored.
August 2, 2010 at 8:16 am
You can find most of the information in the sys.servers view to build a script for this.
Otherwise you could use SMO to script this programatically.
If they are static, you can just use management studio to script them once and save the script somewhere...
August 2, 2010 at 8:21 am
I second that. sys.servers should give you the details.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 3, 2010 at 11:09 am
HowardW (8/2/2010)
You can find most of the information in the sys.servers view to build a script for this.Otherwise you could use SMO to script this programatically.
If they are static, you can just use management studio to script them once and save the script somewhere...
>> ...most...
I see what you mean. I saved the source code that the vendor had me execute. When I compared it to the corresponding row in sys.servers, not everything was there. However, when I scripted the linked server in SQL Server Management Studio, the output seemed to match the saved source code fairly closely.
Scripting seems effective, but it relies on multiple DBAs remembering to do it. I'm thinking it would be better to automate this. Would you by any chance have any related SMO snippets of code that you would be willing to share?
Or, is PowerShell an option?
August 4, 2010 at 6:31 am
This is probably a good place to start:
http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated
It's for 2005, but is applicable to 2008 as well (although you'll want to reference the 2008 dll's in your project).
It gives an example of enumerating through stored procedures, but there's a linkedserver object that's accessed in the same way.
The full documentation is here:
http://technet.microsoft.com/en-us/library/ms162169.aspx
You can also access SMO through Powershell if you want
August 5, 2010 at 10:11 am
Dependent on your DR scenario no scripting may be required.
Usually in a DR situation you will be loading/recovering user databases.
Why not just precede this with a recovery of the master and msdb databases since that is where all of the relevant information is stored.
Easy is usually better !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 5, 2010 at 5:45 pm
rudy komacsar - Doctor "X" (8/5/2010)
Dependent on your DR scenario no scripting may be required.Usually in a DR situation you will be loading/recovering user databases.
Why not just precede this with a recovery of the master and msdb databases since that is where all of the relevant information is stored.
Easy is usually better !
Good point. Maybe all I really need for DR is a list of what is in sysservers that I can use for a checklist after restoring the master database.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply