How to Package Data Only

cklaw

I have two databases with different data in. V2 is in production whilst V3 is in development. I want to update the production database with the new data, but for customer security reasons no computer can see both databases at the same time. Therefore, I need a package.

Fortunately, the new data are in separate tables, that exist in production but which are empty. Therefore, all I need is the ability to package up the data from a dozen tables, transfer the package to the target (I can do that), and run it.

There isn't an obvious way to do this in SQL Packager, although all the key elements are present. Can anyone tell me how I can solve this problem?

I'm using SQL Server 2005 Standard x64.

Thanks.

peter.peart

Hi Charles,

Unfortuantely, if you are creating a database upgrade, you will need to have a copy of the production environment which is accessible to the development environment in order to create the package against production.

You should be fine if you just have a copy of the schema from production, and then use SQL Packager to create an upgrade package based on developement to production, and then only specifying the relevant tables required to be packaged.

You should then be able to run the package on the actual production environment with no issues at all.

One way to get a copy of the schema is to create a snapshot of production using SQL Compare, compare it against a blank database on the development server and then synch it. You should then have a schema matching production that you can use as your target for creating the package against from development.

Does that make sense Charles?

cklaw

Hi Peter, thanks for the reply.

Yes, it does make sense. In fact, though, I arrived at another solution, which is very nearly part of the SQL Packager product, but which requires two passes.

First of all, I created a package to create a new database, in which I selected just the tables I wanted to update the data for. Instead of creating an EXE at this stage, I just saved the script.

I then edited the script and removed the object creation stuff from the beginning; just leaving the millions of INSERT INTO statements.

Finally, I ran the packager again, and elected to package my edited script as an EXE. I took this EXE to the production database and it all just worked.

One of the problems is that the script generated is enormous, so in reality I had to create three scripts, but even still they were large and finding an editor that could open and save a file > 2 Gb was a feat in itself.

Perhaps this an opportunity to make a feature request? It would be nice if there were the option to exclude the database creation stuff in the tool, to avoid the two passes and the script editing horror. Also, some ability to split temporary files over drives, or specifiy somewhere other than drive C: would be good - I kept running out of disk space.

Thanks

Charles

peter.peart

Hi Charles,

Many thanks for your reply.

So, to confirm, you would be looking at potentilly using SQL Packager to just create an INSERT script of data from a database, with the instructions on where to insert into the schema being based on the source databaase, and then package that up as a .EXE file for deployment on the target?

Pete

cklaw

Hi Pete

Yes, I think that is essentially it. We really have two scenarios:

1. We cannot see the source and destination servers at the same time, and want to copy data from one to the other

2. We can see both servers but the volume of data we wish to copy prohibits the use of SQL Data Compare, which we would otherwise use

In both cases, the ability to simpy generate a packaged INSERT script from a source database that we could take to a target and run would make life a whole lot easier, and more reliable.

Regards

Charles

peter.peart

Hi Charles,

If you have set up a blank schema, and then used SQL Data Compare to compare against that using the split transactions option available from going to Tools > Application Options, and then packaged up the INSERT script generated, would this do the job for you?

Or, would you like to see all of this incorporated into SQL Packager?

Pete

cklaw

Hi Pete

Yes, I can see that would do it. In fact, it has the advantage that a WHERE clause can be specified, so that a subset of the data can be extracted and copied to another server.

In terms of whether it might all be part of SQL Packager, I suppose that if one recognises this as more than a very occasional requirement then it would be nice to have it integrated into a single application, such that making appropriate selections in the wizard - including a WHERE clause, as I realised just this morning - resulted in the data being packaged in a single pass.

Regards

Charles