Database Initialization

ktersius

Hi

I need to run a script that can get the database initialized with certain data that cannot be linked as static data. What would be the best way to get this init script going and how should I use deployment manager to run it?

Also this script would only need to run once. It does not need to run if the database is already initialized. What would be the best strategy to accomplish this?

Regards

Brian Donahue

Hello,

My instinct would be to simulate linking static data by editing the database package, changing redgatedatabaseinfo.xml so that tables are linked, then you could use SQL Data Compare to compare the database to a blank database with just the table definition in it for each table and save a synchronization script in the data subfolder of the package, one file for each table, save it all back in, and recreate the package.

For example, linked static data appears in the DataFileSet element of RedGateDatabaseInfo.xml, like this:

<DataFileSet>
<Count>1</Count>
<DataFile>dbo.Users_Data.sql</DataFile>
</DataFileSet>

Then if you have a file called dbo.Users_Data.sql in the Data subfolder of the package and a table schema called Users, the inserts in the Users_Data script should be run.

I haven't tried this so if you want to give it a test and let us know?

chirayu

Hi

You can also use Powershell scripts with Deployment Manager to initialise your database.

If there is a 'postDeploy.ps1' script in the database package that you are deploying, that script will get run post deployment. The powershell script can then poke the database to check if it's already initialised.

Links to documentation:
- Creating database packages in TeamCity or in MSBuild
- Working with database packages in Deployment Manager

Thanks!

Chirayu