Non-elevated user for SQL Server

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

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

This document discusses another approach that does not requires abovementioned SQL Server privileges for packaging. 

Overview

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.

Requirements

This approach requires the following setup:

• 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. Save the groovy script (BackupRestoreSqlServerNoBackupFile-v5.4.groovy) to a path in C://apps//DaticalDB//repl//scripts//extensions
2. Create baseline of your REF database. This will import database objects into Datical's changelog
3. 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 backup