October 28, 2014 at 8:16 am
Hello all,
I have a situation where the drive where my tempdb and templog files used to be housed no longer exist. (not an option at this time to get it back either...)
So this is what I have tried and can do: run cmd prompt as admin.
1st because I can't get the sql server up and running normally I ran the following command: NET START MSSQLSERVER /f /T3608
then I tried: sqlcmd ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='mypathto\tempdb.mdf');
I tried numerous versions of the above and ened up getting output like:
sqlcmd: 'alter' : unknown option. enter '-?' for help
So tried another version:
SQLCMD -E -S ".\myinstance" -Q "ALTER DATABASE [tempdb] MODIFY FILE ( NAME =N'tempdev', FILENAME='mypathto\tempdb.mdf')"
And recieved a new message:
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
So my question is, what am I missing? Alter is wrong, login times out... All examples I have looked at do not indicate I need a user id & pw. When I have tried an ID & PW, it gives me an error only 1 admin allowed at a time.
Thanks for any thoughts.
October 28, 2014 at 8:25 am
BC-36233 (10/28/2014)
NET START MSSQLSERVER /f /T3608
You can't start the service like this. Just grab the service target (the path to the executable sqlservr.exe) and run it in a cmd window opened impersonating the service account. Add the parameters there, not in the NET START command.
then I tried: sqlcmd ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='mypathto\tempdb.mdf');
I tried numerous versions of the above and ened up getting output like:
sqlcmd: 'alter' : unknown option. enter '-?' for help
sqlcmd has to be opened and connected to the target instance before you can issue queries and commands. You can specify a query as a command line parameter, but not the way you did here.
Open sqlcmd passing at least the -S parameter (the name of the server) and the -E parameter (for trusted connection, otherwise use -U and -P)
So tried another version:
SQLCMD -E -S ".\myinstance" -Q "ALTER DATABASE [tempdb] MODIFY FILE ( NAME =N'tempdev', FILENAME='mypathto\tempdb.mdf')"
And recieved a new message:
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
No spaces between -S and the server name:
SQLCMD -E -S.\myinstance -Q"ALTER DATABASE [tempdb] MODIFY FILE ( NAME =N'tempdev', FILENAME='mypathto\tempdb.mdf')"
-- Gianluca Sartori
October 28, 2014 at 8:58 am
Thanks for the reply.
I'm using : NET START MSSQLSERVER /f /T3608
because other wise I can't get anything started.
When I tried: running sqlservr.exe from a cmd window, I get errors I can't create the tempdb.mdf and it's log file. (Unless I am missing something here)
Which is where I am when trying to just start normally.
Any other thoughts?
October 28, 2014 at 9:35 am
You are getting the errors because the tempdb files cannot be created.
1. Open cmd impersonating the service user. Use runas for this.
2. Run services.msc to open the services applet. Find the SQL Server service and open the properties window. Under the "general" tab there's a label named "path to executable". Copy that value, parameters included, and paste it to the cmd window
3. Open SQL Server Configuration manager and open the properties window for the SQL Server service. Copy any additional command line parameters from here and paste them to the cmd window. Remember that you will have to replace the semicolons between parameters with a space.
4. Add the startup parameters you need: -f -m
5. Run the command
6. Open another cmd window and run sqlcmd from there.
-- Gianluca Sartori
October 28, 2014 at 10:19 am
Ok, this might be my problem:
1. Open cmd impersonating the service user. Use runas for this.
I'm using run as admin, however when I try to start the exe, it says:
Error: 18461, Severity: 14, State: 1.
Login failed for user 'network\user'.
Reason: Server is in single user mode. Only one administrator can connect at th
is time. [CLIENT: <local machine>]
so is there some way to start as the service user I am missing?
October 28, 2014 at 10:44 am
You should get that message when you're trying to connect with sqlcmd, not when you're starting sqlserver from the command line.
-- Gianluca Sartori
October 28, 2014 at 11:24 am
Exactly.
So I must not be starting sql correctly.
What should that command look like?
Thanks
October 28, 2014 at 2:11 pm
Got it.
Thanks
October 28, 2014 at 3:08 pm
Glad I could help
-- Gianluca Sartori
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply