I posted earlier about some SQL Server 2005 Management Studio (SSMS) gripes I've come across; I recently came across another really big one that I really feel the need to air out:  It's next to impossible to export an entire database to a new database on the same machine via the SSMS GUI.  Sure, the option is there (right click a db, tasks, export data), and the wizard is pretty similar to the one we had via SQL Server 2000's Enterprise Manager, except for one glaring "feature":  The only objects you can export from the existing database are tables/views, and their associated data. 

Want your sprocs/functions/roles exported as well?  Ain't happenin'...at least not with this wizard.  Copy database wizard?  Only supported for moving databases between instances, no intra-server copying allowed.  Script the entire database to a new query window, create a new blank db, run the original db's create script, and then try to import the data via the wizard?  Nope.  The wizard fails immediately stating that the table objects already exist in the target database (shouldn't it generate DROP/CREATE statements?  Apparently not.); this is because the script database wizard allows for either CREATE or DROP statements exclusively (it's right there in the options).  Ok, how about backing up the original database, then attempting to restore the backup set to the target?  Uh uh, the wizard states that the target database is in use.  Take the target database offline to attempt the backup restore again?  Again I got an error saying the target db was unavailable.  Wash, rinse, repeat a few times for good measure, throw your hands up in frustration, and simply walk away before someone gets hurt?  Roger that.

This is a pretty major defect in this humble developers opinion...if this kind of functionality existed in previous versions of SQL Server management tools, why strip it out in the new version?  This is a very common task, especially in the development realm where database versioning is crucial.  Of course this can be done (somewhat) easily via raw SQL statements (which I will show at the end of this post), and all the hardcore OSQL/SQLCMD folks will probably shake their head at anyone using the GUI at all, but the point is that this was in Enterprise Manager and was taken out of SSMS.  A huge no-no.  The mere fact that apparently this can't be done from the GUI is pretty bad.  I'd love to be proven wrong though.

Anywho, after some digging around through the documentation (which takes more than a minute to open up on my machine by the way), I found what I was looking for which does exactly what I wanted:  An exact copy of the original database bit for bit.  Here's the code:

BACKUP DATABASE [original_db]
   TO DISK = '[pathToBackupLocation]\[original_db].bak'

RESTORE FILELISTONLY
   FROM DISK = '[pathToBackupLocation]\[original_db].bak'

RESTORE DATABASE [target_db]
   FROM DISK = '[pathToBackupLocation]\[original_db].bak'
   WITH MOVE '[original_db]_Data' TO '[pathToSqlServerDataLocation]\[target_db]_data.mdf',
   MOVE '[original_db]_Log' TO '[pathToSqlServerDataLocation]\[target_db]_log.ldf'
GO

SSMS will even automatically attach the database as well, so you're off to the races immediately after it's done.  It is very much worth mentioning I'm not adverse to this approach at all; my gripe is that the import/export wizard in SSMS is largely a piece of junk...a database is more than just tables and data.  So Microsoft, if you're reading this, here's yet another thing to put on your list for service pack 1.  Who knows what I'll find next.

Share this post: | | | | |