Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL database move from SQL 2000 to SQL 2008

Posted on 2013-01-25
12
Medium Priority
?
227 Views
Last Modified: 2014-05-15
We need to retire an old server running SQL 2000. This server is running one last production database that needs to be migrated to our SQL 2008 server. I have taken a backup and restored it on the SQL 2008 server. When I attempt to connect to it via ODBC to test, I am unable to complete the login. I have looked at the current log file on the SQL 2008 server and see the following error for the login:
Login failed for user "RFEK\dschumacher". Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.0.196]
Error:18456, Severity: 14, State: 11.

Any help in quickly resolving this would be greatly appreciated.
0
Comment
Question by:RFEMHelpdesk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 38820340
You need to transfer all SQL logins (including the NT ones) from old box to the new one but you should have done it before the restore because the logins from those DB would have binded to these from the server level. You can still do it now and the worst you may need to remove and add them back to each db after restore them in SQL 2008

http://support.microsoft.com/kb/246133/en-us
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38820349
Oh...and for the NT authentication if the server is not on the same domain or trusted one...you get the idea...
0
 

Author Comment

by:RFEMHelpdesk
ID: 38820743
Thank you so much for your response. Actually I am quite a newbie when it comes to SQL. The link you provided appears to be for a transfer between 2000 and 2005 and I am moving to 2008. Upon running the script it appears that there are quite a few logins and other information that does not pertain to this database. How can I copy over only the logins associated with this specific database?

Would I be able to delete the database on the 2008 sever and start over? Would it be possible to get a step by step procedure to do this....correctly?

~Schu
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:RFEMHelpdesk
ID: 38828101
Thank you so much for your response. Actually I am quite a newbie when it comes to SQL. The link you provided appears to be for a transfer between 2000 and 2005 and I am moving to 2008. Upon running the script it appears that there are quite a few logins and other information that does not pertain to this database. How can I copy over only the logins associated with this specific database?

Would I be able to delete the database on the 2008 sever and start over? Would it be possible to get a step by step procedure to do this....correctly?

~Schu
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38828199
Sure you can drop the database on SQL 2008 but make sure you have a good source (SQL 2000) FULL backup copy available and ONLY if this new SQL 2008 db was never used (sorry for late reply...)
0
 

Author Comment

by:RFEMHelpdesk
ID: 38828216
Any chance you might be able to answer the other questions?

~Schu
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 38828239
Ok, briefly these are the steps I would perform assuming SQL 2008 server was installed clean and just waiting for new database(s).

1. Script all SQL Jobs from 2000 server (in Enterprise manager or 2008 management studio you can connect to old 2000 server, select Jobs under SQL Agent, highlight all, right click and scrip[t to new window - then save the script.)
2. Script ANY other Server objects from old SQL 2000 (if you have any - like Linked Servers, DTS packages, etc... the same way - it's easy from Enterprise manager because it's just Right click - script as, then save so you can execute the script(s) on new box.
3. use the script above to transfer logins and run it on SQL 2000 - the result will be a list of SQL and NT logins with hashed passwords to be created on new box - save it or copy the results and run in new SQL 2008.

Disconnect any activity from SQL 2000 and
4. FULL backup all DBs on SQL 2000 then copy them to new box and restore.
5. Run on new SQL 2008 all scripts generated from 2000 - Scheduled, jobs, import DTS packages if any, etc.
0
 

Author Comment

by:RFEMHelpdesk
ID: 38828322
Thank you for that but, I am only interested in moving one database and it's associated login's as the rest of the databases had already been moved or are now no longer used.

~Schu
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38828679
Then just make sure you have the full backup from 2000 available and drop the one from SQL 2008, transfer logins as per Microsoft Article (you can remove all that are not needed in new SQL 2008 environment) then restore the database on the new SQL 2008.
0
 

Author Comment

by:RFEMHelpdesk
ID: 38831250
Does drop mean delete?

~Schu
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38836781
Yes - DROP DATABASE from T-SQL translates to Delete in SSMS.
0
 

Author Comment

by:RFEMHelpdesk
ID: 38836848
Let me give this a try and I will post back. My apologies for all of the questions and back and forth. I am definitely NOT a db Admin!

~Schu
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question