I have databases running on sqlserver 2005. But I need to upgrade that to sqlserver 2008. Please advice if there is any step by step document that I need to follow to upgrade the databases to sqlserver 2008. Also I had one other question, is it easier to install sqlserver 2008 and migrate the databases.
asked Jan 11, 2012 at 01:25 PM in Default
Upgrade advisor should be ran to check for any stored procedures using deprecated code. Other than that I have had great success upgrading 2005 to 2008. If you can do side by side you can simply backup the databases and restore them to the new server. You will have to migrate users, jobs, ssis, dts, etc. An in place upgrade is also very feasible to do and will eliminate the need to migrate users and such, but can be much more invasive if the upgrade fails and blows up your system.
What is most important is to test this upgrade in a development environment first.
As for a step by step document, I am not familiar with one as each shop is different.
I have been working for some months now, on and off migrating 2000 to 2008, in my own time as the business has only just engaged with upgrading.
I have not been able to find any step by step instructions, but as others have mentioned run the upgrade advisor and work through resolving all the issues identified first.
What has work best for me is creating a clean install of 2008R2 and bring the BD's across to the new instance. I have tested both backup restores and detach and attach to bring them over. So far I have experienced no major problems, will use detach & attach FYI when I migrate.
I think you will be ok as far as a DB migration goes, your better placed on 2005 then I. The most important thing is to test, test & test some more is the application that attaches to your database. That is where any failures are likely to occur.
Get your application users involved in testing.
Final word of advice, seems like a big job to start with, so pick an outline plan of action, break it down, documenting it steps by step. Use the net, MSDN and places like this to find out how to action the individual steps. When you think you have a plan, test, and test again if it works in principle adjust where it fails and re-run until your happy.
answered Jan 12, 2012 at 05:31 AM
Others have explained nicely the steps needed but you might want to read this http://msdn.microsoft.com/en-us/library/bb677622(v=SQL.100).aspx if you are new to carrying out backups/restores or detaching/attaching databases. A lot will depend in your set up - whether you have a new server or the new version is going on existing hardware, whether you have a test environment (if you dont then once you get the upgrade done work on creating one) and so on.
answered Jan 12, 2012 at 05:58 AM