Clone Database fast

SQL Server Administration
1

Hi,
‌What is the fastest way to clone a database of 50 GB every day (SQL2014), without any interruption (detach / attache). This database is intended as a ReadOnly DB for marketing. (PowerShell, C # Smo, ??)
Thanks and :slight_smile: Regards
Nicole
:star_struck:

2

Do you want it on a separate server? Are you using Enterprise Edition?

  • Log shipping or mirroring to a remote server and create snapshots? I think this requires Enterprise but that may have changed after SQL Server 2016 SP1.
  • Can you have interruptions in marketing copy? Backup and restore? 50 GB goes pretty fast these days :slight_smile:
  • An availability group with a readable secondary?
  • We use a fancy backup appliance that can mount snapshots onto other servers. That's fast but the appliance is EXPENSIVE.
3

Copy only Backup and restore has the least impact on production. It may not be fastest.
If you are working with enterprise edition a read only secondary would be real time, the fastest, if synchronous. If not it would still be pretty close to the primary once caught up although if the primary is very active in asynchronous mode it may lag behind.