Rebuilding System Databases

Step#3 : Post Rebuild Steps
1. Apply the latest service pack and any applicable hot fixes.

2. Restore your current full backups of the master, model, and msdb databases.
NOTE: If you have changed the server collation, do not restore the system databases as it will replace the new collation with the previous setting.

3. If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them (sole reason why change management is of great essence).

4. If the instance of SQL Server is configured as a replication Distributor, then restore the distribution database as well.

5. If required, move the system databases to the locations you recorded previously.

6. Verify the server-wide configuration values match the values you recorded previously.

Rebuilding System Databases

Step#2[b] : Rebuild the resource Database Procedure

When you rebuild the resource database, all service packs and hot fixes are lost, and therefore steps below must be followed.

1. Launch the SQL Server Setup program (setup.exe) from SQL Server 2008 distribution media.

2. In the left navigation area, click Maintenance, and then click Repair.

3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.

4. On the Select Instance page, select the instance to repair, and then click Next.

5. The repair rules will run to validate the operation. To continue, click Next.

6. From the Ready to Repair page, click Repair. The Complete page message indicates that the operation is finished.

Rebuilding System Databases

Step#2[a] : Rebuild Procedure (for all except resource database)
1. Execute C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe within installation media using command prompt.

2. From a command prompt window, enter the following command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

PS: Use SAPWD switch only when server was using Mixed Authentication mode. If it was using OS Authentication, then you don’t need to use it.

3. When Setup has completed rebuilding the system databases, examine the C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs\Summary.txt log file to verify that the process completed successfully.

Rebuilding System Databases

Step#1 : Pre Build Steps

1. Identify all server-wide configuration values. Query below can be used:

SELECT * FROM sys.configurations;

2. Identify all currently deployed service packs and hot fixes and the current collation. This is so you can reapply these updates after rebuilding the system databases. Query below can be used to identify this information:

SELECT SERVERPROPERTY(‘ProductVersion ‘) AS ProductVersion, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel, SERVERPROPERTY(‘ResourceVersion’) AS ResourceVersion,

SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS ResourceLastUpdateDateTime, SERVERPROPERTY(‘Collation’) AS Collation;

3. Identify the locations of all data and log files for all system databases. This is so you can move the files to current locations from default installation locations after system database rebuild. Query below can be used to identify this information:

SELECT name, physical_name AS current_file_location FROM sys.master_files WHERE database_id IN (DB_ID(‘master’), DB_ID(‘model’), DB_ID(‘msdb’), DB_ID(‘tempdb’));

4. Identify backup of the master, model, and msdb databases.

5. If SQL Server instance is configured as replication Distributor, locate the usable / active backup of the distribution database.

6. Ensure you have sysadmin fixed server role so you can rebuild the system.

7. Verify that copies of the master, model, msdb data and log template files exist on the local server i.e. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Templates. If they are missing, run the Repair feature of Setup, or manually copy the files from your installation media. On the installation media, you can find them within specific platform directory (x86, x64, or ia64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.