?
Solved

SQL database migration

Posted on 2015-01-03
7
Medium Priority
?
139 Views
Last Modified: 2015-01-07
Hi all

I have a 2008 sql database on server 2003, I want to migrate it to SQL 2012 on server 2014, how straight forward/complex is this process, would a back up from original and restore to new instance work? Not to familiar with sql setups

Thanks in advance
0
Comment
Question by:techsolve1
[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
7 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1000 total points
ID: 40529507
1. Use the Upgrade Advisor for SQL Server 2012 to see if there any problems - see http://technet.microsoft.com/en-us/library/ms144256(v=sql.110).aspx

2. see this article - http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28178294.html. Note especially Razmus' comments.

3. See this article: http://technet.microsoft.com/en-us/library/ms188664(v=sql.110).aspx

4. Test, test and test.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40529658

1. Steps

Set up Sql server 2012 on new server

2. Steps

Copy the .mdf/.ldf files from old server into new server

3. Steps

Create the same user/schema, to refer to these .mdf/.ldf schema files, on Sql server 2012
0
 
LVL 79

Assisted Solution

by:arnold
arnold earned 1000 total points
ID: 40529851
I find It rather a poor suggestion to copy mdf/LDF files which requires that the user stop ms sql from running which if one recommends such a transaction should include the requirement that the DB not be running at the time. The size of the database can also be a consideration.

The backup/restore is the way to go following the running of the suggested troubleshooting Phillip mentioned.

Before migrating the DB, you should first make sure the applications that rely on the database actually support sql 2012. Deals with whether they have the requisite sql client library ifyou would need to install the sql server 2012 sql client library.

Transferring the logins Should be done, if needed ms has such references, a search for sql transfer logins will list several such examples.  I'll post a link later on.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 79

Expert Comment

by:arnold
ID: 40529854
http://www.fwbuilder.org

This way when you restore the DB, all permissions/logins will be as they should provided the sql check suggested by Phillip does not raise issue I.e. Functions/SPs that use unsupported ...........in sql 2012 while we're fine insql2008.

The applications that rely if they support sql 20012, you should not have issues with the transition.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 40530279
as per above post you need to check (Upgrade Advisor)  what can be issues for this move
and your abilities (time & $$) for these changes

for example : there is no  DB compatibility level 80 (sql 2000) in sql 2012+
check your DB compatibility level

-----
use yourDBname
SELECT compatibility_level
FROM sys.databases WHERE name = 'yourDBname';
GO
----
0
 
LVL 79

Expert Comment

by:arnold
ID: 40530307
The login transfer info can be found at http://support.microsoft.com/kb/918992

Did not notice that I pasted the wrong url in a prior post.
0
 

Author Closing Comment

by:techsolve1
ID: 40535499
Thanks Guys

Upgrade advisor and back up and restore worked for me
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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