Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Administration Overview

Updated : August 2, 2001

As you get started with Microsoft SQL Server 2000, you should concentrate on these areas:

  • How SQL Server 2000 works with your hardware

  • What versions and editions of SQL Server 2000 are available

  • How SQL Server 2000 works with Microsoft Windows-based operating systems

  • What administration tools are available

On This Page

SQL Server 2000 and Your Hardware
SQL Server 2000 and SQL Server Desktop
SQL Server and Windows 2000
SQL Server and Active Directory
SQL Server Administration Tools

SQL Server 2000 and Your Hardware

Successful database server administration depends on three things:

  • Good database administrators

  • Strong database architecture

  • Appropriate hardware

The first two ingredients are covered: you're the administrator, you're smart enough to buy this book to help you through the rough spots, and you've enlisted SQL Server 2000 to provide your high-performance relational database management system (RDBMS) needs. This brings us to the issue of hardware. SQL Server 2000 should run on a system with adequate memory, processing speed, and disk space. You also need an appropriate data and system protection plan at the hardware level.

Key guidelines for choosing hardware for SQL Server are as follows:

  • Memory A minimum of 256 MB of RAM for Standard Edition and 512 MB of RAM for Enterprise Edition. This is several times the minimum memory requirement of 32 MB and 64 MB respectively; the primary reason for this extra memory is performance. SQL Server 2000 and standard Windows 2000 services together use between 80 and 100 MB of memory as a baseline. User connections consume about 24 KB each. Data requests and other SQL Server processes use memory as well, and this memory usage is in addition to all other processes and applications running on the server.

  • CPU SQL Server 2000 runs only on Intel x86 or compatible hardware. It won't run on Motorola MIPS R4000, PowerPC or DEC Alpha processors. SQL Server achieves solid benchmark performance with Intel Pentium III Xeon 733 MHz and AMD Athlon 1000 MHz. Both CPUs provide good starting points for the average SQL Server system. You can achieve significant performance improvements with a high level of on processor cache. Most Xeon processors ship with 512 KB, 1 MB or 2 MB of on processor cache—and 2 MB of cache yields much better performance overall.

  • SMP SQL Server 2000 supports symmetric multiprocessors and can process complex parallel queries. Parallel queries are valuable only when few users are on a system and you're processing large queries. On a dedicated system that runs only SQL Server and supports fewer than 100 simultaneous users, a single CPU should suffice. If the server supports more than 100 users or doesn't run on a dedicated system, you may want to consider adding processors (or using a system that can support additional processors as your needs grow).

  • Disk drives The amount of data storage capacity you need depends entirely on the number and size of the databases the server supports. You need enough disk space to store all your data plus work space, system files, virtual memory, transaction logs, and, in the case of a cluster, the quorum disk. I/O throughput is just as important as drive capacity. In most cases SCSI (Small Computer System Interface) drives are faster than IDE/EIDE (Integrated Device Electronics/Enhanced Integrated Device Electronics), and therefore I recommend them. For the best I/O performance, FC (Fiber Channel) is the recommended choice for high-end storage solutions. Instead of using a single large drive, you should use several smaller drives, which allows you to configure fault tolerance with RAID (redundant array of independent disks). I recommend separating data and logs and placing them on separate drives. This includes the quorum disk for clustering.

  • Data protection You should add protection against unexpected drive failures by using RAID. For data, use RAID 0 or RAID 5. For logs, use RAID 1. RAID 0 (disk striping without parity) offers good read/write performance, but any failed drive means that SQL Server can't continue operation on an affected database until the drive is replaced. RAID 1 (disk mirroring) creates duplicate copies of data on separate drives, but recovery from drive failure usually interrupts operations while you restore the failed drive from transaction logs or database backups. RAID 5 (disk striping with parity) offers good protection against single drive failure but has poor write performance. For best performance and fault tolerance, RAID 0 + 1 is recommended, which consists of disk mirroring and disk striping without parity.

  • UPS SQL Server is designed to maintain database integrity at all times and can recover information using transaction logs. This doesn't protect the server hardware, however, from sudden power loss or power spikes. Both of these can seriously damage hardware. To prevent this, get an uninterruptible power supply. A UPS system gives you time to shut down the system properly in the event of a power outage, and it's also important in maintaining database integrity when the server uses write-back caching controllers.

