Solved

SQL database move from SQL 2000 to SQL 2008

Posted on 2013-01-25
12
225 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

     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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

691 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