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

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?

No, you can't do partial log backups to split this work into multiple files.

What you can do depends on your goals.

• If you need to maintain the log backup chain, your only option is to provision more storage (locally, or via a network share) and do a complete log backup

• If you don't need to maintain the log backup chain, and your goal is simply to get this file back to a manageable size, you have a number of options. The gist of what you need to do, though, is this:

• "truncate" the transaction log (by switching to the SIMPLE recovery model, or doing a backup log TO DISK = 'NUL', or probably a number of other things)

• shrink the log file to an appropriate size for your workload (using the SHRINKFILE command)

• If you switched to the SIMPLE recovery model, you should switch back to FULL now*

• Regardless of the approach you take to truncating above, you'll want to re-initialize the backup chain by taking a full backup and then a log backup, so that you are back in a good state, recovery-wise

*By the way, if you're intentionally not taking log backups, and don't plan to start, you should leave the database in the SIMPLE recovery model. Otherwise the log file will grow again.

StackExchange Database Administrators Q#301199, answer score: 20