Powershell Script for DB refresh

SQLLover21 201 Reputation points
2020-12-02T17:12:25.177+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,759 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,456 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,381 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments
Accepted answer
  1. Monalv-MSFT 5,896 Reputation points
    2020-12-03T09:46:10.467+00:00

    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?

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dirk Hondong 871 Reputation points
    2020-12-02T17:28:17.273+00:00

    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/

    1 person found this answer helpful.
    0 comments
  2. Mike Fleming 1 Reputation point
    2020-12-21T06:33:24.35+00:00

    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.

    0 comments