We’ve run across an issue in Windows 2008 when formatting drives. Our procedure to spin up new SQL VMs is to format the drives and then join the domain. So this means the local administrator account formats the drives. When this is done, domain admins do not have access to the drives when logged into the server locally. They just appear as NTFS drives and you can’t even see how much free space they have without using WMI, perfmon or logging in as local administrator. SQL Server ran fine so this is more of a security nuisance.
You can get access from another machine using the admin shares e$, f$ etc. We realized this was easiest to resolve by moving the files off, formatting the drives (64k of course) with a domain admin account and moving the files back onto the drives. Now when you have to remote in, you can actually see free space and browse to see what is on the drives. Rather helpful when troubleshooting.
I came across another server with this issue but knew there wasn’t anything on the drives except TempDB. I know tempdb gets cleared out every time the instance starts so I chose the lazy method and just formatted the drives instead of wasting time moving tempdb around. When I tried to start the instance it failed. I checked the event log first and found:
Unable to open the physical file “G:tempdb.mdf “. Operating system error 3: “3(The system cannot find the path specified.)”.
Interesting I say, I thought SQL Server creates this file every startup but maybe it just clears it out. I’ll have to do some research to figure that out later.
I know that restoring master can be a bit tricky and that you have to use trace flags to start up the instance in single user mode. I thought this might be the answer to fix tempdb as well. This KB proved helpful http://support.microsoft.com/kb/288809
To start SQL Server in single user mode, start an administrator command prompt and run this:
%installdir%sqlservr.exe -c -f -T3608 -T4022
Now you can open SSMS, but another speed bump will stop you from doing much. When you connect to the instance as normal the object explorer on the left uses up your only available connection. If you try to right click on the server and choose new query you will get a connection failed message. Disconnect the object explorer and connect the new query window. The KB will reset a bad tempdb but what I tried first was “USE TEMPDB” which completed successfully. I browsed to the directory and saw everything was in order, or so I thought.
When I tried to restart the instance without the start up flags I hit another speed bump. The event log this time said access denied. We’ve made long strides in SQL Server security and now have a domain user account with minimal privileges running SQL Server. Turns out it did not have access to this newly created tempdb file.
This unplanned DR practice was good for my experience. To sum things up… shortcuts are good, just don’t be lazy.