Restoring MSDB

David-Betteridge — Mon, 27 Aug 2012 21:21:00 GMT

We recently performed a disaster recovery exercise which included the restoration of the MSDB database onto our DR server. I did a quick google to see if there were any special considerations and found the following MS article. Considerations for Restoring the model and msdb Databases (http://msdn.microsoft.com/en-us/library/ms190749(v=sql.105).aspx). It said both the original and replacement servers must be on the same version, I double-checked and in my case they are both SQL Server 2008 R2 SP1 (10.50.2500)..

So I went ahead and stopped SQL Server agent, restored the database and restarted the agent. Checked the jobs and they were all there, everything looked great, and was until the server was rebooted a few days later.

Then the syspolicy_purge_history job started failing on the 3rd step with the error message

“Unable to start execution of step 3 (reason: The PowerShell subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.”

A bit more googling pointed me to the msdb.dbo.syssubsystems table

SELECT * FROM msdb.dbo.syssubsystems WHERE start_entry_point ='PowerShellStart'

And in particular the value for the subsystem_dll. It still had the path to the SQLPOWERSHELLSS.DLL but on the old server. The DR instance has a different name to the live instance and so the paths are different.

This was quickly fixed with the following SQL

Use msdb;

sp_configure 'allow updates', 1 ;

RECONFIGURE WITH OVERRIDE ;

UPDATE msdb.dbo.syssubsystems SET subsystem_dll='C:\Program Files\Microsoft SQL Server\MSSQL10_50.DR\MSSQL\binn\SQLPOWERSHELLSS.DLL' WHERE start_entry_point ='PowerShellStart';

sp_configure 'allow updates', 0;

RECONFIGURE WITH OVERRIDE ;

Stopped and started SQL Server agent and now the job completes.

I then wondered if anything else might be broken,

SELECT subsystem_dll FROM msdb.dbo.syssubsystems

Shows a further 10 wrong paths – fortunately for parts of SQL (replication, SSIS etc) we aren’t using!

Lessons Learnt

1. DR exercises are a good thing!

2. Keep the Live and DR environments as similar as possible.

sp_help

David-Betteridge — Tue, 20 Mar 2012 18:14:00 GMT

One of the nice things about SQL Server Management Studio (SSMS) is that you can highlight a table name in a script and press Alt + F1 to perform sp_help on it.

Unfortunately I've never been able to use that feature as the majority of the tables in our product belong to a schema other than dbo.

On a long train journey back to York I wondered if I could solve this problem by writing my own replacement for sp_help (which I’ve called sp_help_table_schemas).

My version works by first checking the system tables to find out which schemas the table belongs to

SELECT s.Name --Find the schema

FROM sys.schemas s

JOIN sys.tables t on t.schema_id = s.schema_id

WHERE t.name = 'Orders'

It then dynamically calls the standard sp_help method but this time supplying the table owner as well.

SET @cmd = 'EXEC sp_help ''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ''' ;' ;

EXEC ( @cmd )

Once I had proved the basics worked I wrapped it up into a stored procedure and deployed it to the master database on my laptop. It was then just a question of going into Tools à Options within SSMS and defining the keyboard short cut

A couple of notes

You can’t amend the existing Alt+F1 entry so I went with Ctrl+F1 for my replacement.
You need to open a new query window for the change to be picked up

So I can now highlight a table name and press Ctrl+F1

The completed script is attached.

Thanks go to Martin Bell who reviewed my stored procedure and give some valuable advice.

David Betteridge

Restoring MSDB

sp_help