Scroll Bar


you can participate with latest Interview Questions here. Use this mail ID (bhanudba15@gmail.com) to send Your Questions.

Rebuild Master Database

 SQL Server 2000 

To rebuild the master database

1. Shutdown Microsoft® SQL Server™ 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

(C:\Program Files\Microsoft SQL Server\80\Tools\BINN\Rebuildm.exe)

2. In the Rebuild Master dialog box, click Browse.

3. In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.

4. Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.

Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK. 

5. In the Rebuild Master dialog box, click Rebuild to start the process.

The Rebuild Master utility reinstalls the master database.

 

SQL Server 2005

Need CD

1.START /WAIT <media drive and path>\setup.exe /qn INSTANCENAME=<INSTANCE NAME> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<STRONG SA PASSWORD>

 Check summery.txt

2. Test the server by starting the instance in single-user mode and connect using SQLCMD

C:\>net start MSSQLSERVER /m 

Log out of the server and shut down the instance

C:\>net stop MSSQLSERVER

Restore MASTER database using your latest SQL Server full database backup. This MUST be run in single user mode. 

In the previous step we logged out and shutdown the instance. Start the SQL instance in single-user mode via command prompt, connect using SQLCMD and restore master database. See steps below.


C:\>net start MSSQLSERVER /m 


C:>\sqlcmd -S <server name>


1 - restore database master from disk='C:\TheDBAVault\Backup\SystemDB\master.bak' with replace

2 - GO

 

SQL Server 2008 & 2014 ...

Prerequisites

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\MSSQL12.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.

To rebuild system databases for an instance of SQL Server:

1. Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

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 a Windows operating system that has 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]


No comments:

Post a Comment

DisableRC