How Do I Update from SQL 2005 to SQL 2012?

Hey everyone.

Well, the title says it all.  Any articles, tips, etc that you have would be appreciated.

Thank you
IDMAAsked:
Who is Participating?
 
Micael CarlssonConnect With a Mentor DBA ConsultantCommented:
Hi.

I have gone from sql 2005 to sql 2008 r2 many times and the same will work up to sql 2012.

1 script out the sql accounts so you could reuse them in the new sql version with http://support.microsoft.com/kb/246133 using the sp_help_revlogin stored procedure

2 check eventual db links and script these out if there are any (not in thiscase with ePO db but could be in future in other cases), script also out eventual sql jobs under sql server agent that you will need on the new server

3 take full  backups of the databases in case of

4 drop all databases on the sql 2005 server with detach database in sql server management studio

5 here you could uninstall sql 2005 and install sql 2008R2/2012 on the same server och if it is to Another new server you will use that one

6 copy the deattached databases to the new sql 2008R2/2012 server and attach them, alternativly you could use the restored dbs above and restore the .bak files

7 run the scripts to restore the sql account that you scripted out with sp_help_revlogin stored procedure  above

8 restore eventual database link with script taken out above

9 restore sql jobs which you saved from old server above

10 check if you want to change the sql compability leveel from 90 to higher at the databases moved under properties options on db

done , have done this many times and have nice instructions if needed.

Best regards

Micael Carlsson
0
 
didnthaveanameCommented:
Unless your underlying OS is Windows Server 2008, you're looking at having to migrate your database to a completely new server.  Take a look at my post: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28121746.html#a39148501.  All of that information is going to be relevant as you're looking at a migration scenario, not a strict upgrade.  Hope it helps!
0
 
GiladnCommented:
you mean UPGRADE .. :)

all is written here, quoting is unneccesary
http://msdn.microsoft.com/en-us/library/bb677622.aspx

post back if you have any questions..

G
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
IDMAAuthor Commented:
Correct..  Sorry.  Yes, this is going to be a brand new server.  Either 2008 R2 or 2012
0
 
GiladnCommented:
great, so you have a new server and you want to MIGRATE data from the old server to a new server including users, DBs and settings? then it is a MIGRATION , not and upgrade or update.
an I correct?
0
 
IDMAAuthor Commented:
Giladn,

That's right.  I stand corrected, again.
0
 
GiladnCommented:
what is the porpose of this sql server? how many  DBs? is it being used with a 3rd party application?
0
 
GiladnConnect With a Mentor Commented:
OK  IDMA ,try this:

1. make sure source server and destination server are fully updated inc sql service pack and os service packs.

2. take full system backup, or at least sql folder+ sql databases.

3. see here to avoid common mistakes:
    http://thomaslarock.com/2011/12/7-mistakes-you-cant-afford-to-make-when-upgrading-to-sql-2012/

4. install  SQL 2008 version on the destination server.


Done?
Great, now you are left with a fresh SQL server 2008 and
you need to move data from the old SQL 2005 server:


moving databases:
http://www.packtpub.com/article/moving-a-database-from-sql-server-2005-to-sql-server-2008-in-three-steps


http://blog.sqlauthority.com/2011/05/27/sql-server-copy-database-from-instance-to-another-instance-copy-paste-in-sql-server/


if you are having problems with security objects after/before moving databases:
http://johnsterrett.com/2012/01/24/8-steps-to-moving-database-users-from-sql-2005-to-sql-2008/
0
 
IDMAAuthor Commented:
It will be holding our McAfee ePO, which we are upgrading, too, and our ticketing database.  There might be plans for it to host our accounting software database (Sage-Timberline), too, but that's not an absolute yet.
0
 
IDMAAuthor Commented:
Also, does this apply to SQL 2012, too?
0
 
GiladnConnect With a Mentor Commented:
Great, I am using ePO 5 now and had the same thing while upgrading from ePO 4.5 because version 5 does need 2008 x64 OS. so you will be installing ePO and SQL on the same machine  right?
after moving  databases you will need to choose database to use, select the database imported/copied from the old server and you should be fine.

if Epo app is installed on a server other then the sql 2005 server you will be demoting then
you may have to change settings for the sql server in your ePO but i'm pretty sure adding a CNAME record in dns will do the trick.

G
0
 
GiladnCommented:
P.S

haven't done this myself but this should apply to sql 2012 as well , SSIS (used to copy DBs) package should do the work.
0
 
didnthaveanameCommented:
I would really encourage you to look into the guts of the migration options...  They all have their strengths and weaknesses.
0
All Courses

From novice to tech pro — start learning today.