Create databases snapshot from executionblock

Forum for users of SQL Toolkit 3,4,and 5

Create databases snapshot from executionblock

Postby PeterHageus » Wed Apr 16, 2008 10:21 am

Hi. I got a schema and a data executionblock that I wan't to persist as a database snapshot. Whats the best way to do this? I'd rather not script it into a livedatabase.

TIA, Peter
PeterHageus
 
Posts: 9
Joined: Wed Apr 16, 2008 10:17 am

Postby Michelle Taylor » Wed Apr 16, 2008 11:46 am

Once it's an ExecutionBlock, there's no straightforwards way to turn it into a snapshot. For the schema component, assuming this is an ExecutionBlock which does have all the information in that you need to create an entire database (i.e. it was produced by synchronizing a database to an empty database), you should be able to do the following:

1) Save the ExecutionBlock as a file (with the SaveToFile method) in its own folder
2) Register that folder as a script database
3) Save that script database as a database snapshot

I don't guarantee that this will always work, but it's probably your best option if you don't want to script it into a live database.

You won't get the data component that way though, because we haven't implemented read-from-scripts for data yet.

How did you end up with an ExecutionBlock that you want to persist as a database snapshot? Maybe you could save something from earlier in the process that would be easier to turn into a snapshot?
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Postby PeterHageus » Wed Apr 16, 2008 12:55 pm

Thanks for fast answer.

The reason I ended up with executionblocks is that I haven't found a way to filter a Database.SaveToDisk.

I want the entire schema of the database, but only data from a subset of the tables in my snapshot. Since one of the tables contains rather many rows, I figured a snapshot was the best way to persist it.
PeterHageus
 
Posts: 9
Joined: Wed Apr 16, 2008 10:17 am

Postby Michelle Taylor » Wed Apr 16, 2008 2:58 pm

What are you going to use the snapshot for? I can't find any way to get a snapshot where only some of the tables have their data with them without involving a live database, but you can get a SQL Packager package in that condition.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Postby PeterHageus » Wed Apr 16, 2008 3:03 pm

The snapshot will be used to update customer databases. They can be any version, and we don't have remote access to them, and I want to keep the update-package minimal in size. So a snapshot would be ideal.

The snapshot will be created from a live db, but I don't want all the data in it (and I don't want to maintain a separate 'empty' model-db).
PeterHageus
 
Posts: 9
Joined: Wed Apr 16, 2008 10:17 am

Postby Michelle Taylor » Wed Apr 16, 2008 3:19 pm

The easiest way to do what you want is to make a SQL Packager package instead of a snapshot. See http://help.red-gate.com/help/SQLPackag ... frame.html for more details about the SQL Packager API.

Whilst we currently only have a v5 of the SQL Packager API (which means that it only works with v5 of the Compare and Data Compare APIs), we're planning to update it for SQL Server 2008 support for the release of SQL Server 2008, which will include updating it to work with the latest SQL Compare and Data Compare engines.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Postby PeterHageus » Wed Apr 16, 2008 3:28 pm

But from what I understand I have to compare against the actual update-target to create an update-package?

Or can I make Compare/Data Compare work against the output of Packager somehow? As far as I could see I could only save as .exe or project.
PeterHageus
 
Posts: 9
Joined: Wed Apr 16, 2008 10:17 am

Postby PeterHageus » Thu Apr 17, 2008 10:37 am

Solved it by programatically creating a temporary live model db, and then made snapshot from that Not the most elegant solution though, since there are user managent and security issues etc.

I think the most logical and clean solution would be if Packager could output a database snapshot.
PeterHageus
 
Posts: 9
Joined: Wed Apr 16, 2008 10:17 am


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests