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?