Tuesday, May 17, 2011

Backing up an MS SQL 2008 Database with Metadata Intact

In a strange time machine recently which has taken me back to ASP (which still feels higher GL than reference counting in Objective-C, but whatever), I'm dealing with a shopping cart vendor on that platform. I explain that I have the entire system (website) copied over, but two functions aren't working. When I describe the symptoms, he writes, "Based on your comments I am about 99% sure the issue was the database transfer. The primary keys were probably dropped." So he writes, check the docs, which say, essentially, get a DBA. Ironically, they were the same docs I was following when I decided not to call a DBA. Primary keys!? Oh yeah, right. 

So how do you transfer a Microsoft SQL 2008 DB with Enterprise Manager and the kind of permissions they give you on a shared cheapo server somewhere like Winhost?

There are many false paths. First, do not do what I did, which drops the primary keys and any relevant meta-information about the DB:

Do not try to backup or mirror, as both of these are dead ends due to permissions issues:

You have to script it. But do NOT use this!

Instead you have to use:

It's mostly intuitive, but use these options:

And then you have a query which you can execute as if you had typed it by hand.

Update: You should set "If not exists" to Yes and "indexes" to "Yes" too.

No comments: