none
Restore a SQL DB To another instance

    Dotaz

  • Does anyone know how to restore a SQL DB to another SQL server instance? Dealing with SQL server express 2005 DB to SQ Server 2008. Different instances.

    22. března 2012 19:20

Všechny reakce

  • backup & restore is the way to go. (use SQL Server Management Studio & backup/restore wizard in your case)

    it works as long as you go from lower to same or higher version and from lower edition to same or higher edition.

    the only thing you've to handle separately are the SQL logins which needs to be recreated on the new server.



    22. března 2012 20:25
  • we Tried using the copy wizard and are receiving an error. We also tried copy and restore with the same error . The oringinal DB is from SQl server express 2005 and trying to restore to  sql server 2008. Is that the issue? I've posted the error. Thanks so much for replying!!

     

    SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 192.168.0.209].

    Log Name:      Application

    Source:        MSSQLSERVER

    Date:          3/22/2012 1:11:23 PM

    Event ID:      17806

    Task Category: Logon

    Level:         Error

    Keywords:      Classic

    User:          N/A

    Computer:      sql.waggonereng.com

    Description:

    SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 192.168.0.209].

    Event Xml:

    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

      <System>

        <Provider Name="MSSQLSERVER" />

        <EventID Qualifiers="49152">17806</EventID>

        <Level>2</Level>

        <Task>4</Task>

        <Keywords>0x80000000000000</Keywords>

        <TimeCreated SystemTime="2012-03-22T18:11:23.000000000Z" />

        <EventRecordID>1342624</EventRecordID>

        <Channel>Application</Channel>

        <Computer>sql.waggonereng.com</Computer>

        <Security />

      </System>

      <EventData>

        <Data>8009030c</Data>

        <Data>14</Data>

        <Data>AcceptSecurityContext failed. The Windows error code indicates the cause of failure.</Data>

        <Data> [CLIENT: 192.168.0.209]</Data>

        <Binary>8E4500001400000004000000530051004C00000000000000</Binary>

      </EventData>

    </Event>

    22. března 2012 20:31
  • when do you get the error "SSPI handshake failed..." ?

    during the copy resp. backup&restore or when you're using your client to access the database on the new SQL Server 2008 ?

    in the 2nd case, you need to recreate the SQL Logins on the new server and map to the database users.

    Is the SQL Server 2008 in the same domain as the client computer?

    22. března 2012 20:55
  • During the restore
    22. března 2012 22:10
  • During the restore

    did you execute the restore from within SSMS 2008 or 2005 ?

    why not just try backup the database on SQL 2005, copy the backup to the SQL 2008 server and the restore the database using SSMS 2008 (GUI or script)?

    23. března 2012 21:04