Our test database is linked to a database owned by another department within our company. Whenever they bring their database down (like when refreshing with production data) our application goes down as well. The only thing we are doing with their database is we have a view that selects from one of their tables and we join to this view in a number of queries.
Ideally, whenever their system goes down, I'd like our view to pull from a backup of their table that exists in our database. It has slightly stale data, but at least we would be able to continue working. I thought of using a TRY...CATCH in the view or in a sql function, but they are not supported in those. A stored procedure might work, except that you can't join to the results of a stored procedure in queries, can you?
How can I make my SELECT statements fall back to a backup table when the linked server's table is unavailable?
So what I ended up doing was to create a SQL Server Agent job that calls sp_testlinkedserver in a TRY...CATCH every few minutes and if it's down we alter the view to point to our backup table and if it's up, we alter it to point to the "live" data again. We also track the previous state so we only alter the view if the state has changed. It works pretty slick.