Create databases snapshot from executionblock

PeterHageus

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

Michelle T

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?

PeterHageus

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.

Michelle T

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.

PeterHageus

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).

Michelle T

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.

PeterHageus

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

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.