How to recover MS SQL 2008 database

Jephe Wu - http://linuxtechres.blogspot.com


Objective: understanding the recovery cases in MS SQL 2008
Environment: Windows 7 Professional SP1 64bit with 8G RAM, MS SQL server 2008 standard edition.

Prerequisites (refer to http://msdn.microsoft.com/en-us/library/dd207003.aspx for sql server 2012)

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.

Case 1:  Master database failure, but we can still startup with single user mode with master database.

Go to 'sql server configuration manager' - 'sql server service' - SQL server(MSSQLSERVER)- advanced tab, to append startup parameter with -m, so it becomes:

-m;-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

stop all other services first, then start SQL server(MSSQLSERVER) service.

After starting up single user mode, you can use 'sqlcmd' in command prompt or SSMS to connect to it, when you use SSMS, remember not to login with database engine, cancel it, use 'new query' to get connected. Otherwise you might encounter error message "Only one administrator can connect at this time."

Now you can restore master database from your backup with the following command:


C:\Users\Ling Chen>sqlcmd
1> restore database master from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\master.bak'
2> go


Processed 376 pages for database 'master', file 'master' on file 1.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.


or just run:
restore database master from disk = 'C:\Program Files\Microsoft SQLServer\MSSQL10.MSSQLSERVER\MSSQL\Backup\master.bak'  in SSMS

FAQ:
a. C:\Users\Ling Chen>sqlcmd
Msg 18461, Level 14, State 1, Server FEIT3263, Line 1
Login failed for user 'FEIT3263\Ling Chen'. Reason: Server is in single user mod
e. Only one administrator can connect at this time.
Solution 1: change sql server to start it as your current Windows login user and password instead of system account.
Solution 2: logout current user although it belongs to administrator group, use 'Administrator' to login, then run 'sqlcmd'

Case 2: Master database corrupted,  we cannot even startup in single user mode

http://msdn.microsoft.com/en-us/library/dd207003(v=sql.100).aspx for how to rebuild db

In this case, we need to rebuild master database as follows:


Assuming MS SQL server 2008 standard edition installation disk is at H drive (Magic ISO to mount ISO file at H drive), the instance name is default MSSQLSERVER

H:\>setup /quiet /action=rebuilddatabase /instancename=MSSQLSERVER
Microsoft (R) SQL Server 2008 Setup 10.00.1600.22
Copyright (c) Microsoft Corporation.  All rights reserved.

The following error occurred:
Missing system administrator account. To continue, provide at least one Windows
account to provision as a SQL Server system administrator.

Error result: -2068578304
Result facility code: 1204
Result error code: 0

Please review the summary.txt log for further details


H:\>setup /quiet /action=rebuilddatabase /instancename=MSSQLSERVER /sqlsysadminaccounts=Administrator
Microsoft (R) SQL Server 2008 Setup 10.00.1600.22
Copyright (c) Microsoft Corporation.  All rights reserved.



try to restore master db from SSMS (in single user mode) after rebuild systemdb:
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'master' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

-----------
Updated on 26/01/2013:
For SQL server 2012 rebuild system database howto, please refer to http://msdn.microsoft.com/en-us/library/dd207003.aspx

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

-----------------

restore database master from disk = 'C:\Program Files\Microsoft SQLServer\MSSQL10.MSSQLSERVER\MSSQL\Backup\master.bak' with replace

After rebuilding master/system db, you can startup in single user mode, follow case 1.