February 16, 2012 at 9:02 am
Hi, I have 2005 installed on C drive. But I would like to upgrade 2005 to 2008R2 but to a different drive (Z:)...How do I go about this?
February 16, 2012 at 9:08 am
Do you want to run 2005 and 2008R2 side by side? If so you will need to create a named instance of 2008R2.
During the install it will ask for Default or Named instance and where you want the data diectories, just a case of selecting the Z drive and a name for SQL eg SQL2008R2 so you would connect as SERVERNAME\SQL2008R2
February 16, 2012 at 9:10 am
No. Acutually only one instance => 2008R2. Upgrade 2005 to 2008R2 and maintain only one instance but in Z drive (not on C drive where 2005 currently is).
February 16, 2012 at 9:15 am
If you want to use the default instance where you just use the servername to connect via SSMS etc then you need to uninstall 2005 first (that is if 2005 is installed as default), then install 2008R2 on the Z drive using the data directories options in the install.
you will need to backup / restore or detach / attach your databases and make a note of all your login information before you do any uninstalling.
other wise install them side by side then do a tansfer login SSIS package and then backup/restore or detach/attach
February 16, 2012 at 9:19 am
OK. Then how about SSIS? I have lots of SSIS in 2005. Will they be upgraded when I restore msdb to 2008R2?
February 16, 2012 at 9:24 am
Well thats a different kettle of fish all together, as its a system database I wouldnt backup and restore it as your upgrading, yet script all the SSIS packages out and re-upload them.
the attached file will pull all your DTSX packages out, just needs tweaking for a drive and server
you will need to rename the file to .ps1 instead of .zip as SSC doesnt allow PS1 file uploads
February 16, 2012 at 1:51 pm
Sorry...How do I run this? Through Powershell?
February 17, 2012 at 1:19 am
yep through powershell, there is a variable at the top for the servername, think its set to localhost as default in the script.
make sure you have c:\temp directory as well and that your C drive has enough space to export all your SSIS packages.
if you have the solution files in TFS or another source control you can simply reupload them instead of running this script and reimporting.
February 17, 2012 at 6:30 am
Sweet. Is there any easy way to upload them in the server as well or just manually upload them one by one?
Also, since I am a newbeie on this what would you suggest would be best approach without losing logins/data/SSIS packages/Jobs? I have 2005 on C drive. But I want to put only one instace 2008R2 on Z drive? Would side by side installation good or wipe out 2005 first and install R2?
February 17, 2012 at 6:50 am
if you can do side by side then this is by far the best approch to upgrading.
you could do side by side now, but would mean you need to create a named instance which would mean reconfiguring any connection strings from SERVERNAME to SERVERNAME\INSTANCENAME
you could then use SSIS to transfer logins, script any jobs and copy data etc and you can then test the upgrade process before going live with the upgrade.
if you dont want to change connection strings then you need to do an uninstall reinstall to keep the connection string the same which means scripting logins, jobs etc
you could look at doing an in place upgrade which goes and upgrades direct from 2005 to 2008 which would upgrade your system databases, but never done an in place with a drive move at the same time, so unsure if it will work as intended
as for uploading the SSIS packages, it is an individual process, unless you create a new solution, import them and then upload the solution using BIDS
February 17, 2012 at 2:46 pm
I think i will do a fresh install of 2008 R2; and here will be my steps..Please correct me if I miss out anything.
1) Backup/detach databases from 2005.
2) Script out logins/SQL jobs. =>Any body know sricpt out all the logins and jobs at once?
3) Copy SSIS using your script.
4) Uninstall 2005
5) Install 2008 R2 on Z drive
6) Attach all dbs
7) Copy all logins/SQL jobs (run the script)
8) Upload SSIS
How about the replication jobs?
February 23, 2012 at 7:36 am
Anybody can add on this please? Thanks!
February 23, 2012 at 7:40 am
SQL_Surfer (2/17/2012)
I think i will do a fresh install of 2008 R2; and here will be my steps..Please correct me if I miss out anything.1) Backup/detach databases from 2005.
2) Script out logins/SQL jobs. =>Any body know sricpt out all the logins and jobs at once?
3) Copy SSIS using your script.
4) Uninstall 2005
5) Install 2008 R2 on Z drive
6) Attach all dbs
7) Copy all logins/SQL jobs (run the script)
8) Upload SSIS
How about the replication jobs?
for point 2, take a look at the create user/login commands and see what the inputs are.
you might be able to loop through the syslogins table and generate the create login/user commands needed along with hashed passwords and sids to stop the orphaning of users.
unsure on replication, dont use it myself, but I would say you can script out the publishers subscribers articles in the same way
February 23, 2012 at 7:44 am
Thanks. I found on the MS site sp_helprevlogin stored proc that could script out the logins.
As far as Jobs, I can right click and script those out. But doing so, I can miss some schedules and stuff. Anybody has nicer way of doing this without missing schedule?
What are other thing I need to script out to ensure I don't miss anything on the new install? Configuration, settings, roles, linked servers and how do I get those?
February 24, 2012 at 7:31 am
When I uninstall 2005, all the db files will be in their folder. correct? Because I want to be able to attach those db files after I install 2008.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply