Solved

SQL database migration

Posted on 2015-01-03
7
126 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
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 77

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 77

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:EugeneZ
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 77

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 36
How to share SSIS Package? 6 37
SQL View nearest date 5 36
Merge two rows in SQL 4 13
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

809 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