I've recently upgraded our deployment environment to include a staging server. Said server has a database on the same server as our production database. I am trying to set it up to copy data from the production database on a nightly basis.
The production database is set to full recovery model, with hourly trans logs backups and a nightly full backup. It is ~7gb, and its log file sits somewhere around 700mb.
I tried to use the "Copy Database" wizard to copy the prod db to staging and create a job to do so in the future. Upon the initial run from the wizard and any subsequent runs of the job the staging log file blows up to 15-17gb. I assume this is a result of it being in full recovery mode and the method with which it copies the database (an enormous amount of inserts?).
My question is, firstly, why would a built in function allow for log file growth like this? It seems dangerous. And, secondly, what is the best way to go about copying the data to the staging server? I would restore it from the nightly production backups, but the file name of the backup files is dependent on the date (Productiondb-2014-11-05-2205.bak), so I don't know how to script it.