During startup of warm standby database, its standby file ('.tuf') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup. (Microsoft SQL Server, Error: 3441)

I tried to simulate this on my test machine which had log shipping configured. Below are the steps which I followed -
1. Deleted the TUF file which was available in the secondary server.
2. The delete operation was successful.
3. Checked log shipping status and found that the health is ‘Good’
4. Both primary and secondary databases are synced and both have got same set of data. Row by row,Col by Col.

Note - Ideally deleting the TUF file should also cause issues to log shipping secondary restores, however my simulation did not faced that behavior.
All looks good, and you might be wondering that deleting a TUF file is easy and it’s not going to hurt me much!!!
Now, let’s assume that we lost our primary database server due to Memory burn(Short circuit) and we are in need of the Secondary database.
The RTO and RPO matrix is quite okay and we are allowed to bring the secondary database up within 30 minutes. Walk in the park right? We just have to bring the database up, the users/jobs/other objects are already taken care and just the database needs to be up.
Let’s write this simple 6 word TSQL to bring our database up.

RESTORE DATABASE [Test] WITH RECOVERY

Test is my test database which is available in the secondary server and its primary copy was the one which was residing on the server which just went for a trip(Memory burn!)
As soon as we execute this command with a big smile assuming that the database will be up, we will get this message -
Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Msg 3441, Level 17, State 1, Line 1 During startup of warm standby database ‘Test’ (database ID 6), its standby file (‘C:\Program Files\Microsoft SQL Server\MSSQL11.SERVER2012B\MSSQL\DATA\Test_20120112191506.tuf’) was inaccessible to the RESTORE statement. The operating system error was ’2(The system cannot find the file specified.)’. Diagnose the operating system error, correct the problem, and retry startup.

What does it mean – It simply means that you have done a good job by deleting the TUF file and now please bring it back.
TUF file is required for the Stand by database to recover and we will not be able to bring the database up without the same.
As the simulation was in a very controlled environment, I brought back the TUF file and ran the restore command once again.

RESTORE DATABASE [Test] WITH RECOVERY

RESTORE DATABASE successfully processed 0 pages in 0.908 seconds (0.000 MB/sec).

The database was recovered and was accepting new connections.
Conclusion – TUF file is a very important part of recovery of a stand by database and we have to educate server ops team or anyone who is responsible for cleaning up files and make sure that this is un-touched.
Do you have any ways to recover a stand by database in log shipping secondary without TUF file.If Yes,then please share your experience in the comments section of this post.
Thanks for reading.

Permissions of Fixed Database Roles

Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server 2005. The following table describes the mapping of the fixed database roles to permissions.

Fixed database role	Database-level permission	Server-level permission
db_accessadmin	Granted: ALTER ANY USER, CREATE SCHEMA	Granted: VIEW ANY DATABASE
db_accessadmin	Granted with GRANT option: CONNECT
db_backupoperator	Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT	Granted: VIEW ANY DATABASE
db_datareader	Granted: SELECT	Granted: VIEW ANY DATABASE
db_datawriter	Granted: DELETE, INSERT, UPDATE	Granted: VIEW ANY DATABASE
db_ddladmin	Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES	Granted: VIEW ANY DATABASE
db_denydatareader	Denied: SELECT	Granted: VIEW ANY DATABASE
db_denydatawriter	Denied: DELETE, INSERT, UPDATE
db_owner	Granted with GRANT option: CONTROL	Granted: VIEW ANY DATABASE
db_securityadmin	Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION	Granted: VIEW ANY DATABASE
dbm_monitor	Granted: VIEW most recent status in Database Mirroring Monitor The dbm_monitor fixed database role is created in the msdb database when the first database is registered in Database Mirroring Monitor. The new dbm_monitor role has no members until a system administrator assigns users to the role. Important:	Granted: VIEW ANY DATABASE

Event ID: 7000 The SQL Server...service failed to start due to the following error: Access is denied.

Event ID: 7000 The SQL Server...service failed to start due to the following error: Access is denied.

Two months back, I worked through a new install of SQLServer 2012 with SP1 on Microsoft Windows Server 2012 Standard on a VMware virtual machine.  During the install everything went smoothly, however once complete I noticed the services were stopped and there were errors in the OS system log:

Log Name:      System
Source:        Service Control Manager
Date:          4/11/2013 12:50:41 PM
Event ID:      7000
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      ...
Description:
The SQL Server (MSSQLSERVER) service failed to start due to the following error: 
Access is denied.

I turned to my best friend, in situations like this: www.google.com.  Unfortunately this didn't get me very far; it took a bit more digging.  I gathered more errors from the Application log, the System log, and the bootstrap summary (%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt): 

• Attempted to perform an unauthorized operation
• error: 40 - Could not open a connection to SQL Server

I also notice that if I attempted to change the "log on as" user in SQL server Configuration Manager again I received an "access is denied" error.  This occurred even if I attempted to use a system user.

It was googling more detailed error messages from the bootstrap summary log that lead me to the solution.  

Apparently, when you build Windows Server 2012 on VMware we need to disable the HotAdd/HotPlug capability.  Who knew?

This was a difficult one to track down, but thanks to SQL Server Central forums, MSDN forums and the community I now have a new item on my build checklist.

One of the things that really helped quickly get through multiple install attempts was using an install config file.  This allowed me to run through the install over and over without having to click through the install each time.  There's a really good article on how to setup an unattended install here: SQL Server 2008 R2 – Unattended Silent Install (Works the same in 2012).

Happy Installs!