locked
Fast Clone of Database RRS feed

  • Question

  • Hi,

    What is the fastest way to clone a database of 50 GB 3 Times per day (SQL2014), without any interruption (detach / attache). This database is intended as a ReadOnly DB for marketing. (PowerShell, C # Smo, Backup/Restore, Copy :)....?
    Regards
    Nicole

    Saturday, September 8, 2018 1:54 PM

Answers

  • Hi There,

    Attach/detach is not your solution as you need to take the database offline.

    Backup restore is one considerable option. but three times in a day do not seems to be ideal. how ever this can be automated.

    I agree with philfactor idea of snapshot replication. 

    good luck

    kumar

    • Marked as answer by Nicole44 Thursday, June 13, 2019 5:52 PM
    Monday, September 10, 2018 1:59 AM

All replies

  • Backup/Restore seems like the best bet. You can try to speed up the backup times by following some best practices such as backing up to the local server, striping, compression etc. 

    Alternatively, you can do the first restore with "Standby" which would make the clone readable and for the second attempt, you can simply restore the incremental backup (log backup) on the clone which would make it current as of the second attempt. You can repeat it for the third time in the day. That way, you won't have to restore a full backup each time. This has some caveats though, such as the clone would be unavailable during the restores.

    Unfortunately, SQL doesn't provide an option to clone from an Active database as Oracle's RMAN active duplication does. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Saturday, September 8, 2018 3:55 PM
  • Backup restore is most common method to create copy of database

    You can take a copy only backup to make it independent of normal backup chain if you've one


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Saturday, September 8, 2018 4:48 PM
    Saturday, September 8, 2018 4:45 PM
  • Snapshot replication feature may satisfy your requirement for creating read-only copies of database on a regular schedule:
    Snapshot Replication

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Sunday, September 9, 2018 2:10 PM
  • Hi There,

    Attach/detach is not your solution as you need to take the database offline.

    Backup restore is one considerable option. but three times in a day do not seems to be ideal. how ever this can be automated.

    I agree with philfactor idea of snapshot replication. 

    good luck

    kumar

    • Marked as answer by Nicole44 Thursday, June 13, 2019 5:52 PM
    Monday, September 10, 2018 1:59 AM