Thursday, April 19, 2012

script system object in resource database

When I troulbshooting the logshipping problem, I found many logshipping store procedure can not be found in the master or msdb database. for instance, in the master. [sys].[sp_add_log_shipping_secondary_primary] , it calls sys.sp_MSprocesslogshippingjob to create the copy job, however, sys.sp_MSprocesslogshippingjob is in the resource database, we can not review its code with sp_helptext command.

In order to check system object script, you need to follow up the steps below
1. open command prompt windows with administration, nevigate to sql server binn folder, by default it looks like C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

2. run the command below
sqlservr -c -m

3. open another command prompt windows, run
sqlcmd -E -A
sqlcmd -E -A -S <instancename>

4. run the command below to get the sys object script

use mssqlsystemresource
:out c:\temp\script.txt
sp_helptext sp_MSprocesslogshippingjob

:out set the output desitnation.

you can also use another way to get the system object script

1.  copy to the mssqlsystemresource.mdf and mssqlsystemresource.ldf from C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn to other data folder
2. attach the mssqlsystemresource.mdf  with a new database name, let's say "RS"
3. open SSMS, swith to RS, then you can find the system store procedure sp_MSprocesslogshippingjob
under RS.

No comments:

Post a Comment