BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  <...>
  [ WITH { <...>
           | <general_WITH_options> [ ,...n ] } ]
[;]
<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
<...>
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...>

BACKUP DATABASE smth
TO DISK = 'D:\Backup\smth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

DROP TABLE IF EXISTS ##bt_results;
CREATE TABLE ##bt_results (
    id              int IDENTITY (1, 1) PRIMARY KEY,
    start_date      datetime NOT NULL,
    finish_date     datetime NOT NULL,
    backup_size     bigint NOT NULL,
    compressed_size bigint,
    block_size      int,
    buffer_count    int,
    transfer_size   int
);

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GO

DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:\SQLServer\backup\bt.bak''
WITH
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';
DECLARE @sql AS nvarchar(max);
/* BLOCKSIZE values */
DECLARE @bs     int = 4096,
        @max_bs int = 65536;
/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;
/* MAXTRANSFERSIZE values */
DECLARE @ts     int = 524288,   --512KB, default = 1024KB
        @min_ts int = 524288,
        @max_ts int = 4194304;  --4MB
SELECT TOP 1
    @bs = COALESCE (block_size, 4096),
    @bc = COALESCE (buffer_count, 7),
    @ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;
WHILE (@bs <= @max_bs)
BEGIN
    WHILE (@bc <= @max_bc)
    BEGIN
        WHILE (@ts <= @max_ts)
        BEGIN
            SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));
            EXEC (@sql);
            INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
            SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size,  @bs, @bc, @ts
            FROM msdb.dbo.backupset
            ORDER BY backup_set_id DESC;
            EXEC xp_cmdshell 'del "D:\SQLServer\backup\bt.bak"', no_output;
            SET @ts += @ts;
        END
        SET @bc += @bc;
        SET @ts = @min_ts;
        WAITFOR DELAY '00:00:05';
    END
    SET @bs += @bs;
    SET @bc = @min_bc;
    SET @ts = @min_ts;
END

BACKUP DATABASE [bt]
TO DISK = 'D:\SQLServer\backup\bt.bak'
WITH COMPRESSION;

SELECT TOP 7 WITH TIES
    compressed_size,
    block_size,
    buffer_count,
    transfer_size,
    DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;

RESTORE DATABASE [bt]
FROM DISK = 'D:\SQLServer\backup\bt.bak'
WITH REPLACE, RECOVERY;

TO <backup_device> [ ,...n ]

BACKUP DATABASE [bt]
TO
    DISK = 'D:\SQLServer\backup\bt1.bak',
    DISK = 'D:\SQLServer\backup\bt2.bak'
WITH COMPRESSION;

BACKUP DATABASE [bt]
TO
    DISK = 'D:\SQLServer\backup\bt1.bak',
    DISK = 'D:\SQLServer\backup\bt2.bak',
    DISK = 'D:\SQLServer\backup\bt3.bak',
    DISK = 'D:\SQLServer\backup\bt4.bak'
WITH COMPRESSION;

RESTORE DATABASE [bt]
FROM
    DISK = 'D:\SQLServer\backup\bt1.bak',
    DISK = 'D:\SQLServer\backup\bt2.bak',
    DISK = 'D:\SQLServer\backup\bt3.bak',
    DISK = 'D:\SQLServer\backup\bt4.bak'
WITH REPLACE, RECOVERY;