HELP!! Need to Restore SQL 2000 DB in SQL 2005

  • Angela S.

    SSC Veteran

    Points: 278

    OK when I tried to restore to 2005 it gave as Master_2000 it have me an error, something about sys....something existed already. I installed a version of 2000 on a computer that just came in. How do I install over the master database though to get my backup info versus master that installs when installing 2000?

  • george sibbald

    SSC Guru

    Points: 104200

    asizemore (3/31/2008)


    OK when I tried to restore to 2005 it gave as Master_2000 it have me an error, something about sys....something existed already. I installed a version of 2000 on a computer that just came in. How do I install over the master database though to get my backup info versus master that installs when installing 2000?

    you do not have to , can use the restore as another database option again.

    If you want to restore over existing master database. (SQL version must be exactly the same)

    Backup current one first

    stop sql server

    belt and braces - copy systemdb files to another directory

    restart sql server in single user mode from a command line

    sqlservr.exe -m or net start mssqlserver \m

    from query analyser run restore database master from disk - 'your backup' with replace

    Sql server wil stop

    restart in multi user mode in normal fashion - proceed with sp_help_revlogin script

    I have to go now and not back till wednesday - good luck:)

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

  • Angela S.

    SSC Veteran

    Points: 278

    OK I may have gotten the master copied over, not sure. Another question I do have. In my databases that run through the ODBC on the computers I use to have it set up through SQL server (the 2000 driver), do I need to set those up to be the 2005 driver which is SQL native client? or will it run off the 2000 driver?

    Though even with the passwords and users and permissions from backup db passed over I am still getting a login failed so I will start a new topic on this alone.

  • george sibbald

    SSC Guru

    Points: 104200

    Angela, if you were using ODBC to connect before to 2000, it should still work, presuming the server name was not changed when you rebuilt it.

    a number of other reasons have been stated in this post why you might get login failures., check them out

    Turn on auditing for login failures and run profiler to see if the connection is even getting as far as SQL

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

  • george sibbald

    SSC Guru

    Points: 104200

    Angela, for the education of others, can you post the eventual fix in this post as well

    glad you got there in the end

    cheers

    george:)

    the actual reason for the logon failure turned out to be that in sql 2005 the password is case sensitive and in the odbc connection string the cases did not match to SQL.

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

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Angela S. (3/31/2008)


    OK I may have gotten the master copied over, not sure. Another question I do have. In my databases that run through the ODBC on the computers I use to have it set up through SQL server (the 2000 driver), do I need to set those up to be the 2005 driver which is SQL native client? or will it run off the 2000 driver?

    Though even with the passwords and users and permissions from backup db passed over I am still getting a login failed so I will start a new topic on this alone.

    ODBC will continue to work, but the Native client should give you better performance if you can switch to it...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Angela S.

    SSC Veteran

    Points: 278

    How do I get the SQL native client driver on my workstations to change it to be the native client because right now the driver just exists on my Server to choose or my one desktop where I installed Server management studio which I don't want to install on all computers?

    Is it as simple as copying the driver from system32 folder and placing in appropriate folder on each workstation?

  • Key DBA

    SSCertifiable

    Points: 6029

    Angela,

    There are windows registry keys involved (and other behind the scenes installation components) for installing the SQL Server Native Client.

    For loading the SQL Server Native Client on your workstations for this application, you can find it as a component of the "Feature Pack for Microsoft SQL Server 2005 - April 2006".

    http://www.microsoft.com/downloads/details.aspx?familyid=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&displaylang=en

    Scroll down to the "Microsoft SQL Server Native Client " section and choose the blue highlighted link appropriate for your clients and download the MSI file:

    X86 Package (sqlncli.msi) - 3516 KB

    X64 Package (sqlncli_x64.msi) - 6403 KB

    IA64 Package (sqlncli_ia64.msi) - 8376 KB

    Execute the MSI file on each machine. (I am not sure whether or not the user installing the MSI file has to have Administrative rights, but it is safer to be logged in as a local machine Administrator.) Once completed you will need to create a new DSN that uses the SQL Server Native Client driver, that is pointed to the new SQL Server 2005 database. That should be it.

    Keep us posted,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 8 posts - 16 through 23 (of 23 total)

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