If you follow these hardware guidelines, you'll be well on your way to success with SQL Server 2000.

SQL Server 2000 and SQL Server Desktop

SQL Server 2000 is distributed in three main editions: Standard, Enterprise, and Developer. In all of these editions, you'll find a server installation and a desktop installation. The server installation includes the full version of SQL Server. The desktop installation includes a scaled-down version of SQL Server with some limitations.

The most widely deployed edition is the Standard Edition, which is designed for the average-sized organization. The Standard Edition

  • Runs on multiple versions of the Microsoft Windows operating system, including Windows NT 4.0 Server, Windows NT 4.0 Server Enterprise, and all Windows 2000 Server variants.

    Note: For all editions of SQL Server 2000 running on Microsoft Windows NT Server 4.0, Service Pack 5 (SP 5) or later must be installed. For additional requirements pertaining to the operation of SQL Server 2000 on Windows NT Server 4.0, refer to the SQL Server 2000 Books Online.

  • Supports an unlimited database size, up to 2 GB of RAM, four CPUs for symmetric multiprocessing, full-text search, and Microsoft SQL Server Analysis Services. Supports up to eight CPUs on Windows NT 4.0 Server Enterprise only.

While the Standard Edition is a strong database server solution, large organizations will want to consider the Enterprise Edition. The Enterprise Edition adds

  • Support for up to 64 GB of RAM and 32 CPUs on Windows 2000 Datacenter Server, 8 GB of RAM and 8 CPUs on Windows 2000 Advanced Server, which provides for exceptional performance and the ability to scale SQL Server to support large database installations.

    Note: Refer to SQL Server 2000 Books Online for maximum amount of physical RAM and number of CPUs supported on all versions of the Windows operating systems. Search for "Maximum Capacity Specifications."

  • Log shipping, which allows SQL Server to send transaction logs from one server to another. Use this feature to create a standby server.

  • Federated databases, which allows you to create distributed partition views by horizontally partitioning tables across multiple servers. Use this feature when you want a group of servers to work together to support a large Web site or enterprise data processing.

  • Failover clustering, which allows you to create four-node clusters on Windows 2000 Datacenter Server and two-node clusters on Windows 2000 Advanced Server. Use this feature to provide failover and failback support.

  • Indexed views, parallel DBCC, parallel CREATE INDEX, and enhanced read-ahead. Use these features to enhance the performance of SQL Server.

As you might expect, the SQL Server 2000 Enterprise Edition runs on Windows NT 4.0 Server Enterprise, Windows 2000 Advanced Server, and Windows 2000 DataCenter. The Developer Edition supports all the features of the Enterprise Edition but is licensed for development use only.

Other editions of SQL Server 2000 are available. These editions are the Windows CE Edition, the Personal Edition, and the Desktop Engine. The Windows CE Edition allows you to use SQL Server as the data store on Windows CE devices. The Personal Edition is the version you run when you want to work with the SQL Server desktop. The Desktop Engine is a version of the SQL Server 2000 database engine that can be distributed with third-party applications.

All editions of SQL Server 2000 automatically and dynamically configure user connections. This is different from previous versions, where specific limitations were placed on the number of simultaneous user connections. So you don't have to worry about managing user connections as much as you used to. Just keep in mind that as the number of user connections increases so does the amount of resource usage on the server. The server has to balance the workload among the many user connections, which can result in decreased throughput for user connections and the server as a whole.

Whether you use the Standard or Enterprise Edition, you have four options for working with SQL Server. You can

  • Work with the full server and tool installation Perform a complete installation of SQL Server, choosing the Server And Client Tools option. The installation can be performed locally or remotely.

  • Work with only the management or development tools , or both Start a normal installation but choose the Client Tools Only option on the Installation Definition page. This gives you the management tools and books online. Add development tools and code samples if you plan to do any development. Don't select the server components. (If you use per server licensing, this is the best option for management and development.)

  • Work with the SQL Server Desktop Install the SQL Server Desktop (Personal Edition) on any Windows workstation or server operating system, including Windows 95, Windows 98, and Windows 2000. For best performance, I recommend five or fewer connections. (If you use per seat licensing, this is the best option for management and development.)

  • Work with only the data access components and network libraries Start a normal installation but choose the Connectivity Only option on the Installation Definition page. This installs the Microsoft Data Access Components and network libraries a client needs to connect to SQL Server 2000.

