Solved

How Do I Update from SQL 2005 to SQL 2012?

Posted on 2013-05-14
13
256 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Assisted Solution

by:Giladn
Giladn earned 150 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 150 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:
micen earned 100 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now