More SQL Server 2005 Management Studio Woes

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.

Comments

# Hank said:

Dude, exactly the same issues and gripes, I thought it was the database and gave up. Nice catch, I'll try it when I get into work tomorrow. If anything, migrating a database *should* have gotten easier, not harder. And while I'm at it, what ever happened to the demo VS2005 I saw in the presentations that launched in a milisecond or two? Mine takes almost a full minute, on any machine, even my wookie development box at work.

Monday, February 13, 2006 7:36 AM
# Jason Haley said:

Monday, February 13, 2006 2:33 PM
# Jayson Knight said:

@Hank: Yeah, a lot of the SSMS GUI is written in managed code...I do realize that MS needs to start dogfooding .Net more and more with the software they release, but SSMS (and the new books online) take absolutely forever to open, to the point where I dread doing anything with them. Did that snippet end up working for you?

Tuesday, February 14, 2006 1:31 AM
# Euan Garden said:

This is from memory, however... In the copy database wizard there is a screen with at least 2 radio controls after the 2 connection screens, the lower one is called something like Copy using SQL Management Objects. If you use this I think you will get what you want, this is the 2005 version of the Copy Objects option that appeared as the 3rd option (after tables and views) in the DTS wizard in SQL Server 2000. -Euan

Tuesday, February 14, 2006 8:24 AM
# Jason said:

I've just found a great problem with SSMS;  Customer had a powercut and the msdb device got borked.  It is *not* possible to open the server in SSMS with a broken msdb.  2000EM used to open and show the database as 'suspect'.  This no longer happens. Great!

As somebody pointed out before, the old 2000 tools still work, so I fired up EM and carried on fixing.  Would have been a bind without the old (working) tools though...

++ Variables on a _CI database are case sensitive.  They never used to be.  One of the products we look after is full of shonky stored procs that suddenly no longer work.

++ Most of our apps are now running 25-100% slower on the same hardware with the same memory.  Having migrated through 4.1, 6.0, 6.5, 7.0 and 2000 this is the first release that has been slower than the version being replaced.

.NET my arse.

Friday, March 3, 2006 7:33 PM
# Erich said:

You think that lack of database exporting is an oversight or bug?  I'm guessing that they want to deter people from converting their SQL Server databases to something else, what with open source products maturing.  They probably bet that the number of people malaffected by removing this feature is far less than the number of customers they can keep.

Saturday, September 9, 2006 12:12 PM
# Erich said:

Besides, it would have to be quite the coincidence for all of those methods not to work.

Saturday, September 9, 2006 12:14 PM