SQL Server 2008 R2 – How to Backup Full Transaction Logs

sql serversql-server-2008-r2

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.

Best Answer

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.

Related Question