Split backups in SQL Server

Split backups in SQL Server

Split backup is a method of performing the backups on a SQL Server database to multiple files.
When we perform the split backups on a database, the SQL server engine creates multiple backup files with the size split into the number of files mentioned in the backup command.

?

1 2 3 4 5

BACKUP DATABASE [SansSQL] TO   DISK = N'D:\Backup\SansSQL_Part1_Backup.bak'    ,DISK = N'D:\Backup\SansSQL_Part2_Backup.bak' WITH INIT, STATS = 10 GO

When you execute backup command like above then the backup of that particular database will be split into 2 different files of almost equal size.
This can be used with Full, Differential and Log backups as well.

Stripe SQL Backups on multiple files

Stripe SQL Backups on multiple files

-- FULL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 



--DIFFERENTIAL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH DIFFERENTIAL,
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND