Moving SQL Server databases
I spent a lot of last night and this morning moving an SQL Server database from one computer to another. This is much more complex than it needs to be.
I'm using Rainbow as a portal engine, and I'm using the yetanotherforum.net forum engine and a few other pieces of software. I set up SQL Server a long time ago and I decided to move the database to a different computer.
Here are some of the issues, and the solutions I found:
- When you install MSDE, it defaults to not having any network connectivity. I guess this is a good thing, except when you're trying to connect to it over a network. No problem, run the “srvnetcn.exe” tool that comes with MSDE and enable the protocols you like.
- I tried to use the “Copy Database Wizard” but had zero luck with it. It would lock up and not actually do anything. Probably something misconfigured, but still, it didn't help me find out what was wrong. I never did end up getting it to work.
I ended up using Backup and Restore to get the job done. Backup is simple enough - the SQL Server Enterprise Manager makes it really easy to backup and restore databases, so I asked it to backup to a file, and it did. I copied the file over to the other computer, and tried to restore. This led to:
- When you make a backup of a database, the file location where the database originally lived is apparently stored in the backup. Trying to restore using the Enterprise Manager led to errors that the physical path to the database files that worked on the original server didn't work on the new server.
To get around this I had to manaually execute the SQL to restore my database. Here's what it ended up being:
RESTORE DATABASE Rainbow
FROM DISK = 'e:\Rainbow.bkp'
WITH RECOVERY,
MOVE 'Rainbow' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Rainbow.mdf',
MOVE 'Rainbow_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Rainbow_log.ldf', file = 2
The MOVE clauses are what tells it the physical location for the files in the backup.
The 'file = 2' says that the backup to be restored is the 2nd backup in the file. The first time I did this I assumed it would restore the most recent backup in the file, but no, it defaults to restoring the first backup it finds, so I had a nice working copy of my site from some time ago. Doh!
I had another problem that ended up being related to permissions - some tables belonged to the wrong owner. This caused Rainbow to start up and connect to the database, but decide it needed to be set up. To get around this, I found an SQL script that sets the owner of every user object to 'dbo'. Very useful. You can find it here. Most of the problems I've had moving databases around with SQL Server end up being related to permissions, so this is a very useful script to have around.
I've moved about half my databases to the other computer so far; it's painful because once I've moved it, I have to hunt down connection strings in the various applications that connect to the databases, but I'm getting there.