3

I have a 32GB database with over 7GB logs (DB name: XYZ). I'd like to copy the DB with all of the logs. I thought that I can simply run:

...
ALTER DATABASE XYZ SET RECOVERY FULL;
BACKUP DATABASE XYZ TO DISC = foo.bak;
BACKUP LOG XYZ TO DISC = foo.bak;
...

RESTORE DATABASE N_XYZ FROM DISC = foo.bak WITH NORECOVERY 
  MOVE LogicalNameData TO ....\mylocation\n_xyz.mdf
  MOVE LogicalNameLog TO ...\mylocation\n_xyz.ldf

RESTORE DATABASE N_XYZ FROM DISC = foo.bak WITH NORECOVERY
RESTORE LOG N_XYZ FROM DISC = foo.bak WIT RECOVERY

But when I checked the N_XYZ DB size it turned out that data takes 32GB but logs take only 10MB. I searched on the Internet and I found this link. It says that the DB transaction logs will only contain the pages between now and the last backup. Could that be a reason why my logs are so small? Can I force SQL Server to always copy the full logs? If so how?

Edit: Sorry for such a vague question. Below I described the whole story.

I have a CI which tests the newest code base every night. To be more specific the steps are:

  1. Get the newest code from the repository,
  2. Copy the database (the snapshot of production)
  3. Test/Run the whole App

The CI works fine but way slower than regular development environment. After a couple of hours I figured out that the slow down is caused by the second step. In other words: If I run the CI on freshly copied DB it takes 10 hours but if I rerun the CI on the same DB (so no DB is copied) it takes 3 hours. It looks like I'm loosing DB statistics when copying the backup.

So by all of the logs I meant the transaction logs with statistics.

7
  • What is the size of the original LDF? 7GB of logs, is this the backup size of the log you have?
    – DenisT
    Apr 24, 2015 at 12:37
  • 1
    What does "when I checked" mean? Where are you checking exactly? Apr 24, 2015 at 12:43
  • "pages between now and the last backup" - is actually between the last Transaction Log backup and current log backup! And yes, if between 2 logs backups you don't have a lot of operations then the tlog backup will be small - the size of the LOG file is not proportionally connected with the tlog size. Apr 24, 2015 at 12:52
  • Aaron Bertrand: I wrote an SQL query to get the DB/Log size.
    – orim
    Apr 24, 2015 at 13:44
  • My guess is the slowdown has nothing to do with logs or statistics, but rather the fact that (a) no plans exist for the new database and (b) none of the data is in buffer. There may be other complications too, like the fact that all plans must get recompiled and this could lead to less optimal plans. Apr 24, 2015 at 13:48

2 Answers 2

3

Does the ALTER DATABASE XYZ SET RECOVERY FULL imply that you are normally running in SIMPLE recovery model?

If so, the logs from the SIMPLE mode period are discarded once they are no longer needed. There is no time machine to go back and recover the logs that were not saved. So, to interpret your script:

ALTER DATABASE XYZ SET RECOVERY FULL; -- Begin saving logs
BACKUP DATABASE XYZ TO DISC = foo.bak;-- Full backup gives the log a start point
BACKUP LOG XYZ TO DISC = foo.bak; -- The logs that start with the full can now be backed up.

You can get the full logs by keeping the database in the FULL recovery model at all times.

Also the statistics are not stored in the log files but in the database.

6
  • I think that he can create FULL BACKUP WITH COPY - it depends what he means by "... copy the DB with all of the logs ... " Apr 24, 2015 at 12:57
  • Yes, the "all of the logs" is the apparent crux of the problem, but the language is not clear.
    – RLF
    Apr 24, 2015 at 13:22
  • RLF: Yes currently the DB recovery mode is set to SIMPLE (also I provided more information in my question).
    – orim
    Apr 24, 2015 at 13:44
  • Is it possible to copy the DB with statistics? Or I need to copy the statistics as a separate step?
    – orim
    Apr 24, 2015 at 14:18
  • When you restore backup it contains all the data, including statistics. The only problem will be if you have production database - all changes that are after you take backup, will not be in the restored database. Apr 24, 2015 at 14:53
3

You don't need to change database to Full recovery or backup and restore the transaction log for the scenario you are describing. If your database is always in Simple recovery then the transaction log is "cleared" as soon as a transaction is complete. Your transaction log on production is 7GB because at one time you had a transaction that used 7GB of transaction log space. use this to check how much log space is used.

DBCC sqlperf(logspace)

When doing a full database backup the transaction log is included in that backup otherwise the restored database would not be transitionally consistent.

The issue of performance is probably due to a cold cache. on your production server much of your databases may be in the buffer pool that not having to retrieve this data from disk. On you CI server with a newly restored database, there will be nothing in the cache and thus all data is read from disk which is much slower than reading from cache.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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