Simple way to Drop Test databases and restore from Prod databases on 2008r2

Question

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.

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.