Moving store database from shared provider to local install

Introduction

Have you ever tried to backup a SQL database on one server and restore it to a local or different server install? Sometimes it works, other times it just melts down and you can't figure out why. You probably missed something I just spent the last 5 hours missing....

WARNING: This is some advanced SQL stuff, make backups and use caution

Schemas

SQL 2005 has a feature called schemas. What is a schema? Don't ask. Suffice it to say, a schema can ruin your day if you're trying to move a database. Let me explain. Have you ever gone into the SQL Studio Express and expanded the tables list for your store database? Have you ever noticed that all the tables start out with something common like "dbo.ac_Payments" or "dbo.ac_StoreSettings"? Yeah, that "dbo." is the schema name. It's not always "dbo" though - that all depends on how the original database and your SQL 2005 permissions were created.

Shared hosting providers don't like to give their customers the DBO permissions. DBO is the full-scale-can-blow-up-everything set of permissions. That would be bad for their other customers, so they almost always create a separate SQL user account for you and your newly-minted database. Once you run the AC7 install script and the tables created, that user account is created as a schema name and assigned to every single AC7 table.

Woe is my database

That's swell you might think, but how does it affect us? Try restoring a backup of your database to another SQL 2005 server. The database restores just great. The user account stored in the database restores as well. But oh wait, you don't have a SQL user account created and mapped to the schema that is assigned to all those tables. Why not? Well, the SQL backup file not only captured all the data but also captured that original schema info. When SQL 2005 restores a database backup of your store, it doesn't restore the main SQL users nor does it re-assign the schema name to something that's valid on the destination server.

In other words, you just restored a bunch of tables assigned to a particular schema to a database server that does not have any knowledge of that schema. Since schema decides permissions, you just broke your local AC7 install - it lacks the ability to access that database. An easy clue is when you try to launch the local site and you get a SQL error about not being able to find ac_Stores. It doesn't make sense - that table is right there! But it's assigned a completely different schema so your existing local SQL account can't access it.

Why not all the time?

An easy question deserves an easy answer. Not all SQL administrators create databases the same way nor set permissions to an exact standard. For instance, I own my own server. When I created my database, it was created with a schema of DBO i.e. the do-all-master schema. I did the same for my local install. So whenever I back up and restore, the same schema exists in both SQL installs and everything links right back up. But if your ISP creates your SQL account with a unique schema (usually named the same as your connection string username), you won't know what that is and will not create it in advance of restoring your downloaded store database to your local install. Ahhh, now wouldn't be cool if there were a way to change all those dozens of tables to a new schema name all at once?

The best IT people are the laziest people You heard me. We hate doing repetitive stuff - especially repeating repetitive stuff. We love automation and the best IT people automate the daylights out of anything they can get their hands on. So, we're going to build a short SQL command that will generate a big list of SQL commands to change the schema for every AC7 table.

The Prerequisites

First, you gotta know the old schema name. That's easy - make a new database, restore the backup file from your store database and expand Tables. Every table will begin with the old schema name. Our example will use "Snoopy" for the old schema name. Second, you need to know the new schema name. Well, we're going to use "dbo" because that'll most likely be what you are using on your local install. Not sure, fine. Just expand another database on the same SQL server and look at the tables - what do they all start with? Told you so

Now fire up the SQL Studio Express and connect to your local SQL install. Open a new query window. Enter the following text but do NOT execute it yet:

use mystoredbname
SELECT 'EXEC sp_changeobjectowner ''Snoopy.' + name + ''', ''dbo'''
FROM sys.tables
WHERE schema_id = SCHEMA_ID('Snoopy');

(note those are all single-quotes, none are double-quotes)

Right-click in the query window, select Results To and choose Results to Text. Now execute the query.

The output of the query will show below and you will see the same command repeated for every table in your database. Now just copy all those commands (don't grab the first or last line), paste it into a new query window and execute it. You'll probably also get a bunch of warnings in the output - ignore them. Warnings? We don't need no stinkin' warnings!

You won't see anything change on the left side of SQL Studio Express until you right-click your database and click "Refresh". What you should see (after you refresh it) is the schema name in front of all the tables now shows "dbo" instead of whatever old schema name you had. Now you can access the tables from your local AC7 install. Fire up your browser and give it a shot. Note that if you aren't running SSL on your local install (few people do), you'll have to edit the ac_StoreSettings table and change the SSL Enabled parameter from True to False.

Conclusion

SQL 2005 has some really sophisticated features. Unfortunately most of us rarely need them. Nor do we care to have them waste our Friday evening overcoming the unnecessary complexity inherent to sophisticated features. However, now you can save your Friday evening and quite possibly your sanity