Solved

SQL database move from SQL 2000 to SQL 2008

Posted on 2013-01-25
12
217 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 39

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 39

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
 

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 39

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 39

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 39

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 39

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now