Solved

SQL database move from SQL 2000 to SQL 2008

Posted on 2013-01-25
12
221 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 500 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 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