Is it possible to do a partial transaction log backup with SQL Server 2017? I don’t have enough disk space to do a full backup

Question

Inherited a database server to maintain and found that the transaction log is 92 GB. My plan is to do the backup of that and get it brought back down to a regular size, then fix it. The problem is I don’t have enough disk space on the machine to do the backup locally, then offload. Is there a way I can run the backup for a specified size or time period so I can do it in stages?

asked 2021-10-15 by sdouble

---

Answer

No, there is no concept of a "partial transaction log backup".

Do you need to maintain the transaction log chain for restores, or otherwise hold on to the 92GB of transaction log backups?

If you want to just get rid of it & start over, and can confidently say "I don’t need to do point in time recovery to a point in time before now!" then you can just start over.

You can effectively discard the whole thing by backing up to NUL:

BACKUP DATABASE MyDatabaseWithHugeTLog
    TO DISK = 'NUL:'; --Sends it to nowhere

Then you would be able to shrink the transaction log to make it be less huge:

USE MyDatabaseWithHugeTLog
DBCC SHRINKFILE(MyDatabaseWithHugeTLog_Log,10000);

Note, you may need to backup/shrink/backup/shrink more than once to get the log down to the desired size. Shrinking the log effectively just lops off the tail end of the file, so if there is a portion that is in-use, you may need to get that active portion to wrap around to the start to shrink to the desired size.

Then, you’ve ruined your ability to do point-in-time recovery, because we sent the log off to NUL. So you’ll want to take a fresh full or differential backup so that you have a starting point for future restores.

BACKUP DATABASE MyDatabaseWithHugeTLog
    TO DISK '\\SomeServer\MyBackupShare\MyDatabaseWithHugeTLog_YYYYMMDD_HHMISSMS';

answered 2021-10-15 by Andy Mallon