INF: How to Rebuild the Repository in SQL Server 7.0 (238822)

MORE INFORMATION

To rebuild the repository, perform the following steps:

1. Use the following script to drop all the tables in the msdb database starting with Rtbl%, Dbm%, Dtm%, Dts%, Gen%, Mds%, Ocl%, Olp%, Tfm%, Uml%, Umx% (where % represents the remaining characters in the table's name).
   
   CAUTION: Before you execute the script, it is strongly recommended that you backup the MSDB database!

   Use msdb
   go
   drop table  DbmColumn                                                                                                                        
   go
   drop table  DbmColumnSet                                                                                                                     
   go
   drop table  DbmConnection                                                                                                                    
   go
   drop table  DbmDataType                                                                                                                      
   go
   drop table  DbmIndex                                                                                                                         
   go
   drop table  DbmMiscellaneous                                                                                                                 
   go
   drop table  DbmPackage                                                                                                                       
   go
   drop table  DbmProvider                                                                                                                      
   go
   drop table  DbmProviderDataType                                                                                                              
   go
   drop table  DbmProviderTypeMapping                                                                                                           
   go
   drop table  DbmReferentialRole                                                                                                               
   go
   drop table  DbmUmlOperation                                                                                                                  
   go
   drop table  DtmNamespace                                                                                                                     
   go
   drop table  DtmObjectType                                                                                                                    
   go
   drop table  DtmObjectTypeMapping                                                                                                             
   go
   drop table  DtsBulkInsertTask                                                                                                                
   go
   drop table  DtsNode                                                                                                                          
   go
   drop table  DtsOLEDBProperty                                                                                                                 
   go
   drop table  DtsSendMailTask                                                                                                                  
   go
   drop table  DtsStepExecution                                                                                                                 
   go
   drop table  DtsTransferObjectsTask                                                                                                           
   go
   drop table  GenElement                                                                                                                       
   go
   drop table  GenMiscellaneous                                                                                                                 
   go
   drop table  MdsStore                                                                                                                         
   go
   drop table  OclSequence                                                                                                                      
   go
   drop table  OlpDimension                                                                                                                     
   go
   drop table  OlpDimHierarchy                                                                                                                  
   go
   drop table  OlpDimLevel                                                                                                                      
   go
   drop table  OlpMappingLevelPair                                                                                                              
   go
   drop table  RTblClassDefs                                                                                                                    
   go
   drop table  RTblDatabaseVersion                                                                                                              
   go
   drop table  RTblIfaceDefs                                                                                                                    
   go
   drop table  RTblIfaceMem                                                                                                                     
   go
   drop table  RTblNamedObj                                                                                                                     
   go
   drop table  RTblPropDefs                                                                                                                     
   go
   drop table  RTblProps                                                                                                                        
   go
   drop table  RTblRelColDefs                                                                                                                   
   go
   drop table  RTblRelshipProps                                                                                                                 
   go
   drop table  RTblRelships                                                                                                                     
   go
   drop table  RTblRoot                                                                                                                         
   go
   drop table  RTblSites                                                                                                                        
   go
   drop table  RTblSumInfo                                                                                                                      
   go
   drop table  RTblTypeLibs                                                                                                                     
   go
   drop table  RTblVersionAdminInfo                                                                                                             
   go
   drop table  RTblVersions                                                                                                                     
   go
   drop table  RTblWorkspaceItems                                                                                                               
   go
   drop table  TfmCodeDecodeValue                                                                                                               
   go
   drop table  TfmDependency                                                                                                                    
   go
   drop table  TfmElement                                                                                                                       
   go
   drop table  TfmPackage                                                                                                                       
   go
   drop table  TfmStepExecution                                                                                                                 
   go
   drop table  TfmTransformation                                                                                                                
   go
   drop table  TfmTransformationTask                                                                                                            
   go
   drop table  UmlAssociationRole                                                                                                               
   go
   drop table  UmlAttribute                                                                                                                     
   go
   drop table  UmlElement                                                                                                                       
   go
   drop table  UmlGeneralizableElement                                                                                                          
   go
   drop table  UmlMember                                                                                                                        
   go
   drop table  UmlNote                                                                                                                          
   go
   drop table  UmlOperation                                                                                                                     
   go
   drop table  UmlParameter                                                                                                                     
   go
   drop table  UmlPoint                                                                                                                         
   go
   drop table  UmlProjection                                                                                                                    
   go
   drop table  UmlReference                                                                                                                     
   go
   drop table  UmlSignalReference                                                                                                               
   go
   drop table  UmlStereotype                                                                                                                    
   go
   drop table  UmlTaggedValue                                                                                                                   
   go
   drop table  UmlType                                                                                                                          
   go
   drop table  UmlValue                                                                                                                         
   go
   drop table  UmxMiscellaneous                                                                                                                 
   go
   					

2. Use the script below to drop all stored procedures starting with r_%. This step is not always required and should be executed if you are experiencing problems with the repository's stored procedures. These stored procedures are part of the repository.

   Use msdb
   go
   drop proc  r_iUmlType                                                                                                                       
   go
   drop proc  r_iUmlValue                                                                                                                      
   go
   drop proc  r_iTfmStepExecution                                                                                                              
   go
   drop proc  r_iUmlAssociationRole                                                                                                            
   go
   drop proc  r_iTfmTransformationTask                                                                                                         
   go
   drop proc  r_iDbmConnection                                                                                                                 
   go
   drop proc  r_iUmlTaggedValue                                                                                                                
   go
   drop proc  r_iDtsSendMailTask                                                                                                               
   go
   drop proc  r_iTfmElement                                                                                                                    
   go
   drop proc  r_iTfmDependency                                                                                                                 
   go
   drop proc  r_iDbmColumnSet                                                                                                                  
   go
   drop proc  r_iRTblSumInfo                                                                                                                   
   go
   drop proc  r_iOlpDimension                                                                                                                  
   go
   drop proc  r_iGenMiscellaneous                                                                                                              
   go
   drop proc  r_iOlpMappingLevelPair                                                                                                           
   go
   drop proc  r_iOlpDimLevel                                                                                                                   
   go
   drop proc  r_iGenElement                                                                                                                    
   go
   drop proc  r_iOlpDimHierarchy                                                                                                               
   go
   drop proc  r_iDbmColumn                                                                                                                     
   go
   drop proc  r_iUmlAttribute                                                                                                                  
   go
   drop proc  r_iUmlSignalReference                                                                                                            
   go
   drop proc  r_iUmlStereotype                                                                                                                 
   go
   drop proc  r_iUmlNote                                                                                                                       
   go
   drop proc  r_iTfmTransformation                                                                                                             
   go
   drop proc  r_iUmlPoint                                                                                                                      
   go
   drop proc  r_iDbmProvider                                                                                                                   
   go
   drop proc  r_iDtmNamespace                                                                                                                  
   go
   drop proc  r_iRTblVersions                                                                                                                  
   go
   drop proc  r_iDbmReferentialRole                                                                                                            
   go
   drop proc  r_iRTblRelships                                                                                                                  
   go
   drop proc  r_iRTblSites                                                                                                                     
   go
   drop proc  r_iRTblProps                                                                                                                     
   go
   drop proc  r_iRTblRelshipProps                                                                                                              
   go
   drop proc  r_iRTblPropDefs                                                                                                                  
   go
   drop proc  r_iRTblRelColDefs                                                                                                                
   go
   drop proc  r_iRTblIfaceDefs                                                                                                                 
   go
   drop proc  r_iRTblClassDefs                                                                                                                 
   go
   drop proc  r_iDbmMiscellaneous                                                                                                              
   go
   drop proc  r_iRTblTypeLibs                                                                                                                  
   go
   drop proc  r_iMdsStore                                                                                                                      
   go
   drop proc  r_iDbmPackage                                                                                                                    
   go
   drop proc  r_iDtmObjectTypeMapping                                                                                                          
   go
   drop proc  r_iDtmObjectType                                                                                                                 
   go
   drop proc  r_iDbmDataType                                                                                                                   
   go
   drop proc  r_iRTblIfaceMem                                                                                                                  
   go
   drop proc  r_iRTblWorkspaceItems                                                                                                            
   go
   drop proc  r_iRTblRoot                                                                                                                      
   go
   drop proc  r_iDbmProviderDataType                                                                                                           
   go
   drop proc  r_iRTblNamedObj                                                                                                                  
   go
   drop proc  r_iDbmProviderTypeMapping                                                                                                        
   go
   drop proc  r_iDbmIndex                                                                                                                      
   go
   drop proc  r_iUmlGeneralizableElement                                                                                                       
   go
   drop proc  r_iUmlOperation                                                                                                                  
   go
   drop proc  r_iUmlElement                                                                                                                    
   go
   drop proc  r_iOclSequence                                                                                                                   
   go
   drop proc  r_iTfmCodeDecodeValue                                                                                                            
   go
   drop proc  r_iUmlReference                                                                                                                  
   go
   drop proc  r_iRTblVersionAdminInfo                                                                                                          
   go
   drop proc  r_iDtsNode                                                                                                                       
   go
   drop proc  r_iUmlMember                                                                                                                     
   go
   drop proc  r_iDtsOLEDBProperty                                                                                                              
   go
   drop proc  r_iDbmUmlOperation                                                                                                               
   go
   drop proc  r_iDtsStepExecution                                                                                                              
   go
   drop proc  r_iUmlParameter                                                                                                                  
   go
   drop proc  r_iDtsBulkInsertTask                                                                                                             
   go
   drop proc  r_iTfmPackage                                                                                                                    
   go
   drop proc  r_iUmlProjection                                                                                                                 
   go
   drop proc  r_iDtsTransferObjectsTask                                                                                                        
   go
   drop proc  r_iUmxMiscellaneous                                                                                                              
   go
   					

3. Performing the preceding two steps should remove the existing repository. The next step is to rebuild the repository, using the utility extracted from RepRbld.exe, located at the SQL Server Downloads page at:

   RepRbld.exe

   Before running this utility, please read the Readme.txt file and follow the instructions on how to use it.
   
   NOTE: It is important to remove the repository (that is, do steps 1-2 above) if you completely want to rebuild it because this utility does not re-create tables if they already exist.

In some cases, you may not be concerned about the existing msdb, and it may be easier to completely rebuild msdb. In those situations, you can drop and re-create msdb and then run the Repository Rebuild utility. To rebuild msdb, perform the steps below.

WARNING: When you rebuild msdb, any existing tasks, alerts, operators, replication information, Data Transformation Services (DTS) packages saved locally, and DTS packages saved in the repository will be lost.

1. Stop the SQLServerAgent service.
2. Drop the existing msdb database.
3. Use Query Analyzer to run the Run the Mssql7\Install\Instmsdb.sql script; this script creates the database and the necessary tables.
4. Run the Repository Rebuild utility.
5. Start the SQLServerAgent service.
6. Test the rebuild by attempting to add tasks, alerts, and operators, save DTS packages, and so on.

For information about how to rebuild msdb, on SQL Server 6.0 or 6.5, please click the article number below to view the article in the Microsoft Knowledge Base: