Powershell Script for DB refresh

Question

I need to create a PowerShell script that will copy my backup files from PROD server to restore it on DEV server on a monthly basis. Then I need to create a SQL Agent job with two steps. The first step will be to restore those copied files onto DEV Server. The second step will be to execute a SP that cleans up the redundant/inconsistent data.

I have never created a PowerShell Script before. Please provide me with best recommendations for a short and easy script.

Answer 1

Hi @SQLLover21 ,

Hope the following links will be helpful :

1.PowerShell Script To Manipulate SQL Server Backup Files

2.PowerShell how to backup and recover an SQL Server database FAQ

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 2

Hi there,

take a look at powershell module dbatools.
You do not need to reinvent the wheel.
Just get used to the cmdlets and you are fine

www.dbatools.io

Or as a starter how you can solve such problems:
https://www.sql-aus-hamburg.de/powershell-script-with-dbatools-copy-database-with-rename/

Answer 3

Have a look at this refresh script.

https://github.com/MikeyMing/powershell-sql/wiki/BackupAndRestore

It will allow you to refresh a databases using one line of PowerShell and takes care of all the usual checks and best practice configurations. Its very useful for backup and restore to dev or test environments for for migrating SQL databases.