SQL Server recovery procedures for a catastrophic failure – (Part-2)

Author: Basit A. Farooq

To rebuild master follow the instructions below:

Execute Setup.exe as follows:

start /wait setup.exe /qn INSTANCENAME=instance_name

REINSTALL = SQL_Engine REBUILDDATABASE=1

SAPWD=strongpassword

Setup recreates the resource database and the master database. Install any necessary service packs.

Attach any user databases or restore databases from backup. If the user databases aren’t damaged, you can attach them. If a database’s files or transaction log are damaged or missing, you need to restore from backup. Also, recreate server objects such as linked servers, end point etc.

When SQL Server rebuilds the master, it also rebuilds model and msdb. If you’ve made changes to model, you will need to restore it from backup. If you’ve made changes to msdb or if you use backup history, you will need to restore msdb.

Moving master

You might need to move the master database and the resource database to a new location to recover from a hard disk or disk controller failure. To do so:

• Launch SQL Server Configuration Manager.
• Display Properties for the SQL Server service.
• Activate Advanced.
• Modify the datafile (-d) and log file (-l) startup parameters to reference the new location.
• Click Apply.
• Stop the SQL Server service.
• Move the files to the new location.
• Start in master-only recovery mode by running:

NET START MSSQLSERVER /f /T3608

• Launch a command prompt and run sqlcmd.
• Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the database file.
• Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the log file.
• Use the ALTER DATABASE statement to make the Resource database read-only.
• Stop the SQL Server service.
• Move the database file for the Resource database.
• Start the SQL Server service.

The procedures for moving msdb and model are similar, except you don’t need to change the startup parameters or move the resource database. After running ALTER DATABASE, you can start the SQL Server service normally.

Recovering from out-of-disk space conditions for tempdb

Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

• Tracking versions for row-versioning concurrency.
• Performing bulk load operations on tables with triggers enabled.
• Running DBCC CHECKDB.
• Rebuilding an index with SORT_IN_TEMPDB option.
• Variables of LOB data types.
• Storing intermediate query results, for example, during joins, aggregates, or sorts.
• Service broker dialog information.
• Caching temporary objects and tables.
• Storing inserted and deleted tables in triggers.
• Running sp_xml_preparedocument.

Viewing tempdb space usage

The following dynamic management views (DMVs) can be used to report information about tempdb space:

• sys.dm_db_session_space_usage – Retrieves the number of pages allocated and deallocated by each session for the database.
• sys.dm_db_task_space_usage – Retrieves the page allocation and deallocation activity by task for the database.
• sys.dm_db_file_space_usage – Retrieves information about disk space used by any database.

How to move tempdb?

SQL Server creates the tempdb database every time it starts up. Therefore, you only need to change the pointer to move the tempdb database and log files to move it to a new location. To do this:

• Retrieve the logical name of the database and log files from the sys.master_files catalog.
• Run ALTER DATABASE with the MODIFY FILE option to specify the new locations for the tempdbdatabase data and log files (see below):

USE [master]

GO

ALTER DATABASE tempdb MODIFY FILE (

NAME = tempdev

,filename = N’tempdb.mdf’

)

GO

ALTER DATABASE tempdb MODIFY FILE (

NAME = templog

,filename = N’templog.ldf’

)

GO

Once complete, an informative message will appear stating that tempdb will be moved the next time SQL Server is started (see below):

The file “tempdev” has been modified in the system catalog.

The new path will be used the next time the database is started.

The file “templog” has been modified in the system catalog.

The new path will be used the next time the database is started.

Restart the SQL Server service. Don’t forget to delete the files from old location.

Dedicated Administrator Connection

The Dedicated Administrator Connection (DAC) is a special connection administrators can use to connect to an instance of SQL Server that is otherwise inaccessible. You can open the DAC:

• When using sqlcmd
• To open a query editor window in SQL Server Management Studio

Only members of the sysadmin role can open a DAC. The SQL Server Browser service must be enabled.

To open a DAC connection using sqlcmd, open a command prompt and then execute the following:

sqlcmd –A

