Page Comparison

Datical's Deployment Packager for SQL Server requires elevated permissions as documented here: /wiki/spaces/DDOC59/pages/795738600SQL Server Roles and Permissions for Datical DB. These roles inclue:

• dbcreator - server-level permission
• db_backupoperator - database-level permission

...

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 Packager will use the changelog instead of the backup file to restore the database.

...

1. Save the groovy script (BackupRestoreSqlServerNoBackupFile-v5.4.groovy) to a path within Datical install directory, e.g., C://apps//DaticalDB//repl//scripts//extensions.
   1. Rename the file to BackupRestoreSqlServerNoBackupFile.groovy
   2. The script performs the following operations:
      1. Backup: No backup is taken. This is configured in Step 3 where Datical project is configured for "on_demand" backup mode.
      2. Restore: Performs a drop of all objects by executing SQL code (built into the groovy script) and then restores/rebuilds the database from change sets in the Datical project. 
2. Provide a mechanism to build REF DB using one of the following two approaches:
   1. If the REF DB is already populated, then create a baseline of your REF database using the command: hammer groovy project_baseline.groovy
   2. If the REF DB is a blank database, then DBAs will need to provide a DDL script of all objects from production instance. This script will need to be added to sql_direct/ref_only folder containing custom metadata.properties file that mentions contexts=REF_ONLY
3. Update deployPackager.properties with these new lines:
   1. databaseBackupMode=on_demand
   2. databaseBackupRestoreMethod=BackupRestoreSqlServerNoBackupFile
   3. extensionsPath=C://apps//DaticalDB//repl//scripts//extensions
4. Ensure that REF DB has context set to REF_ONLY. This will allow load script from sql_direct/REF_ONLY 

You are now configured with non-elevated use that can run Deployment Packager. Start packager your SQL scripts.

...

For packaging scripts using STOREDLOGIC, DDL_DIRECT, DATA_DML, SQLFILE, 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. 

...