Copy Database 1

[grnzbra]

I have a working SQL Server 2005 database on one machine and need to get it copied (everything - tables, views, SSIS packages, data - everything) to another machine. Can this be done and how?

 

[dhulbert]

Easiest way is to do a full backup of the existing system. Copy the file to the new machine and restore the database onto the new machine.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[grnzbra]

Thank you. I build them and modify them, but this is the first time I have been involved in a move to another machine. The powers that be here want to move it one object at a time - tables, views, stored procedures etc - one at a time.

 

[SQLBill]

No! Tell them to back away from the server. Back up the database, copy it over and restore it. That will take care of almost everything. For SSIS packages, how are they saved? Are they saved with encryption? Are they saved in MSDB or in a file? Are the values hardcoded in the package or do they use config files? That all will determine how they have to be copied over. If they were saved with encryption, the encryption is a hash that included the computer address. The easiest way to copy a SSIS package from one server to another is open it in BIDS and then save it to the new server. You can also create a manifest file and deploy it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875