Scheduled copy of production database into test database

Question

My question is similar to this one.

I've Two different severs (both SQL Server 2014 Std Edition). Production and Test. I want my test server to connect every night to the production server and copy/clone one database.

I Know I could schedule a backup in the Production server, put this backup in a shared folder and the restore it in the Test Server.

Is there any way to make the Test Server do all the job without using any shared folder?

Thanks.

Solution 1

You could write a program which does it all in TSQL. Or use an SSIS task

Have a look on this SO post for some more details

Solution 2

Use the Copy Database Wizard. - Right click the source database - Choose Tasks - Copy Database.

In the guide You can choose to create a schedule for the job also.

Comments

[Grant]

I prefer to automate the restore from my backups. That way you get automatic testing thay your backup and restore procedures work as well.