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.
No comments:
Post a Comment