none
one DB in Recovery and two is suspect

    Question

  •  

    Hi all,

    We had to do a repair to SQL 2008 as  the E: Drive was not connected on a restart and the .mdf files were on this drive, the .ldf file are on F: Drive

    After the repair, we connected and have the following errors.

     

    Can anyone help at all?

    2010-10-14 12:12:41.31 Server      Microsoft SQL Server 2008 (SP2) - 10.0.3798.0 (X64)
        Jun 18 2010 16:37:39
        Copyright (c) 1988-2008 Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

    2010-10-14 12:12:41.31 Server      (c) 2005 Microsoft Corporation.
    2010-10-14 12:12:41.31 Server      All rights reserved.
    2010-10-14 12:12:41.31 Server      Server process ID is 3136.
    2010-10-14 12:12:41.31 Server      Authentication mode is WINDOWS-ONLY.
    2010-10-14 12:12:41.31 Server      Logging SQL Server messages in file 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2010-10-14 12:12:41.31 Server      This instance of SQL Server last reported using a process ID of 1068 at 13/10/2010 10:23:29 (local) 13/10/2010 09:23:29 (UTC). This is an informational message only; no user action is required.
    2010-10-14 12:12:41.31 Server      Registry startup parameters:
         -d E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
         -e E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
         -l E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2010-10-14 12:12:41.83 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2010-10-14 12:12:41.83 Server      Detected 2 CPUs. This is an informational message; no user action is required.
    2010-10-14 12:12:41.89 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2010-10-14 12:12:41.91 Server      Node configuration: node 0: CPU mask: 0x0000000000000003 Active CPU mask: 0x0000000000000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2010-10-14 12:12:41.93 spid6s      Starting up database 'master'.
    2010-10-14 12:12:42.15 spid6s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2010-10-14 12:12:42.18 spid6s      CHECKDB for database 'master' finished without errors on 2010-07-30 14:34:10.903 (local time). This is an informational message only; no user action is required.
    2010-10-14 12:12:42.18 spid6s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2010-10-14 12:12:42.19 spid6s      SQL Trace ID 1 was started by login "sa".
    2010-10-14 12:12:42.19 spid6s      Starting up database 'mssqlsystemresource'.
    2010-10-14 12:12:42.22 spid6s      The resource database build version is 10.00.3798. This is an informational message only. No user action is required.
    2010-10-14 12:12:42.33 spid10s     Starting up database 'model'.
    2010-10-14 12:12:42.35 spid6s      Server name is 'BRANDMG-CFE478E'. This is an informational message only. No user action is required.
    2010-10-14 12:12:42.35 spid6s      Informational: No full-text supported languages found.
    2010-10-14 12:12:42.35 spid13s     Starting up database 'msdb'.
    2010-10-14 12:12:42.35 spid14s     Starting up database 'D_Livev1'.
    2010-10-14 12:12:42.35 spid15s     Starting up database 'Prospectt'.
    2010-10-14 12:12:42.35 spid16s     Starting up database 'Datav1'.
    2010-10-14 12:12:42.35 spid17s     Starting up database 'Dv1'.
    2010-10-14 12:12:42.50 Server      A self-generated certificate was successfully loaded for encryption.
    2010-10-14 12:12:42.50 Server      Server is listening on [ 'any' <ipv4> 1433].
    2010-10-14 12:12:42.50 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2010-10-14 12:12:42.50 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2010-10-14 12:12:42.51 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2010-10-14 12:12:42.51 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2010-10-14 12:12:42.51 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2010-10-14 12:12:42.51 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2010-10-14 12:12:42.54 spid15s     Error: 9003, Severity: 20, State: 1.
    2010-10-14 12:12:42.54 spid15s     The log scan number (31:72:1) passed to log scan in database 'Prospect List' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
    2010-10-14 12:12:42.54 spid15s     Error: 3414, Severity: 21, State: 1.
    2010-10-14 12:12:42.54 spid15s     An error occurred during recovery, preventing the database 'Prospectt' (database ID 6) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
    2010-10-14 12:12:42.72 spid17s     Error: 9003, Severity: 20, State: 1.
    2010-10-14 12:12:42.72 spid17s     The log scan number (15466:473:1) passed to log scan in database 'D_Livev1' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
    2010-10-14 12:12:42.72 spid17s     Error: 3414, Severity: 21, State: 1.
    2010-10-14 12:12:42.72 spid17s     An error occurred during recovery, preventing the database 'Datav1' (database ID 8) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
    2010-10-14 12:12:42.94 spid10s     Clearing tempdb database.
    2010-10-14 12:12:44.84 spid10s     Starting up database 'tempdb'.
    2010-10-14 12:12:45.11 spid13s     The Service Broker protocol transport is disabled or not configured.
    2010-10-14 12:12:45.12 spid13s     The Database Mirroring protocol transport is disabled or not configured.
    2010-10-14 12:12:45.15 spid13s     Service Broker manager has started.
    2010-10-14 12:13:17.04 spid52      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2010-10-14 12:13:17.12 spid52      Using 'xpstar.dll' version '2007.100.3798' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    Thursday, October 14, 2010 11:22 AM

All replies

  • Do you have backups of the suspect databases and of the one that is in Recovery?

    If so then you can restore the Databases.


    Please click "Mark As Answer" if my post helped. Tony C.
    Thursday, October 14, 2010 11:46 AM
  • Hi Robert,

    So from errors its clear that there is some mismatch in Log Sequence Number of MDF & LDF files of database so files are not consistent and while opening up database its failing. Now what we can do:

    1) Either we can stop SQL Server, rename existing LDF file and restart SQL Server. On restart SQL Server will re-create the LDF file. BUT in this way we will loose data which is not committed but not yet written to the database file.

    2) We can try to do more research on this database.

          try to bring database in EMERGENCY mode.

         run DBCC CHECKDB with ALL_ERRORMESSAGES

         check what kind of errors are and what is the minimum recovery option and act accordingly.

     Below post will help in executing 2nd option.

    http://sqlserverpedia.com/blog/sql-server-bloggers/how-i-conquered-a-corrupt-database/

    HTH

         


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++++++++++++++++++++++++++++++++++ Please mark "Propose As Answer" if my answer helped +++++++++++++++++++
    Thursday, October 14, 2010 11:56 AM
  • http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 14, 2010 12:13 PM
    Answerer
  • If there is nothing to rollback or rollfwd then SQL will recreate the log file ....if there are any transactions that are left and need the recovery then you can try these :

    Try a Single File attach DB options , that can be a good option to attach a MDF file. In case its not helping you can do an ATTACH FORCE REBUILD LOG which will help you create a New Log file in SQL 2005 instance.But remember you will loose everything in the log file ..so be cautious ..

    1st option

    CREATE DATABASE test
     ON (FILENAME = 'd:\data.mdf' )
     FOR ATTACH_REBUILD_LOG
     CREATE DATABASEFOR ATTACH_REBUILD_LOG

    2nd option (you will loose everything in you log)

    CREATE DATABASE test
     ON (FILENAME = 'd:\data.mdf')
     FOR ATTACH_FORCE_REBUILD_LOG


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, October 14, 2010 12:27 PM
  • <<CREATE DATABASE test
     ON (FILENAME = 'd:\data.mdf')
     FOR ATTACH_FORCE_REBUILD_LOG>>

    Note that above isn't just about losing evrything in the log. The log is used to produced a both logically and physically consistent database. Use tricks like about and you never know what weird state the data and database is in. Some weirnedd might be spotted by DBCC CHECKDB where other won't (like the effect of half-baked transactions on user data). A proper restore is always the ting to strive for here.


    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, October 14, 2010 3:09 PM
    Moderator
  • That the reason I said .........BE cautious ......Its better to have something then nothing :) ...I could not get this point though (like the effect of half-baked transactions on user data).


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, October 14, 2010 3:34 PM
  • <<I could not get this point though (like the effect of half-baked transactions on user data).>>

    I decided to blog it: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/14/why-we-never-want-to-trick-sql-server-into-rebuilding-a-log-file.aspx


    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, October 14, 2010 6:22 PM
    Moderator
  • Hi Robert,

    So from errors its clear that there is some mismatch in Log Sequence Number of MDF & LDF files of database so files are not consistent and while opening up database its failing. Now what we can do:

    1) Either we can stop SQL Server, rename existing LDF file and restart SQL Server. On restart SQL Server will re-create the LDF file. BUT in this way we will loose data which is not committed but not yet written to the database file.

    2) We can try to do more research on this database.

          try to bring database in EMERGENCY mode.

         run DBCC CHECKDB with ALL_ERRORMESSAGES

         check what kind of errors are and what is the minimum recovery option and act accordingly.

     Below post will help in executing 2nd option.

    http://sqlserverpedia.com/blog/sql-server-bloggers/how-i-conquered-a-corrupt-database/

    HTH

         


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++++++++++++++++++++++++++++++++++ Please mark "Propose As Answer" if my answer helped +++++++++++++++++++


    Hi,

    I tried option 1 but there are 3 .ldf files so this did not work as it says there are multiple .ldf files so cannot complete

    Any other options?

    Friday, October 15, 2010 10:18 AM
  • Hi All,

    We have managed to sort out the suspect DB's but we are still struggling to resore the backup and get the following messages.

    We are running Server 2003 with SQL 2008

    We have a database stuck in Recovery, which is not recovering.  We decided to retore from a backup but get the following message when requesting the retore (right click, database restore)

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

    Failed to connect to server BRANM-CFE478E. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
       at Microsoft.SqlServer.Management.Common.ServerConnection.get_FixedServerRoles()
       at Microsoft.SqlServer.Management.Common.ServerConnection.IsInFixedServerRole(FixedServerRoles fixedServerRole)
       at Microsoft.SqlServer.Management.SqlMgmt.AgentAccess.CanUserAccessAgent(Server server, Boolean& isUserSa, Boolean& isMemberOfAgentOperatorRole, Boolean& isMemberOfAgentReaderRole, Boolean& isMemberOfAgentUserRole)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.HasAgentAccess()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.EnableRunType(RunType runType, Boolean state)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.UpdateCommandUiInternal()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

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

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

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

    ------------------------------
    Server Name: BRANM-CFE6478E
    Error Number: 233
    Severity: 20
    State: 0


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParserStateObject.WriteSni()
       at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
       at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
       at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

    It allows us to continure to restore the back up but then comes up with the following error:

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

    Failed to connect to server BRANM-CFE478E. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
       at Microsoft.SqlServer.Management.Common.ServerConnection.get_FixedServerRoles()
       at Microsoft.SqlServer.Management.Common.ServerConnection.IsInFixedServerRole(FixedServerRoles fixedServerRole)
       at Microsoft.SqlServer.Management.SqlMgmt.AgentAccess.CanUserAccessAgent(Server server, Boolean& isUserSa, Boolean& isMemberOfAgentOperatorRole, Boolean& isMemberOfAgentReaderRole, Boolean& isMemberOfAgentUserRole)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.HasAgentAccess()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.EnableRunType(RunType runType, Boolean state)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.UpdateCommandUiInternal()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

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

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

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

    ------------------------------
    Server Name: BRANM-CFE478E
    Error Number: 233
    Severity: 20
    State: 0


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParserStateObject.WriteSni()
       at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
       at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
       at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

    Can anyone help us resolve this please?

    Robert

    Friday, October 15, 2010 10:23 AM
  • See reply in separate thread.
    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, October 15, 2010 10:43 AM
    Moderator
  • Ok, we have rectified the suspect db's, but are still struggling with  database stuck in Recovery, which is not recovering.  We decided to retore from a backup but get the following message when requesting the retore (right click, database restore)

    We are running Server 2003 with SQL 2008 Standard

     

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

    Failed to connect to server CFE685E. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
       at Microsoft.SqlServer.Management.Common.ServerConnection.get_FixedServerRoles()
       at Microsoft.SqlServer.Management.Common.ServerConnection.IsInFixedServerRole(FixedServerRoles fixedServerRole)
       at Microsoft.SqlServer.Management.SqlMgmt.AgentAccess.CanUserAccessAgent(Server server, Boolean& isUserSa, Boolean& isMemberOfAgentOperatorRole, Boolean& isMemberOfAgentReaderRole, Boolean& isMemberOfAgentUserRole)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.HasAgentAccess()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.EnableRunType(RunType runType, Boolean state)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.UpdateCommandUiInternal()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

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

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

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

    ------------------------------
    Server Name: CFE685E
    Error Number: 233
    Severity: 20
    State: 0


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParserStateObject.WriteSni()
       at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
       at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
       at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

    It allows us to continure to restore the back up but then comes up with the following error:

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

    Failed to connect to server CFE685E. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
       at Microsoft.SqlServer.Management.Common.ServerConnection.get_FixedServerRoles()
       at Microsoft.SqlServer.Management.Common.ServerConnection.IsInFixedServerRole(FixedServerRoles fixedServerRole)
       at Microsoft.SqlServer.Management.SqlMgmt.AgentAccess.CanUserAccessAgent(Server server, Boolean& isUserSa, Boolean& isMemberOfAgentOperatorRole, Boolean& isMemberOfAgentReaderRole, Boolean& isMemberOfAgentUserRole)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.HasAgentAccess()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.EnableRunType(RunType runType, Boolean state)
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.UpdateCommandUiInternal()
       at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

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

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

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

    ------------------------------
    Server Name: CFE685E
    Error Number: 233
    Severity: 20
    State: 0


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParserStateObject.WriteSni()
       at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
       at System.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec)
       at System.Data.SqlClient.SqlInternalConnectionTds.Login(Int64 timerExpire, String newPassword)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
       at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

    Can anyone help us resolve this please?

    Robert

    Friday, October 15, 2010 10:47 AM
  • Robert, please do a commandline restore from query analyzer: restore database <database name> form disk ='full path' ..


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, October 15, 2010 12:32 PM
  • See reply in separate thread.
    Tibor Karaszi, SQL Server MVP | web | blog


    Thanks Tibor.

    We tried that and get the following errors:

    2010-10-15 13:42:41.62 spid52      Error: 18204, Severity: 16, State: 1.
    2010-10-15 13:42:41.62 spid52      BackupDiskFile::OpenMedia: Backup device 'C:\DreamMedia.bak' failed to open. Operating system error 2(failed to retrieve text for this error. Reason: 1815).
    2010-10-15 13:44:29.18 spid52      Error: 18204, Severity: 16, State: 1.
    2010-10-15 13:44:29.18 spid52      BackupDiskFile::OpenMedia: Backup device 'Z:\DreamMedia.bak' failed to open. Operating system error 3(failed to retrieve text for this error. Reason: 1815).

    Any thoughts?

     

    Friday, October 15, 2010 12:49 PM
  • OS error 2 is "The system cannot find the file specified.". Make sure that you have a valid path, and that the service account for SQL Server has permissions on the folder/files.
    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, October 15, 2010 1:00 PM
    Moderator
  • I have checked the permissions and these are granted.  How would I be able to check if the path is valid?
    Friday, October 15, 2010 2:18 PM
  •  

    Thanks Abhay,

    The db is in recovery so this wont work as the db is 'in use'

    Any other thoughts?

    Friday, October 15, 2010 2:28 PM
  • try this : restore database <dbname> from disk ='<full path with file>' with replace.Run it from master ...before that see if there is any SPID open for this database and kill it if possible...
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, October 15, 2010 3:34 PM
  • <<I have checked the permissions and these are granted.>>

    To the SQL Server service account? This is the one that counts. The path need to be a valid path on the server machine (walk over to the server, log on locally to the machine (preferrably as the SQL Server service account), open explorer, and see if you can browse to the file. Also, Z seems to be a mapped drive (see error message), won't fly. Use UNC naming if you try to access file over network.


    Tibor Karaszi, SQL Server MVP | web | blog
    Friday, October 15, 2010 6:13 PM
    Moderator