Home Forums SQL Server 2008 SQL Server 2008 - General How to rectify the mistake of specifying wrong path in Alter database <db name> modify file... command RE: How to rectify the mistake of specifying wrong path in Alter database <db name> modify file... command

  • Don't use NET Start. Stop the service, then start SQL as an application. From the command line (in the appropriate directory)

    SQLServr.exe -m -f -T3608

    Make sure that SQL agent is stopped/ Make sure there is noting else that could try to connect to SQL Server.

    What you should see in the command window is the error log output, and it should contain something like this:

    2012-07-07 21:12:04.83 Server Registry startup parameters: -d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\master.mdf

    -e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\ERRORLOG

    -l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\mastlog.ldf

    2012-07-07 21:12:04.83 Server Command Line Startup Parameters:

    -m

    -f

    -T 3608

    That's paths for my server, not yours.

    Note that your error log makes no mention of the traceflag being enabled, not sure if that's a side effect of using NET START, because of useing /f not -f or for some other reason. Hence while your SQL instance started in restricted mode, the trace flag was not enabled and it did try to recover the other DBs and hence failed and shut down again.

    With SQL started like that, no database other than master is recovered. You will see no mention of any DB other than master in the error log.

    It's model that you need to fix, SQL can actually start without MSDB, but without model it can't recreate TempDB. Fix model as a priority. Preferably use SQLCMD to connect, SSMS has a habit of making more than one connection and breaking (SQL is in single user mode)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass