Page Comparison

Datical's Deployment Packager for SQL Server requires elevated permissions.

...

This approach leverages Datical's baseline to capture the state of the database (establish baseline before you do anything else - this will populate changelog.xml with initial change sets). Then use custom backup/restore script that doesn't use the native SQL Server backup/restore mechanism (and require the associated privileges). This script, as part of restoring the database, performs drop of all objects to clean out the database. By configuring to use "on_demand" backup mode, Deployment Packger will use the changelog instead of the backup file to restore the database.

...

This approach requires the following setup:

• The database must be baselined so that all objects in the database have a corresponding change set in Datical's changelog. The baseline (change sets) serve as backup of the database from which databases will be rebuilt in case restore operation needs to be performed.
• Deployment Packager must be configured with databaseBackupMode=on_demand
• Use the attached groovy script for custom backup/restore method. 
  • Groovy script: BackupRestoreSqlServerNoBackupFile-v5.4.groovy. 
    • This script would need to be added to "extensions" directory, which requires additional property to be set in Deployment Packager configuration: extensionsPath=C://apps//DaticalDB//repl//scripts//extensions
  • As such, Deployment Packager must be configured with databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile

Step-by-Step

1. Create baseline of your REF database. This will import database objects into Datical's changelog
2. Save the groovy script (BackupRestoreSqlServerNoBackupFile-v5.4.groovy) to a path in C://apps//DaticalDB//repl//scripts//extensions
3. Create baseline of your REF database. This will import database objects into Datical's changelog
4. Update deployPackager.properties with these new lines:
   1. databaseBackupMode=on_demand
   2. databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile
   3. extensionsPath=C://apps//DaticalDB//repl//scripts//extensions

Expected Behavior

Datical will execute custom backupFor packaging scripts using CONVERT packaging method (ddl): Deployment Packager will always perform a restore operation.

For packaging scripts using STOREDLOGIC, DATA_DML or DIRECT packaging methods: Deployment Packager may not always perform a restore operation. The restore operation will only be invoked when a failure is encountered during the packaging job.