2

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.

1

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.

  • I had that solution in the back of my mind, I'm not sure why I discounted it, however. I think maybe I had trouble implementing it before, but I'm not sure how as I just did so in the last 30 minutes. I did use this awesome resource, which was very helpful in generating the script. – acurcie Nov 6 '14 at 20:32
  • I do have a follow-up question, though: in what situation would the "Copy Database" wizard's generated job work well? I can't imagine that kind of log growth ever being reasonable. – acurcie Nov 6 '14 at 20:33
  • 1
    Log growth is never unreasonable; it is required to maintain consistency and durability. You simply need to plan for the necessary size of the log file, and allocate space to it. You can reduce the amount of used log space by taking transaction log backups simultaneous to the load process. Setup your hourly transaction log backups to occur more frequently, and the transaction log will not grow as much. – Max Vernon Nov 6 '14 at 20:42
  • 1
    @MaxVernon How would the frequency of trans log backups make a difference? It's still ultimately backing up the same data, is it not? Or when you say 'simultaneous to the load process' you mean that taking multiple trans log backups while the copy database job runs will keep the log file size down? – acurcie Nov 7 '14 at 16:20
  • 1
    Simultaneous log backups will help clear inactive log as the inserts or other transactions complete and are finished with that portion of the log. It may still grow a bit but generally helps it from growing rapidly. – Shawn Melton Nov 7 '14 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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