Problem:
After I moved my RAID set from 1 server to another, the SQL Server service refused to start on the old machine, even though the data files are still intact on the system.
The SQL Server error logs resided on the RAID container I moved; I wasn't aware of this.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17058
User: N/A
Description:
initerrlog: Could not open error log file '<full file path and filename>'. Operating system error = 3(The system cannot find the path specified.).
Great. I search online, and I get stuff about MOVING the data files at http://support.microsoft.com/default.aspx?scid=kb;en-us;224071. But what if I had already pulled the RAID container? My data is safe... I just want to move the error log file.
Solution:
Edit the startup properties of the service to point the Error Log to a new location. Make sure you don't just specify a target directory, but also the target log file name. Here's what I did.
I went into the SQL server directory ("C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn" for me) and tried to start SQL Server manually. (If you go into Admin Tools\Services, right click on the SQL Service, Pproperties, check out the "Path to executable". Note the -sMSSQLSERVER or any other start up parameters that specify the instance you're having trouble with.)
I did a sqlservr /? in a cmd window:
usage: sqlservr
[-c] (not as a service)
[-d file] (alternative master data file)
[-l file] (alternative master log file)
[-e file] (alternate errorlog file)
[-f] (minimal configuration mode)
[-m] (single user admin mode)
[-g number] (stack MB to reserve)
[-k <decimal number>] (checkpoint speed in MB/sec)
[-n] (do not use event logging)
[-s name] (alternate registry key name)
[-T <number>] (trace flag turned on at startup)
[-x] (no statistics tracking)
[-y number] (stack dump on this error)
[-B] (breakpoint on error (used with -y))
[-K] (force regeneration of service master key (if exists))
Next, I did "sqlservr.exe -eH:\SQL -sMSSQLSERVER" at the command prompt. (Hit CTRL+C to safely terminate the instance if necessary.) This generated this error in my event log.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17058
User: N/A
Description:
initerrlog: Could not open error log file 'H:\SQL'. Operating system error = 5(Access is denied.).
Oops. I checked the ACLs; they looked fine. I had forgotten to specify a full path/filename for the log.
Tried "sqlservr.exe -eH:\SQL\err.log -sMSSQLSERVER" Bingo. Service starts. (Hit CTRL+C to safely terminate the instance if necessary.)
Good. Now to update the service so that it behaves. Went into the "SQL Server Configuration Manager" - NOT the Management Studio. Right click on the service, Properties. Advanced tab, change the Startup Parameters.

Edit the text in the red location in the image above, so that it contains the updated error log path we had tested in the console.