I have a production database instance on a production server and a test database instance on a test server. I need to restore the production databases to the test instance daily.

What I need:

LOOP through Prod DB 
If DB name exists in Test Drop the test database
Restore DB from Prod
Next in LOOP

SQLServers Copy Databsase Wizard seemed to do what I wanted but it errors on custom database roles, apparently an issue with 2008r2.

Apex Backup is also getting an error.

Backups are made of the production databases daily so I could also just drop the test databases and restore from those BAK files.

Is there an easy way to do what I need to do?

PS: I'm not a DBA, I'm a dev who has to do DBA stuff, so forgive me my ignorance.

  • 1
    look no further dbatools. or create your own custom ps code to suit your needs.
    – user37701
    Commented Jan 3, 2018 at 5:01

1 Answer 1


As a recommendation, I would suggest switching to Ola Hallengren maintenance solution for the backups and some other administrative tasks, is really easy to configure. Run the main script Maintenance Solution, schedule required jobs and off you go. I hope that backups are copied to a different server than production server.

Then, schedule restore jobs from the test server to restore those daily backups. The job would have minimum 2 steps, drop database and restore it. Maybe you will want to add some cleaning step to clean up or mask some critical data that shouldn't be available for testing, replacing emails to avoid sending emails to clients and stuff like that. Maybe even deleting some data from bigger tables to have smaller databases. Heck, maybe for some cases is enough to just have the skeleton of the databases.

If there are few databases, you can handle it manually. If not, you could make use of dbatools as commented, is a set of powershell scripts really helpful for administrative tasks. Or you can search online, there are plenty of options available, 3rd party tools, scripts that you will need to tune for your needs, etc.

  • 1
    Note that RESTORE DATABASE has the WITH REPLACE clause, which will restore the new DB over the old one without having to drop it first.
    – RDFozz
    Commented Jan 3, 2018 at 22:35
  • Correct, no need to drop it, restore will do it by itself
    – Yaroslav
    Commented Jan 3, 2018 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.