locked
Backup & DBCC SQLPERF(LOGSPACE) RRS feed

  • Question

  • Hello,

    Running SQL Server 2008 R2.  We are attempting to take a copy-only backup from one server and move it to another so we can conduct testing related to backups.  The database in question uses the Full recovery model.

    We run the following command for backup:

    BACKUP DATABASE [Test]
        TO DISK = 'E:\Backup\Test.bak'
    WITH
        COPY_ONLY,
        INIT,
        STATS

    And we use the following command to restore:

    RESTORE DATABASE [Test]
        FROM DISK = 'E:\Backup\Test.bak'
    WITH
        MOVE 'Test' TO 'G:\Data\Test.mdf',
        MOVE 'Test_Log' TO 'G:\Data\Test_Log.ldf',
        REPLACE,
        STATS

    When we run DBCC SQLPERF(LOGSPACE) on the source server we get:
    Log Size: 36823.3 MB
    Log Space Used: 94.40%

    When we run DBCC SQLPERF(LOGSPACE) on the target server (after restoring) we get:
    Log Size: 36823.3 MB
    Log Space Used: 0.46%

    Based on the above, why does the Log Space Used value change between servers?  And how do we preserve the log condition when making the backup?

    Thanks!

    Tuesday, October 9, 2018 11:53 PM

All replies

  • Hi 2012S4,

    DBCC SQLPERF provides transaction log space usage statistics for all databases and your result returns the current size of the transaction log and the percentage of log space used for each database. According to your result,  the percentage of log space used for each database gets small.

     

    Firstly, BACKUP DATABASE specifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up. During a full or differential database backup, SQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored. It means the transaction log is not complete. So you can do some operations to BACKUP LOG. For more details, you can refer to this article :https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

     

    Secondly, if you would like to preserve the log condition when making the backup, you can also use BACKUP LOG.

     

    Hope it can help you.

     

    Best Regards

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 10, 2018 3:19 AM
  • Hi Rachel,

    Thanks for responding.  If I were to use the following as my backup command:

    BACKUP LOG [modal]
        TO DISK = 'E:\Backup\Test_Log.bak'
    WITH
        COPY_ONLY,
        INIT,
        STATS

    Can you confirm that the copy-only backup WILL NOT truncate the transaction log?  I ask because BOL says that copy-only backups never truncate the transaction log, however when using SSMS to backup the log the options for transaction log truncation are not deactivated when you select Copy-only Backup.  I am especially cautious in this instance due to the fact that the log backup we're attempting to take is from a production server and I don't want to disturb the restore sequence on this server/database.

    Thanks!

    Wednesday, October 10, 2018 3:59 AM
  • Hi 2012S4,

     

    A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

     

    A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore.

     

    The transaction log is never truncated after a copy-only backup. So the copy-only backup WILL NOT truncate the transaction log.

     

    By the way ,as you said that 'using SSMS to backup the log the options for transaction log truncation are not deactivated when you select Copy-only Backup', I may not find options.

     

    For more details, you can refer to this official article: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-2017

     

     

    Hope it can help you.

     

    Best Regards

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 10, 2018 5:53 AM