Post reply

Automation of refresh database from Prod to Dev using power shell script

  • February 14, 2018 at 11:08 am

    #327427

    Hi experts,
    Can someone please post a good power shell script to automate database refresh on Dev from Prod?
    Thanks.

  • February 14, 2018 at 11:43 am

    #1979795

    why power shell, why not T-sql?

    https://social.technet.microsoft.com/wiki/contents/articles/34552.sql-how-to-refresh-multiple-sql-databases-with-powershell.aspx

  • February 14, 2018 at 12:12 pm

    #1979802

    thanks goher2000 for your response. 
    Just wanted to clear my requirement, I have Prod database (s) backup sit in \'\prodserver\backup' drive and need to take latest backup(s) from the network drive and copy to the .bak file to '\\devserver\backup' and restore from there every day/week using power shell script. Will appreciate the help.

  • February 15, 2018 at 6:27 am

    #1979886

    Tac11 - Wednesday, February 14, 2018 12:12 PM

    thanks goher2000 for your response. 
    Just wanted to clear my requirement, I have Prod database (s) backup sit in \'\prodserver\backup' drive and need to take latest backup(s) from the network drive and copy to the .bak file to '\\devserver\backup' and restore from there every day/week using power shell script. Will appreciate the help.

    Using Powershell we haven't implemented the automation. Lets wait for other expert answers.

  • February 15, 2018 at 7:00 am

    #1979892

    subramaniam.chandrasekar - Thursday, February 15, 2018 6:27 AM

    Tac11 - Wednesday, February 14, 2018 12:12 PM

    thanks goher2000 for your response. 
    Just wanted to clear my requirement, I have Prod database (s) backup sit in \'\prodserver\backup' drive and need to take latest backup(s) from the network drive and copy to the .bak file to '\\devserver\backup' and restore from there every day/week using power shell script. Will appreciate the help.

    Using Powershell we haven't implemented the automation. Lets wait for other expert answers.

    The automation part can be done by running the powershell from either a SQL Agent job or a Windows Scheduler task (or even a third party scheduler). Make sure the account executing the powershell will have the permissions on both the PRD and DEV backup folder and the permissions in SQL on DEV to perform the restore action.
    For the powershell take a look at DBATools.IO. It has an extensive list of powershell scripts to perform many tasks with SQL Server.

    An additional note: think about depersonalize the data and also security when getting PROD data to another environment. You don't want your production data exposed by accident or malicious co-workers or...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 5 posts - 1 through 4 (of 4 total)

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