The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P76: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | ETL Techniques Chapter 8 357 Creating packages programmatically is way beyond the scope of this book so we won t be talking about that option at all. Just know that it is possible if you need it. We will look at the two wizards however and take a brief look at using BIDS to work with packages. Let s start by looking at the Copy Database Wizard. Using the Copy Database Wizard The Copy Database Wizard gives SQL administrators an easy way to copy an entire database between two SQL Server instances. The obvious goal of the Copy Database Wizard is to make a copy of an entire database. Normally this is going to be between two instances of SQL Server however you can use it to copy a database within a single instance. You can think of a database two different ways physically or logically. Physically a database is a collection .ndf and .ldf files on disk. Logically a database is made up of a bunch of objects like tables indices views procedures data the rows in the tables and metadata object definitions permissions and so on . Given those two possible views of a database you then have two different models for copying a database. You could copy .ndf and .ldf files that make up the physical database. Alternatively you could create new empty files on the destination server and logically copy the database by running statements to create copies of every object in the target database copying the data into those objects and then copying all the metadata. The Copy Database Wizard supports both models. It performs physical copies through the detach-and-attach method. It performs logical copies via the SQL Management Object method. The detach-and-attach method is the most efficient method of copying an entire database. This method physically copies the entire database by detaching the database from the source instance copying the .mdf .ndf and .ldf files to the destination and then attaching them to the destination SQL Server instance. It does have some constraints .
