Check out our new training course on AngularJS for Flex Developers

Moving a database from one server to another

One of my clients is moving their site to a new host. As their web developer, I'm doing most of the work (As it should be).

It's easy to ftp down (and up) the HTML files or CFM templates, but moving the database is a bit tricky.

In an ideal world I could get a database backup from the current host and have it restored at the new host. Unfortunately, the current host is unresponsive to phone calls, e-mails, and Instant Messages.

It is worth nothing that it is this un-responsiveness that prompted the search for a new host. They aren't being difficult because we're moving. We're moving because they were being difficult.

So, how do you move a bunch of data from one SQL Server database to another SQL Server database? You could use DTS. I've used it before and it is acceptable approach. But, I think I found something better.

I'm using ApexSQL Diff, which is a sync tool for databases. It can work on both structure and data. First, I used the tool to move the table structure. Then I created a data sync file for each table. Since the new database is completely empty, the data sync file contains a ton of insert statements.

The problem I'm having is that a ton of data inserts will inflate the transaction log file and prevent you from inserting any more data. So, I've only finished half the job. I got on the phone w/ tech support at the new host and asked them to bump up the log file size.

The tech support rep told suggested that I move less data. And he said the transaction log has ~7 megs free which is plenty of space. I told him I'm getting this error, and usually it is because the transaction log does not have enough space. He suggested moving data in smaller chunks, which I suppose is valid. But, I really don't want to spread this process out over the next week.

Eventually he escalated my request and said someone would get back to me.

I've run into these type of "transaction log" problems before, but don't have an ideal solution for it.

How do you handle this?

Alternatives to Database Recursion

Sean Corfield recently posted an entry about recursionin the database. This got me thinking. I once had a teacher who was adamantly against recursion for performance reasons, saying that Towers of Hanaoi was the only example where recursion was beneficial from a performance standpoint.

I have been unable to prove him wrong; can anyone else do so?

As such, I've always been cautious of recursion.

I recently spoke to a client about an application with a similar structure (to what Sean describes in his post). It was storing part information in a self-referencing table. (One part can be made to build other parts, etc.. ). It turned out that this application was a drag, because in some cases it was recursing up to 300 times to get all the data in the 'recursive tree'. This is a situation where recursion was just not working. We spoke, and I suggested this type of solution.

For simplicity, I'll use a table like this:


RecurisveExample(PKID, Data, RecurseOrder)

PKID is the primary key. Data is whatever data we are storing (whether it is part numbers or cat pedigree) RecurseOrder is a text string

For each element in the table, you give it a 3-character identifier (In my example, I'll use 3 characters, but the identifier can actually be 4 or more). The RecurseOrder field contains 'Parent's identifier' + 'Current identifier'

So, to put some data in this table:
1 Root 1 001
2 Root 2 002
3 Root 3 003
4Child 1.1 001001
5 Child 1.2 001002
6 Child 1.3 001003
7 Child 1.4 001004
8 Child 2.1 002001
9 Child 2.2 002002
10 Child 2.3 002003
11 Child 2.3.1 002003001
12 Child 2.3.2 002003002
13 Child 2.3.2.1 002003002001
14 Child 2.3.2.2 002003002002
15 Child 2.2.1 002002001
16 Child 2.2.2 002002002

The root is assigned '001', '002', '003'. The children of root 1 are assigned '001001','001002', '001003'. The children of child 1 are assigned '001001001', '001001002', '001001003', and so on.

Given any root, you can get the tree in recursive order just by sorting on the recurseorder column. To get the recurseorder on root 2, for example, you can do this:


select * from recursiveexample
where recurseorder like '002%'
order by recurseorder

To get the tree, starting at the third child of root 2, you can do this:


select * from recursiveexample
where recurseorder like '002003%'
order by recurseorder

In some cases, such as discussion groups (where you want to retrieve a whole thread in post order) I have found this to be an elegant solution.

With a three-character identifier, you are limited to 1000 elements before you run out of thread strings. But you use a larger identifier string if you need more.

All Content Copyright 2005, 2006, 2007, 2008, 2009 Jeffry Houser. May not be reused without permission
BlogCFC was created by Raymond Camden. This blog is running version 5.9.2.002.

pure garcinia cambogia

payday loans

seo services

seo services