Automate Database Snapshots between two servers using Redgate

Lloyd_Mason

Before investing in a Redgate license, I'm outlining a plan for daily database snapshots and would appreciate feedback on its feasibility.

 We're looking to set up an automated process for daily snapshots from a database, and we'd appreciate if someone could confirm whether the steps listed below are possible or not.

1. Database Connection:

   • We plan to use Redgate SQL Compare to connect to our databases.
     • Source: sql-prod-server
     • Target: sql-dev-server
   • Can we establish these connections?

2. Scheduling Snapshots via SQL Automation Pack:

   • We aim to utilize Redgate's SQL Automation Pack for automating database snapshots.
   • Our goal is to:
     • Take a daily snapshot from sql-prod-server.
     • Push this snapshot to sql-dev-server.
   • Is this scheduling feasible?

3. Post-Snapshot Syncing:

   • Once the snapshot is captured, we want to resolve differences between the source and target.
   • Our plan:
     • Compare the snapshot from sql-prod-server with sql-dev-server.
     • Apply necessary changes to make sql-dev-server reflect the snapshot.

4. Notification Mechanism:

   • For oversight, we'd like to be alerted for:
     • Successful task completions.
     • Any failures in the process.
   • Is it possible to set up such notifications?

5. Testing the Setup:

   • To ensure proper setup, we'd manually run the tasks at first.
   • We'd then monitor sql-dev-server for the snapshot and the entire process.
   • Can we conduct such tests?

Our goal is to have sql-dev-server updated daily using the snapshot from sql-prod-server via Redgate tools. We'd also monitor this regularly. Could you confirm the feasibility of these steps?

DanC

Hi @Lloyd_Mason

I would have a look at the example here: https://www.red-gate.com/hub/product-learning/sql-compare/automating-schema-comparisons-with-sql-compare-projects-and-powershell