Solved

SQL database move from SQL 2000 to SQL 2008

Posted on 2013-01-25
12
219 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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