Post reply

Database copy automation

  • December 18, 2019 at 11:53 am

    Go to Answer

    #3708749

    I have SQL server 2016 with multiple databases so i have specific request for one of databases with name Persons. The requirement is that developers need copy of that database on same server with other name ex.PersonsCopy. This task need to be automated so every 17th on the month that database Persons need to be restored with other name PersonsCopy. I want to notice that i have scheduled daily full backup so the backup file name is dynamic ex.Persons_backup_2019_12_15_000002_1287189.

    BR

  • December 18, 2019 at 1:28 pm

    #3708762

    That is a requirement, but what is your question?

    1. How do I restore a database?
    2. How do I restore a database when the name of the backup is dynamic?
    3. How do I retrieve the name of the latest backup file?
    4. Is restoring a copy of a database on the same instance with a different name a good idea?
    5. Should I mix development and production databases on the same instance?
    6. How can I be sure that my restored database does not contain any three-part naming?

    The list of possibilities goes on. Please ask a specific question.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 18, 2019 at 1:44 pm

    Answer

    #3708792

    RESTORE DATABASE name

    WITH REPLACE, --if it's there already

    MOVE 'file' TO 'x:\newfile.mdf',

    MOVE 'log' TO 'z:\newlogfile.ldf'

    Automate this.

    However, I think Phil pointed out an excellent question, should development be occurring on a production system? GDPR anyone? CPPA? HIPAA? FERPA? PCI?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 19, 2019 at 12:42 pm

    #3709032

    I can manual restore the database with different name, but my point is how to restore database every 3rd day from the latest daily full backup and this process to be scheduled via SQL agent.

    p.s forget security, GDPR, production vs test server etc.. this is only for testing purpose.

     

    Thank you

     

  • December 19, 2019 at 12:56 pm

    #3709039

    In mean time i have found solution.

     

    Tnx guys

     

    BR

  • December 19, 2019 at 1:24 pm

    #3709048

    alexandermkd wrote:

    In mean time i have found solution.

    Tnx guys

    BR

    I assume a script (powershell or t-sql) and SQL Agent? That's how I'd do it.

    Posting the solution would be helpful for anyone who finds your question because they have the same problem.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 19, 2019 at 2:36 pm

    #3709122

    alexandermkd wrote:

    In mean time i have found solution.

    Tnx guys

    BR

    Two way street here... what solution did you come up with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply