Legacy and VSS Restore of corrupted master SQL Server system database

Answer

Legacy Restore:

a. Rebuild System Databases

1. Insert the SQL Server 2012 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\110\Setup Bootstrap\Release.

2. From a command prompt window, enter the following command. The command prompt must be run as Administrator.

D:\>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<MyDomain\Administrator> /SAPWD=<mysapass>

In this case the default instance name is being used, which is MSSQLSERVER

3. After the rebuild, the command prompt should be displayed again.
Check the output from the rebuild of the database to check it was successful:
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt
For example the following output shows a successful result:

Overall summary:
Final result: Passed
Exit code (Decimal): 0

4. Login to SQL SERVER Management Studio and check for a healthy state of the system databases. All user databases are unattached;

Attach each database using a GUI or sql script. For example the sql script for Archive_stream db would be:

USE [master]
GO
CREATE DATABASE [Archive_stream] ON
( FILENAME = N'C:\data\archdat1.mdf' ),
( FILENAME = N'C:\data\archlog1.ldf' )
FOR ATTACH
GO

b. Start the SQL Server in single-user mode.

The CLI command is:
net start MSSQLSERVER /m.

After this step, the availability group role(resource) will automatically go from an online to failed status. If this does not occur, manually take it offline.

c. Run CLI restore master command

For example:
tdpsqlc Restore master FULL /BackupMethod=Legacy /BackupDestination=TSM /SqlServer=<>
Check for a successful result.

d. Restart the database engine to restore the SQL Server to normal multi user mode.

After this step the availability group role(resource) should automatically go from failed to online status.


VSS Restore:

a. Create <newmaster> new db located in default location. Ensure that the SQL Server is online.

b. Restore the master database to a new database in Microsoft Management Console (MMC), or at the command line.

Enter the tdpsqlc command with the /recovery=no option.

For example:

C:\Program Files\Tivoli\TSM\TDPSql>tdpsqlc Restore master FULL /OBJect=20150616150228 /BackupMethod=VSS /BackupDestination=Local /RECOVERY=NO /Into=newmaster

c. After the restore operation is complete, verify that all data files are restored successfully.

d. Stop the SQL Server instance, and rename all data files of the master database.
The Availability group resource should change from online status to failed.

e. Copy(rename) all data files from the new master restored database to the location of the master database. Verify that all data files are copied.

f. Start the SQL Server instance and verify that the master database is restored successfully.
The Availability group resource will go from the failed status to online.