Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

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

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
NAMEWITHELD12
Asked:
NAMEWITHELD12
  • 4
  • 4
  • 2
  • +2
8 Solutions
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The best way is to create a backup, and restore it on the new server.  Both can be done in SSMS (not SSIS).
0
 
Ammar GaffarSoftware EngineerCommented:
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
 
NAMEWITHELD12Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
NAMEWITHELD12Author Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Must. Never offer options :-)
0
 
NAMEWITHELD12Author Commented:
ok , but it can be done with SSIS? , I mean I want to let him do it his way if possible.....Thanks

t
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<< 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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
NAMEWITHELD12Author Commented:
"Analogy: do you like using  teaspoons to dig holes or do you prefer using instead of a shovel."

great quote my friend , classic !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now