The Personal Edition provides a full SQL Server database, but it has some limitations. Features not supported by the Personal Edition include

  • Parallel queries when multiple CPUs are available

  • Failover clusters and federated databases

  • Extended memory addressing

  • Log shipping and transactional replication

  • Read-ahead scans and indexed views

Additionally, the Personal Edition has limited support for transactional replication and Analysis Services. Personal Edition databases can only be subscribers for transactional replication. Personal Edition databases don't support user- defined OLAP (online analytical processing database) partitions, linked OLAP cubes, custom rollups, and other key analytical functions.

Note: As you can see, most of the differences between various editions of SQL Server are below the surface and don't affect the interface. Because of this, I refer to specific editions or differentiate between the server and the desktop installation only when necessary.

SQL Server and Windows 2000

When you install SQL Server on server operating systems, SQL Server makes several modifications to the environment. These modifications include new system services, additions to the taskbar, integrated authentication, new domain/workgroup accounts, and registry updates.

Services for SQL Server

When you install SQL Server on Windows NT or Windows 2000, several services are installed on the server. These services include

  • SQL ServerThe SQL Server service is the primary database service. For the default database instance this service is named MSSQLServer. When multiple instances of SQL Server are installed, you'll also see MSSQL$ instancename where instancename is the name of the SQL Server instance.

  • SQL Server Agent The SQL Server Agent service is used with scheduling. For the default database instance this service is named SQLServerAgent. When multiple instances of SQL Server are installed, you'll also see SQLAgent$instancename where instancename is the name of the SQL Server instance.

  • Distributed Transaction Coordinator The Distributed Transaction Coordinator service coordinates distributed transactions between two or more database servers.

  • Microsoft Search Used with full-text searches on databases. Only available when full-text search is installed as a custom component.

  • Active Directory Helper MSSQLServerADHelper adds and removes objects used to register SQL Server and Analysis Server instances. Also updates object permissions related to SQL Server service accounts.

Note: SQL Server Desktop doesn't use services or service accounts. You'll learn more about managing services and configuring service- related options in Chapter 3, "Managing the Enterprise."

Taskbar Extras for SQL Server

A taskbar shortcut is available for SQL Server. You can use this shortcut to manage services and display service status by completing the following steps:

  1. Double-click the shortcut to start the SQL Server Service Manager.

  2. Right-click the shortcut and then select the Current Service On \\COMPUTER-NAME option to configure which service's icon is displayed on the taskbar.

  3. Right-click the shortcut and then select Start, Stop, or Pause to control the active service.

  4. Right-click the shortcut and then select Options to configure options for SQL Server Service Manager.

  5. Right-click the shortcut and then select Exit to temporarily remove the SQL Server shortcut from the taskbar.

Note: To permanently remove the SQL Server shortcut from the taskbar, you must remove SQL Service Manager as a startup option. Right-click Start on the taskbar and then select Explore All Users. You'll see Windows Explorer with the Start Menu folder selected. Double-click Programs, select Startup, right-click Service Manager, and then select Delete. When prompted to confirm the action, click Yes.

Authentication Enhancements

SQL Server security is completely integrated with Windows domain security, allowing for authentication based on user and group memberships as well as standard SQL Server user accounts. These authentication techniques make it much easier to manage logons and security. You can

  • Combine Windows and SQL Server authentication so users in Windows domains can access the server using a single account and other users can be logged on using a SQL Server login ID. This is the default setting.

  • Use authentication based on Windows domain accounts only, so only users with a domain account can access the server.

Service Accounts

On Windows NT and Windows 2000, the MSSQLServer and SQLServerAgent services are configured to use Windows logon accounts. You can also configure additional SQL Server services to use Windows logon accounts. Two types of accounts are supported:

  • Local system accounts Provide administrative privileges to SQL Server on the local system but no privileges on the network. If the server requires resources on the local server only, use the local system account. Use local system accounts when you want to isolate SQL Server and restrict it from interacting with other servers. The Microsoft Search service must always use the local system account.

  • Domain accounts Set the service to use a standard domain account with privileges you configure. Use domain accounts when the server requires resources across the network, when you need to forward events to the application logs of other systems, and when you want to configure e-mail or pager notification.

Caution: With Windows accounts, make sure the SQL Server service has Administrator privileges on the local system. If you don't do this, the tasks you can perform with the server are restricted. You'll learn more about accounts and security in Chapter 5, "Microsoft SQL Server 2000 Security."

