The Business Intelligence Blog

December 20, 2009

Import Export vs Copy Database Wizard in SQL Server

Filed under: SQL Server Database — Tags: , , , — Shahfaisal @ 1:26 pm

Often times, I have seen people confused over the use of Import and Export Wizard. So I thought blogging about it wouldn’t be a bad idea. Many developers think that Import and Export Wizard can be used to copy/move database from one instance of SQL server to another. Well the answer is “yes” and “no”. You can copy database tables using import and export wizard, but you can’t copy all the database objects such as indexes, keys etc. Also, while running the Wizard, if you select a view from the source database, that view will be converted to a table in the destination database. In short, import and export wizard can be used just to import/export data to/from SQL server or other data sources, it can’t be used to copy the “entire” database.

The Copy Database Wizard lets you move/copy database and all its objects such as indexes, keys etc. from one instance to another. One of the very nice things about copy database Wizard is that it allows you to select related objects external to the database such as logins, sql agent jobs, stored procedures from master database etc as shown in the figure below which you wont be able to when using backup/restore or import export wizard for that matter. Moreover, this wizard lets you select multiple databases to copy which is not possible using import export wizard.

Now you might be thinking – Can we use Copy Database Wizard to upgrade from SQL Server 2005 database to SQL Server 2008?
Yes, you can. In fact, its far better than using backup/restore method in my opinion.

Advertisements

1 Comment »

  1. […] is the difference between Import Export Wizard and Copy Database Wizard? Please refer Import Export vs Copy Database Wizard in SQL Server for more […]

    Pingback by SQL Server Database Administration – FAQs « The Business Intelligence Blog — January 16, 2010 @ 11:29 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: