Solved

moving a DB from one physical SQL box to another Virtual SQL box can I use SSIS to do this ?

Posted on 2014-12-19
12
100 Views
Last Modified: 2016-02-15
hi
I need to move a SQL 2008 DB from a physcial to VM can I do this with SSIS ?

If so how ?

thanks
0
Comment
Question by:NAMEWITHELD12
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 251 total points
ID: 40509947
The best way is to create a backup, and restore it on the new server.  Both can be done in SSMS (not SSIS).
0
 
LVL 12

Assisted Solution

by:Ammar Gaffar
Ammar Gaffar earned 63 total points
ID: 40509977
SSIS normally used for transferring/migrating data, but in your case you want to move the whole database from one SQL Server to another (Physical or VM doesn't matter),  You can detach your database from old server, copy database files into your new server and attach it again, this if you can go offline for doing this movement otherwise backup and restore is the best

Good Luck
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 40510044
so you cant use SSIS or is it just easier to use SSMS?

seems like you can , we are going to do the detach and reattach method ,

I have NEVER used SSIS to do this , any other advice? or things to go wrong?



thanks!!!!!!!!!!!!!!!!!!!!!!!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 251 total points
ID: 40510070
SSIS is used for transformations and other complex operations on data you need detailed control of.
Both Backup/Restore and Detach/Attach are done in SSMS.
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 40510077
we have a consultant who whats to use the SSIS to do this , can it be done or should I tell him he MUST use SSMS ?


thanks!!!!!!!!!!!
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 251 total points
ID: 40510088
Must. Never offer options :-)
0
 
LVL 1

Author Comment

by:NAMEWITHELD12
ID: 40510105
ok , but it can be done with SSIS? , I mean I want to let him do it his way if possible.....Thanks

t
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 124 total points
ID: 40510255
<<can I do this with SSIS ?>>
Yes but only if you have full read access on system databases and too much time on your hand to retroengineer the schema information.  Analogy: do you like using  teaspoons to dig holes or do you prefer using instead of a shovel.

SSIS is not the appropriate tool to migrate databases. period.  Use BACKUP/RESTORE or SP_DETACH/SP_ATTACH

<<we have a consultant who whats to use the SSIS to do this , can it be done or should I tell him he MUST use SSMS ?>>
Your consultant either does not know how migrate data or has a non technical reason to have to do it this way. Ask your DBA to restore the database on the new box period.  Also tell your consultant that the DBA needs to be aware of this: this operation can be considered data theft if done on database containing private or confidential information (sorry, that is the law)
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 124 total points
ID: 40510256
<< I mean I want to let him do it his way if possible.....Thanks>>
I would consider this decision with caution.  Imagine a consultant asks you to let him read some data that is not supposed to be read: you become an accomplice of a theft.  Better ask your DBA (if you have one) or your manager first.
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 251 total points
ID: 40510265
With SSIS, each table is created and transferred individually. Indexes and Views are recreated. Security is reapplied. Checks are done a lot.

And you don't get a consistent snapshot of the data, since tables might get changed while others are copied.

Does that sound reasonable for copying a complete DB? No. It is like cloning a harddrive by looking up each bit of every file individually.
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 62 total points
ID: 40512961
ok , but it can be done with SSIS? , I mean I want to let him do it his way if possible.....
Yes, it's possible but as you saw, none of the experts here recommends the SSIS as a tool for database migration.
Why don't you ask the consultant the reasons he wants for using the SSIS instead of a backup/restore or detach/attach method?
0
 
LVL 1

Author Closing Comment

by:NAMEWITHELD12
ID: 40523633
"Analogy: do you like using  teaspoons to dig holes or do you prefer using instead of a shovel."

great quote my friend , classic !
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 61
(sql serv16)ssis 2016 question/check 1 73
RAISERROR WITH NOWAIT 2 18
MS SQL + date 6 23
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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