Migrate Databases from SQL 2005 to SQL 2008 between Virtual or Dedicated Servers

1. Launch Remote Desktop and Connect to your SQL 2005 based Server; Start > Run > mstsc



2. Launch SQL Management Studio, Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio



3. Connect using Windows Authentication



4. Expand Databases
5. Right click on Database you wish to transfer
6. Select Tasks, Backup



7. Note the location of the backup file under Destination



8. Click OK to proceed
9. Click OK



10. Exit from SQL Management Studio
11. Copy the backup file to SQL 2008 based Server; make note of file location
12. Launch Remote Desktop and Connect to your SQL 2008 based Server; Start > Run > mstsc



13. Launch SQL Management Studio, Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio
14. Connect using Windows Authentication



15. Right click on Databases
16. Click Restore Database



17. Enter the database name in To database:



18. Select From device and Click ...



19. Click Add



20. Browse to the location of the backup file you previously created



21. Click OK



22. Click OK
23. Select the backup sets to restore



24. Click OK
25. Your database should be successfully restored



26. Expand Security
27. Right Click Logins > Select New Login

    

28. Enter a Login name
29. Select SQL Server authentication
30. Enter and Confirm Password
31. Uncheck User must change password at next login
32. Under Default database select the restored database



33. Click OK
34. Click New Query



35. Select the restored database from the drop-down



36. In the query window enter the following; be sure to replace userName with the SQL user assocated with this database
EXEC sp_changedbowner 'sa' EXEC sp_change_users_login 'Update_One', 'userName', 'userName'



37. Click Execute



38. If this executes successfully you should see "Command(s) completed successfully."

Your database is now fully available on the SQL 2008 server and is mapped to the SQL login specified.