Question

  • Creator
    Topic
  • #2221203

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

    Locked

    by asizemore ·

    Hello, I need some help ASAP. Our server crashed a week ago and we need to get a database back up. Problem is we ran our old database on SQL 2000 and now our OS is SBS 2003 which they don’t recommend installing SQL 2000 back up so telling us to upgrade to SQL 2005. I get the DB to load through the restore, but I don’t get access from my front-end client due to the whole login error problem. But All answers I’ve seen are when you have a db up to get info from which I don’t have since it totally crashed. All I have is the .bak file. How can I get this to connect with my front end client? I’ve searched and found posts but they are a bit confusing, and when I try them it isn’t working, so I must be missing something.

All Answers

  • Author
    Replies
    • #2453070

      Clarifications

      by asizemore ·

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

      Clarifications

    • #2453020

      You will probably need

      by cmiller5400 ·

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

      It has been a while since I have done SQL2000 work, but my guess is that the logins that have been restored to the local DB, do not exist in the master db. You will need to probably recreate the logins (if they are sql logins, if NT, just give them rights) and re-associate them with the restored DB.

    • #2452991

      This might give you.

      by Anonymous ·

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

      A few pointers.

      Copied from:

      http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=162455&SiteID=1

      To restore a SQL 2000 database on SQL 2005

      create a new database in 2005 with the same name and compatibility set for SQL 2000 or the version you want to restore from.

      restore this database with options ‘overwrite existing database’

      i did this from one server to another, but it is also possible from a .bak file ofcourse.
      Hope this helps you.

      Please post back if you have more problems or questions.

      • #2551462

        Ok…

        by sigman ·

        In reply to This might give you.

        Are you using SQL Server or Windows Authentication (i.e., do you connect with your network account or enter a separate username and password)?

        Do you know what username you are trying to connect as?

        If SQL Server, you will need to create the (probably) non-existent SQL Server login and give it a password, then you will need to run “sp_change_users_login ‘auto_fix’, , ‘” to link the login to the database username. Then you should be able to make that login dbo and/or do whatever you need to.

        If Windows Authentication, open a query analyzer window, change the current database to the recovered database and run “sp_grantdbaccess ‘domainname\windowsaccount’,

        Obviously replace the info in the angle brackets with your choices.

        I’m in Gaithersburg, too, so let me know if you still need help.

    • #2551489

      SQL users

      by mjdevlugt ·

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

      Sometimes you can script out the SQL users that exist with drop / create but the passwords would need to be reset.

      You can right click the database and go to Tasks, Generate scripts.

      Hope that Helps.

Viewing 3 reply threads