Data Backups

Archived Topics
1

This is kind of related to FPMI I guess, so I’ll post it here.

Let’s say I am doing some development on a project. I’ve added my database to the customer’s server (SQLServer 2005) and they started adding some data. In the meantime, I’ve made big changes to the the database on my dev machine, and need to restore it on the customer’s machine. What is the preferred method of retaining their data?

For instance, they have one table with a bunch of networking parameters. Befored I updated the database, I exported their table to a flat file via DTS, restored the database, and then imported the flat file back in. Is that the best way? Or would I better off writing a sql query that exported the data, and then another sql query that inserted the data back in? (using *.sql files)

Do you see what I mean? I’m just looking for a good long term approach for maintaining my data, and would like to automate it as much as possible.

2

Indeed, database schema and data migration is not a trivial task. This is why people hire DBAs and why software like this exists.

It is common to hand-write migration scripts when doing development, which is basically what you’re doing now.

If someone out there has a better, way, I’m all ears.

3

Hey, thanks for that link. I’m going to try it out. It beats hiring a DBA.

4

Let me know how it works - I’ve never used it, it was just the result of a quick Google search for sql server schema migration.

5

Try the Microsoft SQL Server Database Publishing Wizard. It makes porting of your schema AND data a piece of cake.

Here’s the link: http://www.microsoft.com/downloadS/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

6

Thanks Mickey. I gave the publisher a try and it looks like it will do what I need. I’ll have to play around with it some more.

I also started playing around with the teratrax software, and that’s nice as well. I exported a table in xml format and brought it back in on another database, and it worked perfectly. This, as well as the Publisher, were more effective than the DTS tool I was using.

7

+1 for the forum! I love it when I learn new things on our own forum… thanks MickeyBob.