SQL Server and Active Directory

A directory is an important part of any computing environment. Businesses use directories to store information about objects on the network, such as users, contacts, and computers. You access information in a directory using a directory service. The directory service in Windows 2000 is called Active Directory. Active Directory service is used to store information in the directory and to make the information available to authorized users, services, and applications.

SQL Server 2000 includes enhancements for integrating with Active Directory service. Instances of SQL Server and their attributes are registered in Active Directory service during installation. Additional entries are created each time a database, server cube, or data mining model is created. These entries allow users and applications to search the directory for a particular instance of SQL Server and to search for characteristics of the server instance.

Custom applications can use Active Directory service to manage replication objects and to analyze directory data using Microsoft SQL Server 2000 Analysis Services. Active Directory service is also used with replication services to provide the ability to browse for and subscribe to publications. For example, you can search for SQL Server publications in Active Directory Users And Computers. Right-click the domain node, select Find and then in the Find Users, Contacts, And Groups dialog box select SQL Server Publications on the Find selection list.

Custom applications use the OLE DB Provider for Microsoft Directory Services to access information in Active Directory service. This OLE DB Provider exposes an LDAP and a SQL object interface. The Lightweight Directory Access Protocol (LDAP) interface is used to search Active Directory service using LDAP paths. The SQL interface is used to search Active Directory service using SQL syntax. Using the OLE DB Provider for Microsoft Directory Services, applications can also use Active Directory service as a data source for Data Transformation Services (DTS).

The key to using the OLE DB Provider for Microsoft Directory Services is to create a linked server using ADSDSOObject as the provider name and adsdatasource as the data source argument for the sp_addlinkedserver stored procedure. An example follows:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 
'ADSDSOObject', 'adsdatasource

Results from an Active Directory query are returned in a tabular format that can be queried using SQL Server distributed queries. Linked servers and the sp_addlinkedserver stored procedure are discussed in depth in Chapter 8.

SQL Server Administration Tools

SQL Server provides many administration tools. The tools you'll use the most are

  • Graphical administration tools The key tools for managing SQL Server. You can access them by selecting them individually. Select Start, choose Programs, and then use the Microsoft SQL Server menu.

  • Administration wizards and the Taskpad Tools designed to automate key administration tasks. You access them in SQL Server Enterprise Manager.

  • Command-line utilities Additional utilities designed to be run at the command line or accessed in scripts.

The following sections provide brief introductions to these administration tools. Additional details for key tools are provided throughout the book. Keep in mind that you may need administration access to SQL Server to use these utilities.

Graphical Administration Tools

SQL Server 2000 provides several types of tools for administration. The graphical administration tools are the ones you'll use the most. You can access these tools by selecting Start, choosing Programs, and then using the Microsoft SQL Server menu.

SQL Server 2000 is designed for local and remote management. You can use most of the tools to manage local resources as well as remote resources. For example, in SQL Server Enterprise Manager you can register a new server and then connect to it. Afterward, you can remotely manage the server and all its databases from your system. Table 1-1 provides a list of the key graphical administration tools and their uses.

Table 1-1 Quick Reference for Key SQL Server 2000 Administration Tools

Administration Tool


Client Network Utility

Configures the client network libraries and DB Library. Covered in Chapter 2.

ODBC Data Source Administrator

Allows you to view and configure ODBC (open database connectivity) data sources for all ODBC drivers on the workstation or server.

Performance Monitor

A customized version of the standard performance monitor, featuring special objects and counters for SQL Server. (Not available with Windows 95 or Windows 98 desktop installations.) See Chapter 10.

SQL Profiler

Allows you to analyze user activity and generate audit trails. Covered in Chapter 10.

SQL Query Analyzer

A visual utility for making queries and SQL scripts. Used anytime you want to execute SQL statements, check queries, or perform index analysis. See Chapter 4.

SQL Server Enterprise Manager

The main administration tool for SQL Server 2000. Manages SQL servers, databases, security, and more. Key aspects are discussed in Chapter 3.

SQL Server Network Utility

Configures server network libraries.

SQL Server Service Manager

Manages and configures SQL services. Easier to use than the Services utility in the Control Panel. See Chapter 3.

Administration Wizards and the Taskpad

