Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How Do I Update from SQL 2005 to SQL 2012?

Posted on 2013-05-14
13
Medium Priority
?
270 Views
Last Modified: 2013-05-29
Hey everyone.

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

Thank you
0
Comment
Question by:IDMA
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39164746
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
 
LVL 11

Expert Comment

by:Giladn
ID: 39164751
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
 

Author Comment

by:IDMA
ID: 39164763
Correct..  Sorry.  Yes, this is going to be a brand new server.  Either 2008 R2 or 2012
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Expert Comment

by:Giladn
ID: 39164827
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
 

Author Comment

by:IDMA
ID: 39164839
Giladn,

That's right.  I stand corrected, again.
0
 
LVL 11

Expert Comment

by:Giladn
ID: 39164947
what is the porpose of this sql server? how many  DBs? is it being used with a 3rd party application?
0
 
LVL 11

Assisted Solution

by:Giladn
Giladn earned 450 total points
ID: 39165026
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
 

Author Comment

by:IDMA
ID: 39165027
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
 

Author Comment

by:IDMA
ID: 39165033
Also, does this apply to SQL 2012, too?
0
 
LVL 11

Assisted Solution

by:Giladn
Giladn earned 450 total points
ID: 39165059
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
 
LVL 11

Expert Comment

by:Giladn
ID: 39165067
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
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39165238
I would really encourage you to look into the guts of the migration options...  They all have their strengths and weaknesses.
0
 
LVL 1

Accepted Solution

by:
Micael Carlsson earned 300 total points
ID: 39166375
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question