Solved

SQL database migration

Posted on 2015-01-03
7
132 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 250 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 10

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 78

Assisted Solution

by:arnold
arnold earned 250 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 78

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 42

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 78

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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