February 22, 2012 at 11:50 am
I have a 2005 box that needs to be upgraded to 2008R2. I can't do inplace upgrade since currently SQL is installed on C drive
and that needs to be moved to Z drive.
Seems like to me I need to backup everything, uninstall 2005, put clean R2 and restore the databases.
I have lots of SSIS, SQL jobs including replication on this box. What would be the correct steps to migrate to 2008R2?
without losing logins, ssis and jobs.
February 23, 2012 at 7:30 am
Anybody can help here? I would really appreiciate it.
February 23, 2012 at 7:34 am
haven't we already had a topic on this
http://www.sqlservercentral.com/Forums/Topic1253238-1550-1.aspx
February 23, 2012 at 7:37 am
Yes. Sorry. I was waiting on response on the other thread and thought I might have used the wrong forum. Thanks again.
February 24, 2012 at 10:47 am
First off if you can get another server to install sql2008 on and then work on getting everything over to it that is a much better and safer option than deinstalling and reinstalling in case something goes bad.
First off things you will need to do....
Script out ALL logins
Create empty application database in sql2008.
use the script to create the logins.
Backup the SQL 2005 application dbs and restore them into sql2008.
For the Integration Services packages someone else can comment on that because we don't have any.
You cannot restore master, model, msdb from 2005 to 2008. So, any SQLAgent jobs will need to be scripted out and recreated in SQL 2008.
Depending on your applications you will either leave the same or change the database compatibility mode of the application dbs from SQL2005 to 2008.
RUn update stats on all application databases. If you have the time rebuild all the indexes on all application database indexes.
TEST, TEST, TEST!
February 24, 2012 at 11:08 am
February 24, 2012 at 12:23 pm
When I uninstall 2005, all the db files will be in their folder, correct? They don't get wiped out, correct? Because I want to be able to attach those db files after I install 2008.
February 24, 2012 at 12:31 pm
I believe they do. However, a word of warning... shutdown SQL Server BEFORE you uninstall and make a copy of that folder as a CYA.
February 27, 2012 at 4:41 am
Any upgrade plan from SQL Server version X to version Y (for example from SQL 2005 to SQL 2008) that uses an upgrade in place or uninstall reinstall on the same Windows instance is inherently high risk.
This is the type of thing you might want to do with disposible data just to pactice your skills, but you should avoid it for any real data.
If you need to upgrade SQL Server version, then get a new install of Windows and install your desired SQL Server version, and plan how you will move your databases from your old machine to your new one.
If you do an in-place upgrade, there are documented situations where adding .Net 3 which is needed for SQL 2008 simply does not work, and the only way forward is to build a new server. There are also documented problems where an uninstall has left orphan registry items that prevent the install of the new version from working. Any plan you have for doing an upgrade MUST include building a new server to cover the situations where the in-place or uninstall/reinstall fails part-way through.
You should also test the new environment before you commit to moving your production data to it. The process you use to build your test environment should be a rehersal for the process you use for building your new production environment. The only way to properly rehearse an in-place or uninstall/reinstall upgrade is to take disk images of your production server and restore these to a second server to practice on, and all this is more work than a new build and remains more risky than a new build.
(If you ever want to do just an edition upgrade, for example from SQL 2008 R2 Evaluation to SQL 2008 R2 Standard, then this can easily be done on the same server in a couple of minutes using the edition upgrade feature in the install process.)
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply