Cannot Access any database

  • Hello,

    I have used the Article ID 224071 to move the log files from E: to F and now I have errors.

    I pass the command in a DOS prompt:

    ---------------------------------------

    Net start mssqlserver /c /m /T3608

    ---------------------------------------

    then is Microsoft SQL Server Management Studio:

    ---------------------------------------------------------------------

    sp_attach_db 'model','E:\MSSQL.1\MSSQL\DATA\model.mdf','F:\MSSQL.1\MSSQL\DATA\modellog.ldf'

    GO

    sp_attach_db 'msdb','E:\MSSQL.1\MSSQL\DATA\msdbdata.mdf','F:\MSSQL.1\MSSQL\DATA\msdblog.ldf'

    GO

    sp_attach_db 'Altiris','E:\MSSQL.1\MSSQL\DATA\Altiris.mdf','F:\MSSQL.1\MSSQL\DATA\Altiris_log.ldf'

    GO

    sp_attach_db 'Altiris_Incidents','E:\MSSQL.1\MSSQL\DATA\Altiris_Incidents.mdf','F:\MSSQL.1\MSSQL\DATA\Altiris_Incidents_log.ldf'

    GO

    ----------------------------------------------------------------------

    I could not start the SQLSERVER Service anymore....

    I am getting the error 1814 cannot start SQLSERVER Service:

    ------------------------------------------------------------

    Could not find database ID 3. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

    TITLE: Connect to Server

    -----------------------------

    Cannot connect to ALTIRISDB.

    ------------------------------

    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    I check the files *.mdf are under E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    Master and mssqlsystemresource mdf and ldf remain under E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    *.ldf are under F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data except master.ldf and mssqlsystemresource.ldf.

    The databases are:

    -model, msdb, Altiris, Altiris_incidents

    When opening Microsoft SQL Server Management Studio only two databases are showing:

    - Master(with expansion which cannot opened --- error 40 above)

    - Tempdb(with nothing...)

    Nothing else

    Any ideas are welcome...

    Thank You,

    DOM

    :w00t:

  • First move log files back to the E drive and remove the look up parameter T3608. Please let us know if your database is restored to previous state

  • Let me try

    Thanks

    Dom

  • Hello Dom,

    I think you have messed up with the steps to move the databases from one location to another location. As you are trying to move the databases including the system default, the steps below might help you to troubleshoot your issue further.

    1. Move the user created databases from the source to the destination.

    2. Move the model database as shown in the KB Article 224071 under the heading "Moving the model database" for SQL 2005

    3. Move the msdb database as shown under the heading "Moving the model database" for SQL 2005

    4. Move the master database as shown under the heading "Moving the master database"

    5. Move the tempdb database as shown under the heading "Moving the tempdb database"

    Please verify whether you have done the same.

    Hope this helps.

    Thanks


    Lucky

  • Hello,

    The problem is that I don't have access to Microsoft SQL Server Management Studio as when it tries to connect I am getting the error:

    TITLE: Connect to Server

    ------------------------------

    Cannot connect to ALTIRISDB.

    ------------------------------

    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    So I will try to move back physically the model.ldf and see if it is able to reconnect.

    Thanks

  • Hello,

    Thanks to Rick...

    Solution

    The following are two sets of instructions to try. This gets to be VERY picky so please watch all of the details. For example, I noticed in your log that you used a lower case t in your T3608 switch, Microsoft is starting to use the lower case for private internal switches, so you want to try to use upper case T.

    I tested this on one of my machines by detaching the model, moving the log file and then reattaching it with the new log file location and it worked just fine with this exact procedure.

    =====================================

    =======================================

    Go into SQL Server Configuration Manager

    Go to SQL Server 2005 Services

    Stop everything (the other services were taking up the one 'single-user' slot so I couldn't get in without doing this.)

    Go to SQL Server (MSSQLSERVER) and go to it's properties

    Under the advanced tab go to Startup Parameters

    Change the parameter (the default looks like this)

    --------------------------------------------------------

    -dC:\Program Files\Microsoft SQL

    Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL

    Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL

    Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    ----------------------------------------------------------

    To this

    ----------------------------------------------------------

    -c;-m;-T3608;-dC:\Program Files\Microsoft SQL

    Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL

    Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL

    Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    ----------------------------------------------------------

    It needs to go at the front end of this.

    Apply and OK. It should give you a warning message that these changes will not take place until you restart the service.

    Start the SQL Server (MSSQLSERVER) service up.

    Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG and find Errorlog.

    Look for the startup parameters in the log they should look like this if they

    started properly...

    ----------------------

    2008-01-10 09:45:46.15 Server Registry startup parameters:

    2008-01-10 09:45:46.15 Server -c

    2008-01-10 09:45:46.15 Server -m

    2008-01-10 09:45: 46.15 Server -T 3608

    2008-01-10 09:45:46.15 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

    2008-01-10 09:45:46.15 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

    2008-01-10 09:45:46.15 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    ---------------------------

    not like this

    ---------------------------

    2008-01-10 09:33:58.15 Server Registry startup parameters:

    2008-01-10 09:33:58.15 Server -c -m -T3806

    2008-01-10 09:33:58.15 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

    2008-01-10 09:33:58.15 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

    2008-01-10 09:33:58.15 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    ---------------------------

    If it looks like the wrong one where the parameters are all on one line it seems to not apply those switches.

    Then go to the command line.

    sqlcmd -q"sp_detach_db 'model'"

    >Exit

    sqlcmd -q"sp_attach_db 'model', 'E:\MSSQL.1\MSSQL\DATA\model.mdf','F:\MSSQL.1\MSSQL\DATA\modellog.ldf'"

    >Exit

    Now, assuming that all went well and you have no more need for those switches to be activated, go back into the SQL Server Configuration manager, and remove the switches you added at the start of this.

    From there you should now be able to start up the SQL Server Managment Studio, look at the properties of the Model Database, and make sure that the log file is in the new location.

    ===========================================

    this is working for me after I did the same commands for model and msdb.

    Now I am working on the User DB Altiris as I am getting another error...

    sp_attach_db 'Altiris','E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Altiris.mdf','F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Altiris_log.ldf'

    GO

    sp_attach_db 'Altiris_Incidents','E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Altiris_Incidents.mdf','F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Altiris_Incidents_log.ldf'

    GO

    Msg 5173, Level 16, State 2, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    Warning: Directory 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\HD_workitem_comment' does not have a valid full-text catalog. Full-text catalog header file or attach state file either is missing or corrupted. The full-text catalog cannot be attached.

    Thanks

    Dom

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply