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.
Best Answer
The package created by the copy wizard can be modified to bring a little better performance possibly but your log growth is expected. Every bit of data changed for the table and indexes is being modified, that has to be logged no matter what recovery model you are in.
I would go with the restore from backup as this will be a bit cleaner. You can pull the last backup file name from the msdb backup tables. There are a ton of examples of this online, would check MSSQLTips.com for a start.
You would simply pull the last backup, verify it exist, robocopy it over (unless it exist on network share), then restore it.