Red Gate forums :: View topic - How to Package Data Only
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

How to Package Data Only

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
cklaw



Joined: 22 Sep 2009
Posts: 12

PostPosted: Tue Nov 17, 2009 1:26 am    Post subject: How to Package Data Only Reply with quote

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.
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Wed Nov 18, 2009 6:03 pm    Post subject: Reply with quote

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?
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
Back to top
View user's profile Send private message Send e-mail
cklaw



Joined: 22 Sep 2009
Posts: 12

PostPosted: Wed Nov 18, 2009 7:18 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Thu Nov 19, 2009 1:46 pm    Post subject: Reply with quote

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
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
Back to top
View user's profile Send private message Send e-mail
cklaw



Joined: 22 Sep 2009
Posts: 12

PostPosted: Thu Nov 19, 2009 9:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Fri Nov 20, 2009 2:25 pm    Post subject: Reply with quote

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
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
Back to top
View user's profile Send private message Send e-mail
cklaw



Joined: 22 Sep 2009
Posts: 12

PostPosted: Fri Nov 20, 2009 4:41 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group