Solved

SQL database migration

Posted on 2015-01-03
7
122 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 76

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 76

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 76

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now