patternsqlMinor

Saving SQL Server backup file in multiple parts

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

filesqlmultipleserversavingpartsbackup

Problem

I am taking daily backups of a SQL Server database. Right now the .bak file is about 2GB and it is growing day by day. There is a scheduled job is running which moves this .bak file from one location to another.

Is there a way to save the .bak file in chunks - like part1.bak, part2.bak etc..

so it will be much easier to move small data and merge at destination?

Solution

Normal backup

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

to split the size into 3 use the following code, it will split the size of 3 gb into 1 GB each file
you can add more files or less depending on the size you want

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME TO

DISK = 'D:\DBBackups\ReportServer\DBNAME _Split1.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split2.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split3.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

for more details

http://beyondrelational.com/modules/2/blogs/88/posts/10153/sql-server-split-database-full-backup-to-multiple-files.aspx

Code Snippets

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

SET STATISTICS IO ON

SET STATISTICS TIME ON


BACKUP DATABASE DBNAME TO

DISK = 'D:\DBBackups\ReportServer\DBNAME _Split1.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split2.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split3.bak'


SET STATISTICS IO OFF

SET STATISTICS TIME OFF

Context

StackExchange Database Administrators Q#17875, answer score: 9

Revisions (0)

No revisions yet.