Solved

How Do I Update from SQL 2005 to SQL 2012?

Posted on 2013-05-14
13
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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