?
Solved

SQL database migration

Posted on 2015-01-03
7
Medium Priority
?
143 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 80

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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 80

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 80

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

649 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