DBAtlas · Database operations atlas

Take an ad-hoc backup without breaking the chain (COPY_ONLY, striping, throughput)

SQL ServerBackup & Log

Command

-- Ad-hoc FULL that does not reset the differential base:
BACKUP DATABASE [Musteri]
TO DISK = N'D:\Backup\Musteri_copyonly_20261008.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT, STATS = 10;

-- Ad-hoc log backup that does not truncate the log:
BACKUP LOG [Musteri]
TO DISK = N'D:\Backup\Musteri_copyonly_20261008.trn'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT;

-- Large DB: stripe across files and tune buffers:
BACKUP DATABASE [Musteri]
TO DISK = N'D:\Backup\Musteri_s1.bak', DISK = N'E:\Backup\Musteri_s2.bak'
WITH COPY_ONLY, COMPRESSION, CHECKSUM, INIT,
     MAXTRANSFERSIZE = 4194304, BUFFERCOUNT = 64, STATS = 5;

What it does

Takes a standalone FULL or log backup that does not affect the regular backup chain (differential base and log chain); for large databases, striping across files and a larger transfer size shorten the backup.

When to use it

When handing a copy to test/dev, before a risky change, or when a backup is requested for an audit; whenever you need a backup without touching the scheduled backup strategy.

Caution

An ad-hoc FULL without COPY_ONLY becomes the base of subsequent DIFF backups; if that file is deleted, the DIFFs are useless. COPY_ONLY has no effect together with DIFFERENTIAL. Restoring a striped backup needs every file; losing one makes the whole backup unusable. BUFFERCOUNT x MAXTRANSFERSIZE of memory is allocated outside the buffer pool (256 MB in the example); lower it on a server under memory pressure.

Open in DBAtlas →

Related