Networks

Ask something
Question Locked

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

0 helpful
5 replies

by asizemore

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

5 replies

by cmiller5400

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.

by Former member

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.

by sigman

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.

by mjdevlugt

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.

This discussion is closed to new replies.