top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How we can rebuild Master DataBase in SQL Server

+1 vote
140 views
How we can rebuild Master DataBase in SQL Server
posted Mar 31, 2014 by Neeraj Pandey

Share this question
Facebook Share Button Twitter Share Button Google+ Share Button LinkedIn Share Button Multiple Social Share Button
Perform the following tasks before you rebuild the system databases to ensure that you can restore the system databases to their current settings.
1. Record all server-wide configuration values.

    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\MSSQL11.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 or x64) and then navigate to **setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.**

1 Answer

0 votes

Perform the following tasks before you rebuild the system databases to ensure that you can restore the system databases to their current settings.
1. Record all server-wide configuration values.

SELECT * FROM sys.configurations;
  1. 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;

  2. 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'));

  3. Locate the current backup of the master, model, and msdb databases.

  4. If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.
  5. 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.
  6. 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\MSSQL11.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 or x64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.
answer Mar 31, 2014 by Amit Kumar Pandey
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...