When
the master, model, msdb, and tempdb system databases are rebuilt, the databases
are dropped and re-created in their original location. If a new collation is
specified in the rebuild statement, the system databases are created using that
collation setting. Any user modifications to these databases are lost. For
example, you may have user-defined objects in the master database, scheduled
jobs in msdb, or changes to the default database settings in the model
database.
Perform
the following tasks before you rebuild the system databases to ensure that you
can restore the system databases to their current settings.
SELECT
* FROM sys.configurations;
2.
Record all service packs and hotfixes applied to the instance of
SQL Server and the current collation. You must reapply these updates after
rebuilding the system databases.
SELECT
SERVERPROPERTY('ProductVersion
') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
SERVERPROPERTY('Collation') AS Collation;
3.
Record the current location of all data and log files for the
system databases. Rebuilding the system databases installs all system databases
to their original location. If you have moved system database data or log files
to a different location, you must move the files again.
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.
Locate the current backup of the master, model, and msdb
databases.
5.
If the instance of SQL Server is configured as a replication
Distributor, locate the current backup of the distribution database.
6.
Ensure you have appropriate permissions to rebuild the system
databases. To perform this operation, you must be a member of the sysadmin
fixed server role. For more information, see Server-Level Roles.
7.
Verify that copies of the master, model, msdb data and log
template files exist on the local server. The default location for the template
files is C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Templates. These files are used during
the rebuild process and must be present for Setup to succeed. If they are
missing, run the Repair feature of Setup, or manually copy the files from your
installation media. To locate the files on the installation media, navigate to
the appropriate platform directory (x86, x64, or ia64) and then navigate to
setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.
8.
Script out all the jobs on the server.
9.
Check the database maintenance plan and make sure that you have
noted down the schedules for each step in the maintenance plan.
10.
Export all the SSIS packages to the file system.
11.
Check whether any mail profiles are present and copy the details
of each profile to a table on one of the user databases.
12.
Script out the logins on server and prepare a list that contains
the permissions provided for each login.
13.
Stop the replication agent if you have replication set up. Use the
table sysarticles to get the list of objects that are being replicated only if
you chose to use GUI to create replication.
14.
Detach all user defined databases using below script.
Rebuilding the System Databases
The
following procedure rebuilds the master, model, msdb, and tempdb system
databases. You cannot specify the system databases to be rebuilt. For clustered
instances, this procedure must be performed on the active node and the SQL
Server resource in the corresponding cluster application group must be taken
offline before performing the procedure.
This
procedure does not rebuild the resource database.
To rebuild system databases for an instance of SQL Server:
1.
Insert the SQL Server 2008 R2 installation media into the disk
drive.
2.
From a command prompt window, enter the following command. Square
brackets are used to indicate optional parameters. Do not enter the brackets.
When using the Windows Vista operating system with User Account Control (UAC)
enabled, running Setup requires elevated privileges. The command prompt must be
run as Administrator.
Setup /QUIET /ACTION=REBUILDDATABASE
/INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD=
StrongPassword ] [ /SQLCOLLATION=CollationName]
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="" /SQLCOLLATION=Latin1_General_CI_AS_KS_WS
3.
When Setup has completed rebuilding the system databases, it
returns to the command prompt with no messages. Examine the Summary.txt log
file to verify that the process completed successfully. This file is located at
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs.
Post Rebuild Task
After rebuilding the database you may need to perform the following additional tasks:
- Restore your most recent full backups of the master, model, and msdb databases.
- Attach all the user databases
- Recreate the linked servers and logins. Check for orphaned users using the command
- Create all the mail profiles (tailor the script given in step6 and use it).
- Import the SSIS packages to the SQL Server from the file system.
- Recreate all the jobs.
- Create the maintenance plan using schedules.
- Setup replication.
- Create logins.
.jpg)