Administration wizards are designed to make database administration easier. Wizards guide you through common database administration tasks using a series of dialog boxes, and they free you from having to remember what to enter when. SQL Server provides more than 20 wizards.

You start most Administration wizards by selecting Taskpad entries in SQL Server Enterprise Manager (see Figure 1-1). The Taskpad is a key feature in SQL Server 2000. You use the Taskpad to navigate through SQL Server options by clicking hypertext links. While some links access other Taskpad pages, others launch


Figure 1-1: SQL Server Enterprise Manager provides many wizards to make database administration easier.

wizards or display database information pages. Buttons on the toolbar allow you to move backward or forward as well as refresh the display if necessary.

The Taskpad is disabled by default in full installations and disabled by default in desktop installations. To enable or disable the Taskpad, complete the following steps:

  1. In SQL Server Enterprise Manager, select a database server entry. The database server entry is labeled <SERVERNAME (OS)> or <SERVERNAME\DBINSTANCE (OS), such as SQL1 (Windows NT/2000) or SQL1\Engineering1 (Windows NT/2000).

  2. Choose Taskpad from the View menu.

You'll learn more about SQL Server Enterprise Manager in Chapter 3. SQL Server also provides an additional wizard for migrating from previous versions. This wizard is called the SQL Server Upgrade Wizard. Use this wizard to migrate databases from a SQL Server 7.0 database and import the data into an existing SQL Server 2000 installation.

You run the SQL Server Upgrade Wizard by selecting Start, choosing Programs, and then choosing the SQL Server Upgrade Wizard option from the Microsoft SQL Server-Switch submenu. During the installation you'll need to stop and restart both database servers; consequently, you'll probably want to perform this operation after regular business hours.

Command-Line Tools

The graphical administration tools provide just about everything you need to work with SQL Server. Still, there are times when you may want to work from the command line, especially if you want to automate installation, administration, or maintenance with scripts. Key command-line tools you'll use include BCP.EXE, ISQL.EXE, OSQL.EXE, TEXTCOPY.EXE, ODBCPING.EXE, and REBUILDM.EXE. These command-line executables are usually stored in the \Mssql\Binn directory.


BCP is the bulk copy program. You can use BCP to import and export data or copy data between instances of SLQ Server 2000. BCP's major advantage is speed. It's much faster than standard database import/export procedures. Unfortunately, its command-line syntax makes it much harder to use.

The syntax for BCP is shown in Sample 1-1.

Sample 1-1. BCP Syntax

bcp {dbtable | query} {in | out | queryout | format} datafile
   [-m maxerrors] [-f formatfile] [-e errfile]
   [-F firstrow] [-L lastrow] [-b batchsize]
   [-n native type] [-c character type] [-w Unicode characters]
   [-N keep non-text native] [-V file format version] [-q quoted id]
   [-C code page specifier] [-t field terminator] [-r row terminator]
   [-i inputfile] [-o outfile] [-a packetsize]
   [-S server name\instance name] [-U username] [-P password]
   [-T trusted connection] [-v version] [-R regional enable]
   [-k keep null values] [-E keep identity values]
   [-h "load hints"]


ISQL is a SQL query tool that you can run from the command line. ISQL communicates with SQL Server through the DB Library and is used primarily because it runs within scripts. You'll find that ISQL has very little overhead, making it a good choice when system resources are a concern. Sample 1-2 shows the syntax for ISQL.

Sample 1-2. ISQL Syntax

isql [-U login id] [-P password]
   [-S server] [-H hostname] [-E trusted connection]
   [-d use database name] [-l login timeout] [-t query timeout]
   [-h headers] [-s colseparator] [-w columnwidth]
   [-a packetsize] [-e echo input] [-x max text size]
   [-L list servers] [-c cmdend]
   [-q "cmdline query"] [-Q "cmdline query" and exit]
   [-n remove numbering] [-m errorlevel]
   [-r msgs to stderr]
   [-i inputfile] [-o outputfile]
   [-p print statistics] [-b On error batch abort]
   [-O use Old ISQL behavior disables the following]
   <EOF> batch processing
   Auto console width scaling
   Wide messages
   default errorlevel is -1 vs 1
   [-? show syntax summary (this screen)]

Note: ISQL does not support connecting to named instances of SQL Server 2000. ISQL will always connect to the default instance of SQL Server.

