SBS 2000 SQL 2000 import to SBS 2003 SQL 2005

Discussion in 'Windows Small Business Server' started by HN, Jul 24, 2007.

  1. HN

    HN Guest

    Hello

    I have to import a SQL 2000 database (currently in SBS 2000) to my new SBS
    2003 (Premium) SQL 2005 install. I have already carried out a backup of the
    database and copied it to the new server but am now unsure what to do next.

    I understand that there are some import wizards in SQL 2005 that will enable
    this but I cannot find them.

    Grateful if someone could point me in the right direction and indeed confirm
    that this is the correct method of transferring a SQL 2000 database.

    Many thanks in advance.
     
    HN, Jul 24, 2007
    #1
    1. Advertisements

  2. HN

    Paul Shapiro Guest

    You can restore the SQL 2K backup on the SQL 2K5 server. You may need to
    check the location where it wants to put the data and log files if the disk
    structure is different or if you want to move them to a new location.
    Paul Shapiro
     
    Paul Shapiro, Jul 24, 2007
    #2
    1. Advertisements

  3. Hello Customer,

    Thank you for posting here.

    According to your description, I understand that you want to know how to
    migrate the SQL 2000 on SBS 2000 to SQL 2005 on SBS 2003. If I have
    misunderstood the problem, please don't hesitate to let me know.

    Based on my research, we can perform the steps in the following document:

    How to move databases between computers that are running SQL Server
    http://support.microsoft.com/kb/314546/

    Installing SQL Server 2005 WorkGroup Edition in Windows Small Business
    Server 2003 R2
    http://download.microsoft.com/download/4/0/8/40860507-c351-4308-a876-e1b83ee
    4e77a/sqlinstallsteps.htm

    Hope these steps will give you some help.

    Thanks and have a nice day!

    Best regards,

    Terence Liu(MSFT)

    Microsoft CSS Online Newsgroup Support

    Get Secure! - www.microsoft.com/security

    =====================================================
    This newsgroup only focuses on SBS technical issues. If you have issues
    regarding other Microsoft products, you'd better post in the corresponding
    newsgroups so that they can be resolved in an efficient and timely manner.
    You can locate the newsgroup here:
    http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

    When opening a new thread via the web interface, we recommend you check the
    "Notify me of replies" box to receive e-mail notifications when there are
    any updates in your thread. When responding to posts via your newsreader,
    please "Reply to Group" so that others may learn and benefit from your
    issue.

    Microsoft engineers can only focus on one issue per thread. Although we
    provide other information for your reference, we recommend you post
    different incidents in different threads to keep the thread clean. In doing
    so, it will ensure your issues are resolved in a timely manner.

    For urgent issues, you may want to contact Microsoft CSS directly. Please
    check http://support.microsoft.com for regional support phone numbers.

    Any input or comments in this thread are highly appreciated.
    =====================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    | Thread-Topic: SBS 2000 SQL 2000 import to SBS 2003 SQL 2005
    | thread-index: AcfN5NsglFZVWK8OQkO+uo8QVFwKjQ==
    | X-WBNR-Posting-Host: 207.46.19.197
    | From: =?Utf-8?B?SE4=?= <>
    | Subject: SBS 2000 SQL 2000 import to SBS 2003 SQL 2005
    | Date: Tue, 24 Jul 2007 04:22:01 -0700
    | Lines: 14
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | Newsgroups: microsoft.public.windows.server.sbs
    | Path: TK2MSFTNGHUB02.phx.gbl
    | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.windows.server.sbs:52183
    | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
    | X-Tomcat-NG: microsoft.public.windows.server.sbs
    |
    | Hello
    |
    | I have to import a SQL 2000 database (currently in SBS 2000) to my new
    SBS
    | 2003 (Premium) SQL 2005 install. I have already carried out a backup of
    the
    | database and copied it to the new server but am now unsure what to do
    next.
    |
    | I understand that there are some import wizards in SQL 2005 that will
    enable
    | this but I cannot find them.
    |
    | Grateful if someone could point me in the right direction and indeed
    confirm
    | that this is the correct method of transferring a SQL 2000 database.
    |
    | Many thanks in advance.
    |
    |
     
    Terence Liu [MSFT], Jul 25, 2007
    #3
  4. HN

    HN Guest

    Thank you Terence

    You have I believe understood my query.

    However, I really need something that explains a little more clearly how to
    import my old sql 2000 database into SQL 2005 SBS 2003 R2 edition.

    I have also just tried to connect one of my XP SP2 workstations to the
    database and it comes up with an error

    "connection failed SQL state: 28000 sql server error: 18452 [Microsoft][odbc
    sql server driver][sql server] login failed for user "SA". The user is not
    associated with a trusted SQL server connection.

    Any further guidance would be gratefully received.

    Thank you
     
    HN, Jul 25, 2007
    #4
  5. A simple attach/detach database from SQL Server 2000 to 2005 would do
     
    bass_player [SBS-MVP], Jul 26, 2007
    #5
  6. HN

    HN Guest

    I believe I have imported the database into SQL 2005 now but I cannot get any
    client to use it. The message below keeps occuring.

    Further assistance would be gratefully received.
    Thanks

     
    HN, Jul 26, 2007
    #6
  7. Hello Customer,

    Thank you for your kind update.

    From your further description and your client side error message, I can see
    your SQL is not use for SBS built-in components (sharepoint, monitoring),
    this is pure SQL issue.

    I'm not familiar with pure SQL issue. I would like to suggest you repost
    your issue in SQL newsgroup for further assistance. It is a more effective
    ways.

    This newsgroup is primarily for issues involving deployment, configuration,
    consultation and troubleshooting. The reason why we recommend posting
    appropriately is you will get the most qualified pool of respondents, and
    other partners who read the newsgroups regularly can either share their
    knowledge or learn from your interaction with us.

    SQL Newsgroup: microsoft.public.sqlserver.server

    I hope the information above is helpful.

    Thanks for your understanding and have a nice day!

    Best regards,

    Terence Liu(MSFT)

    Microsoft CSS Online Newsgroup Support

    Get Secure! - www.microsoft.com/security

    =====================================================
    This newsgroup only focuses on SBS technical issues. If you have issues
    regarding other Microsoft products, you'd better post in the corresponding
    newsgroups so that they can be resolved in an efficient and timely manner.
    You can locate the newsgroup here:
    http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

    When opening a new thread via the web interface, we recommend you check the
    "Notify me of replies" box to receive e-mail notifications when there are
    any updates in your thread. When responding to posts via your newsreader,
    please "Reply to Group" so that others may learn and benefit from your
    issue.

    Microsoft engineers can only focus on one issue per thread. Although we
    provide other information for your reference, we recommend you post
    different incidents in different threads to keep the thread clean. In doing
    so, it will ensure your issues are resolved in a timely manner.

    For urgent issues, you may want to contact Microsoft CSS directly. Please
    check http://support.microsoft.com for regional support phone numbers.

    Any input or comments in this thread are highly appreciated.
    =====================================================

    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    | Thread-Topic: SBS 2000 SQL 2000 import to SBS 2003 SQL 2005
    | thread-index: AcfPXpvH1zjfb3aaQSqXacuJ8/6HYw==
    | X-WBNR-Posting-Host: 207.46.19.168
    | From: =?Utf-8?B?SE4=?= <>
    | References: <>
    <$>
    <>
    <#>
    | Subject: Re: SBS 2000 SQL 2000 import to SBS 2003 SQL 2005
    | Date: Thu, 26 Jul 2007 01:26:05 -0700
    | Lines: 36
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
    | Newsgroups: microsoft.public.windows.server.sbs
    | Path: TK2MSFTNGHUB02.phx.gbl
    | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.windows.server.sbs:52590
    | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
    | X-Tomcat-NG: microsoft.public.windows.server.sbs
    |
    | I believe I have imported the database into SQL 2005 now but I cannot get
    any
    | client to use it. The message below keeps occuring.
    |
    | Further assistance would be gratefully received.
    | Thanks
    |
    | "bass_player [SBS-MVP]" wrote:
    |
    | > A simple attach/detach database from SQL Server 2000 to 2005 would do
    | >
    | > | > > Thank you Terence
    | > >
    | > > You have I believe understood my query.
    | > >
    | > > However, I really need something that explains a little more clearly
    how
    | > > to
    | > > import my old sql 2000 database into SQL 2005 SBS 2003 R2 edition.
    | > >
    | > > I have also just tried to connect one of my XP SP2 workstations to the
    | > > database and it comes up with an error
    | > >
    | > > "connection failed SQL state: 28000 sql server error: 18452
    | > > [Microsoft][odbc
    | > > sql server driver][sql server] login failed for user "SA". The user
    is not
    | > > associated with a trusted SQL server connection.
    | > >
    | > > Any further guidance would be gratefully received.
    | > >
    | > > Thank you
    | > >
    | > >
    | >
    | >
    | >
    |
     
    Terence Liu [MSFT], Jul 26, 2007
    #7
  8. HN

    HN Guest

    ok will do thank you.
     
    HN, Jul 26, 2007
    #8
  9. HN

    Paul Shapiro Guest

    If the error you're referring to is the "login failed" error below, it looks
    like your application is trying to connect to the db with the SQL Server SA
    login. The default SQL Server 2005 installation disables local SQL Server
    authentication and only enables integrated Windows authentication. You could
    try changing your application to connect using Windows authentication, or
    modify the SQL Server settings to allow local authentication. You might also
    have to create the necessary login at the server level, and add the user to
    the necessary database.

    Allowing applications to connect as SA (System Administrator) is both a
    security risk and a data risk, since that account has full privileges in all
    databases on the server.

     
    Paul Shapiro, Jul 26, 2007
    #9
  10. HN

    Paul Shapiro Guest

    Using an Access 2003 adp project against a SQL 2005 database I had to assign
    all the SQL tables to the dbo schema. Access 2003 doesn't seem to understand
    schemas, which makes sense since they didn't exist in SQL 2000. Schema does
    not seem to map to the older owner concept. If your application lets you
    adjust the connection string, people have reported better success using the
    new SQL Native Client driver instead of the older oledb driver. If the
    Native Client isn't available on the client computer, it can be installed
    with the SQL client tools.
    Paul Shapiro
     
    Paul Shapiro, Jul 26, 2007
    #10
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.