Monday, June 14, 2010

The Latest Reason I Hate SQL Server

When is a copy of a database not a copy of a database? When it was made using SQL Server's "copy database" feature, that's when.

Having run into this before I should have known better, but I needed to make a copy of a production database for testing purposes. Normally I'd take a backup and restore it to a different database, but I noticed SQL 2005 has a "copy database" function I hadn't seen before. So I figured I'd give it a try.

Totally and utterly pointless. It looks like it copies the database until you start looking at all the fields using auto-increment IDs in your old database that magically aren't still auto-increment IDs in the "copy" of the database.

Thanks for another worthless feature Microsoft. I only use SQL Server under duress anyway, but why can't they get even such basic things like "copy database" right? Inexcusable.

4 comments:

danlance said...

Yeah - that is sooo annoying - the Copy DB functionality is completely useless.To be fair it's not SQL Server its self which is at fault - but rather the client programme "SQL Server Management Studio", which, whilst much prettier than the Enterprise Manager / Query Analyser tools it replaced, has a substantial number of gotchas (i.e. stuff that plainly does not work)Still, despite all that, the tools do seem much more polished / easy to use than the out the box tools you get with other RDBMS...

Matthew Woodward said...

I'll take "works" over "polished" every time. MySQL dump / restore may not be pretty but it gets the job done, which is more than I can say for many of the functions in SQL Server.As for blaming SQL Server vs. the SQL Server Management Studio client, I just heard a hair split. ;-) The database and tool are both supplied by MS, so I'm not sure that's a valid distinction. The "ignore the autoincrement/identity stuff" has been an issue with various means of copying data around since at LEAST SQL Server 7. If they have their reasons they're lost on the users (or at least this user).

gamesover said...

The "import wizard" seems to lose default and key fields too. When I want to create a "copy", I detach the database, copy it, rename the file and reattach it with a new name. This prevents keys, indexes, etc from getting lost.

Matthew Woodward said...

Yep, backup/restore or the method you mention are the only way to have it work consistently. Why even have other means available if they don't work.