There are two specific techniques that you can use to resolve SQL Server configuration problems. In this section, you will learn how to recover from a bad configuration and how to rebuild the master database.
Although SQL Server 2005 has many safeguards that help you avoid configuration settings that keep SQL Server from starting, you may occasionally find that a configuration change prevents SQL Server from starting. If you encounter this situation, you can recover the server instance by completing the following steps:
Log on to the affected server locally, or log on remotely through Telnet or Terminal Server. You must log on using a local administrator account or the account used by the database server instance.
Make sure that the MSSQLServer or MSSQL$instancename service is stopped. If it is not, stop the service using one of the following methods:
SQL Server Configuration Manager
Computer Management
Services
If the instance of SQL Server was installed as a default installation, you can stop the service by using the following command:
net stop MSSQLSERVER
From the command prompt, switch to the directory of the associated SQL Server instance (either MSSQL.1mssqlinn or MSSQL.1mssql$instancenameBinn). You must be in this directory to use the sqlservr utility.
Start SQL Server from the command line with the following option:
sqlservr –s(instancename) –f
You must use the –s option to specify the instance of SQL Server if multiple instances of SQL Server are installed. The –f option starts SQL Server in single-user mode with a minimum configuration. This ensures that the bad configuration is not loaded.
Wait for the server to start up. SQL Server should write a few pages of output to the screen. Leave the server running.
In another command prompt window or Telnet session, start SQLCMD with the user name of a SQL account with administrator privileges and password:
sqlcmd –U username –P password
If you have accessed SQLCMD properly, you should see the prompt change to >.
Reverse the changes made to the configuration by entering commands as you would in SQL Server Management Studio. The main difference is that you follow the commands with GO, as shown in the following example:
exec sp_configure "max server memory", 128 go reconfigure go
When you are finished, exit SQLCMD by typing exit.
From the command line in the window running SQL Server, press Ctrl+C.
When prompted, type Y for Yes. This stops SQL Server.
Restart SQL Server as you normally would. If you have made the appropriate changes, the server should start normally. Otherwise, repeat this procedure.
Rebuilding the master database restores all system databases to their original contents and attributes. The main reasons for rebuilding the master database are as follows:
To set a new default collation for a database server instance
To repair a corrupted master database when no backup of the master is available
To repair a corrupted master database when the SQL Server instance cannot be started
The Rebuildm utility is no longer used for rebuilding the master database. Instead, run the SQL Server 2005 Setup program again to rebuild the master database. If you choose to rebuild the master database, keep the following guidelines in mind:
After you rebuild the master database, you should restore the most recent master, model, and msdb databases. If the server was configured for replication, you must restore the most recent distribution database. Any data that cannot be restored must be manually created.
After you rebuild the master database, all user databases are detached and unreadable. To recover them, you must re-create all your user databases. You cannot restore the user databases from backup—the restore maintains the information that was set when you created the backup, and you may instead want to move the databases to another server by means of import and export, covered in Chapter 10.
You must reapply any SQL Server updates to bring the Resource database up to date. The Resource database is updated whenever patches, hot fixes, or service packs are applied to SQL Server.
To rebuild the master database, follow these steps:
Log on to the server using an account with administrator privileges. In Control Panel, double-click Add Or Remove Programs.
Select Microsoft SQL Server 2005 in Add or Remove Programs, and then click Change. When the SQL Server 2005 Maintenance Wizard starts, select the SQL Server instance to maintain and then click Next
On the Feature Maintenance page, select the component you want to work with, such as Analysis Services or Database Engine and then click Next.
The SQL Server Installation wizard is started. Click Next to allow setup to perform a system configuration check. When the system configuration check is completed, note any issues and correct problems as necessary. Click Next.
Setup will then review the installed components. On the Change Or Remove Instance page, click Change.
On the Feature Selection page, double-click the entry for the component. This will expand the component details so you can see subcomponents. Click the icon for the subcomponent to specify its availability.
Click Next and then click Install. SQL Server will then verify the installation and rebuild a damaged installation as necessary. When this process completes, click Next and then click Finish.