When you start ISQL, you can issue Transact-SQL statements to run queries, execute stored procedures, and more. Because you're working at the command line, these commands aren't executed automatically and you need to use additional commands to tell ISQL when to execute statements, when to ignore statements, and so on. These additional statements must be entered on separate lines and are summarized in Table 1-2.

Table 1-2 Commands for Use with ISQL and OSQL




Executes all statements entered up to the previous GO or RESET


Clears statements you've entered so they aren't executed


Calls the editor



!! command

Executes the specified Windows NT shell command or script


Exits ISQL


Exits ISQL


Ends a query without exiting from ISQL


An alternative to ISQL is OSQL. You'll use OSQL to execute queries using ODBC connections instead of DB Library. The syntax for OSQL is shown in Sample 1-3. With OSQL, you can also use the commands listed in Table 1-2.

Sample 1-3. OSQL Syntax

osql [-U login id] [-P password]
   [-S server name\instance name] [-H hostname] [-E trusted connection]
   [-d use database name] [-l login timeout] [-t query timeout]
   [-h headers] [-s colseparator] [-w columnwidth]
   [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
   [-L list servers] [-c cmdend] [-D ODCB DSN name]
   [-q "cmdline query"] [-Q "cmdline query" and exit]
   [-n remove numbering] [-m errorlevel]
   [-r msgs to stderr] [-V severitylevel]
   [-i inputfile] [-o outputfile]
   [-p print statistics] [-b On error batch abort]
   [-u outputfile stored in Unicode format]
   [-R ODBC use client settings when converting currency, date, and time] [-O use Old ISQL behavior disables the following]
   <EOF> batch processing
   Auto console width scaling
   Wide messages
   default errorlevel is -1 vs 1
   [-? show syntax summary]


Textcopy is a command-line utility for importing and exporting text or images files with SQL Server. When you import with Textcopy, data from a specified file is copied into SQL Server, replacing an existing text or image value. When you export with Textcopy, a text or image value is copied from SQL Server into a specified file. The syntax and switches for Textcopy are shown in Sample 1-4 and Table 1-3, respectively.

Sample 1-4. Textcopy Syntax

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
   [/D [database]] [/T table] [/C column] [/W "where clause"]
   [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

Table 1-3 Textcopy Switches




Displays syntax and usage help.

/C column

Specifies the table column.

/D database

Sets the database to use. If the database isn't specified, the default database for the user login is used.

/F file

Sets the input/output file name.

/I Input

Specifies that you're copying into SQL Server from a file.

/K chunksize

Sets the size of the data transfer buffer in bytes. While the minimum value is 1024 bytes, the default value is 4096 bytes.

/O Output

Specifies that you're copying from SQL Server to a file.

/P password

Sets the login password. If password isn't set, NULL is used.

/S sqlserver

Designates the SQL Server to connect to. If sqlserver isn't specified, the local SQL Server is used.

/T table

Specifies the database table.

/U login

Sets the login to connect with. If the login isn't set, Textcopy attempts to use a trusted connection.

/W "where clause"

Used to set the necessary where clause that must specify a single row in the designated table.


Displays debug information while running.


If you need to verify an ODBC connection between a client and server, you can use Odbcping. You can use Odbcping to perform two types of tests. You can test the ability of a client to connect directly to a server, and you can test the integrity of an ODBC data source.

Odbcping is a command-line utility with the following syntax:

odbcping [ {-Sserver_name [\instance_name] | -Ddata_source]
[-Ulogin_id] [-Ppassword]

Note: SQL Server 2000 doesn't install the Odbcping utility during setup. It's located in the \x86\Binn directory on the SQL Server 2000 compact disc.


Rebuildm is a utility for rebuilding the master database. Rebuilding the master database removes all database objects and data. Because of this, you'll need to re-create database objects and reload data by restoring backups of system and user databases, or use sp_attach_db to reattach the data and log files if recent copies are available. You need to rebuild the master database only in these situations:

  • To repair a corrupted master database

  • To change the default collation settings for a SQL Server instance

Note: Unlike SQL Server 7, SQL Server 2000 allows you to specify collations at several levels. The default collation is the setting for all system databases, and you can't change this without rebuilding the master database. You can set a different collation when you create databases, tables, parameters, and literal strings.

To start the Rebuild Master utility, type rebuildm on the command line. Then follow the prompts.

Click to order

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
© 2014 Microsoft. All rights reserved.