SQL Server establishes a connection to the user’s default database. If you want to connect directly to master, you execute:

sqlcmd –A –d master

To open a DAC on Query Editor:

• Open SQL Server Management Studio.
• Click Cancel when prompted to connect.
• Click New Query.
• In the Server name box, type ADMIN:server_name
• Provide the credentials for a member of sysadmin.
• Click Connect.

DAC tasks

You can run most Transact-SQL statements inside a DAC. However, you can’t perform parallel operations. This means you can’t run BACKUP or RESTORE inside a DAC. You shouldn’t run processor-intensive queries or queries that might cause blocks. You should avoid processor-intensive DBCC commands, such as DBCC CHECKDB and dynamic management views that perform multi-table joins. You can execute:

• Catalog views
• Dynamic management views
• DBCC commands
• KILL

Consult SQL Server Books Online for a specific command before you run it under a DAC.

Connecting to a remote server using DAC

SQL Server supports DAC for only local connections by default. You can enable SQL Server to listen for remote DAC connections by enabling the remote admin connections option through sp_configure or Surface Area Configuration for Features. To do so:

• Launch Surface Area Configuration.
• Click Surface Area Configuration for Features.
• Select DAC.
• Check Enable remote DAC.
• Click OK.

The DBCC CHECKDB command

You can use the Database Consistency Checker (DBCC) CHECKDB command to validate the storage of database data on the hard disk. The DBCC command performs the following checks:

• Executes DBCC CHECKALLOC to check the disk allocation structures for the database.
• Executes DBCC CHECKTABLE to check the structures and pages that store tables and indexed views.
• Checks Service Broker objects.
• Executes DBCC CHECKCATALOG to verify consistency of system metadata.
• Checks the contents of each indexed view.

You can also run the CHECKALLOC, CHECKTABLE, and CHECKCATALOG commands separately. DBCC CHECKDB has the following syntax:

DBCC CHECKDB

[

[ ( ‘database_name’ | database_id | 0

[ , NOINDEX

| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST |

REPAIR_REBUILD } ]

) ]

[ WITH

[ ALL_ERRORMSGS ]

[ , NO_INFOMSGS ]

[ , TABLOCK ]

[ , ESTIMATEONLY ]

[ , { PHYSICAL_ONLY | DATA_PURITY } ]

]

]

You can specify NOINDEX to cause DBCC CHECKDB to ignore nonclustered indexes. Doing so will cause the check to run faster. Another option for running CHECKDB faster is to specify PHYSICAL_ONLY. When you use PHYSICAL_ONLY, SQL Server checks only the physical structure.

You can have CHECKDB repair the database by using either the REPAIR_ALLOW_DATA_LOSS or the REPAIR_REBUILD (only repairs that do not cause data loss are performed) option. SQL Server 2005 supports the REPAIR_FAST option is for backwards compatibility, but doesn’t repair errors. In most cases, it’s better to restore from backup than to use CHECKDB to repair a database.

Normally, CHECKDB creates a database snapshot and performs the operation on it. You use the TABLOCK option to cause it to take a table lock instead. Using TABLOCK can cause the check to run more quickly, but might cause blocking issues.You use the ESTIMATEONLY option to determine the amount of disk space tempdb requires to run an actual check. ESTIMATEONLY doesn’t run the check.

You can control how much information CHECKDB returns by specifying ALL_ERRORMSGS or NO_INFOMSGS.

When checking databases that have been upgraded from an earlier version of SQL Server, you use the DATA_PURITY option to check the column values to verify the data is within the limits of the data type. Column values for databases created in SQL Server are always checked unless you specify PHYSICAL_ONLY.

The DBCC CHECKFILEGROUP command

The CHECKFILEGROUP command is similar to CHECKDB, except it only checks the integrity of a single filegroup.

Conclusion

In this second of the two part article series, you learned how to move and rebuild the master database. You also learned how to troubleshoot and resolve disk space issues with tempdb. You learned how to check the database consistency using DBCC CHECKDB and DBCC CHECKFILEGROUP. You also learned how to establish a Dedicated Administrator Connection (DAC).