How to Stripe Your Backups into Multiple Files

If you have experienced the frustration of waiting in high volumes of traffic in a one lane tunnel, then you know what it’s like to be bogged down with lots of backups in just one file. In this blog, we will explore how to stripe your backup files into four rather than one. We will first run the database backups using one file and then stripe them into four files. We will then compare the backup durations required to achieve optimal time-saving benefits.

The simplest way to run a database full backup is to execute the backup database command. As in:

BACKUP DATABASE DatabaseName

TO DISK = 'X:\SQLBackups\DatabaseName.bak'

In the following example, we will run the database full backups for the AdventureWorks2012 and the AdventureWorksDW2012 databases with day and time appended to the backup files for ease of identification for archival and restore purposes.

-- Run the full backups for the selected databases with date and timestamp appended to the backup files

-- ****************************************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ****************************************************************************************************

DECLARE DBFullBackups_Cursor Cursor

FOR

-- Pick the databases you wish to run full backups

SELECT db.name

FROM sys.databases db

WHERE db.name in ('AdventureWorks2012', 'AdventureWorksDW2012') -- Added [TEST DB] with space in the db name for testing purposes


OPEN DBFullBackups_Cursor

DECLARE @db varchar(125);

DECLARE @BackupPath varchar(225);

DECLARE @BackupCmd varchar(525);


-- Check to make sure the path exists

SET @BackupPath = 'E:\SQLBackup\' -- specify your own backup folder


FETCH NEXT FROM DBFullBackups_Cursor INTO @db

WHILE (@@FETCH_STATUS <> -1)

BEGIN

       -- Backup command

       SET @BackupCmd = 'BACKUP DATABASE [' +@db+ '] TO DISK = ''' +@BackupPath+@db+ '_'

       +REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') --YYYYMMDDHHMMSS

       +'.bak'''


       -- Print the backup command

       PRINT @BackupCmd


       -- Run the backup command

       EXEC (@BackupCmd)


       -- Fetch the next database

       FETCH NEXT FROM DBFullBackups_Cursor INTO @db

END


-- Close and deallocate the cursor

CLOSE DBFullBackups_Cursor

DEALLOCATE DBFullBackups_Cursor

GO

Script: FullBackupSingleFile.sql

You can stripe the database backup files into a set of disk files to speed up the backup process. For example:

BACKUP DATABASE DatabaseName

TO DISK = 'W:\SQLBackups\DatabaseName_1.bak',

DISK = 'X:\SQLBackups\DatabaseName_2.bak',

DISK = 'Y:\SQLBackups\DatabaseName_3.bak',

DISK = 'Z:\SQLBackups\DatabaseName_4.bak'

Notice the different drive letters; you can stripe the backup files into multiple drives. This will reduce disk contention and speed up the backup process.

In the following example, we will run the database full backups for the AdventureWorks2012 and the AdventureWorksDW2012 databases with day and time appended to the backup files and stripe the backups into four files for each database.

-- Stripe the full backup files for the selected databases with date and timestamp appended to the backup files

-- ************************************************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ************************************************************************************************************

DECLARE DBFullBackups_Cursor Cursor

FOR

-- Pick the databases you wish to run full backups

SELECT db.name

FROM sys.databases db

WHERE db.name in ('AdventureWorks2012', 'AdventureWorksDW2012')

OPEN DBFullBackups_Cursor

DECLARE @db varchar(125);

DECLARE @BackupPath varchar(225);

DECLARE @BackupCmd varchar(525);

-- Check to make sure the path exists

SET @BackupPath = 'E:\SQLBackup\' -- specify your own backup folder

FETCH NEXT FROM DBFullBackups_Cursor INTO @db

WHILE (@@FETCH_STATUS <> -1)

BEGIN

       -- Backup command

       SET @BackupCmd = 'BACKUP DATABASE [' +@db+ '] TO DISK = ''' +@BackupPath+@db+ '_'

       +REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') --YYYYMMDDHHMMSS

       +'_1.bak'',

       DISK = ''' +@BackupPath+@db+ '_'

       +REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') --YYYYMMDDHHMMSS

       +'_2.bak'',

       DISK = ''' +@BackupPath+@db+ '_'

       +REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') --YYYYMMDDHHMMSS

       +'_3.bak'',

       DISK = ''' +@BackupPath+@db+ '_'

       +REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ':', ''), '-',''), ' ', '') --YYYYMMDDHHMMSS

       +'_4.bak''

       '

       -- Print the backup command

       PRINT @BackupCmd

       -- Run the backup command

       EXEC (@BackupCmd)

       -- Fetch the next database

       FETCH NEXT FROM DBFullBackups_Cursor INTO @db

END

-- Close and deallocate the cursor

CLOSE DBFullBackups_Cursor

DEALLOCATE DBFullBackups_Cursor

GO

Script: FullBackupMultiFiles.sql

We will now query the backup histories to make comparisons of the durations for the single file backups and multi-file backups.

-- Querying the Backup Histories

-- ****************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ****************************************************************************

SELECT SERVERPROPERTY('SERVERNAME') as InstanceName

, bs.database_name as DatabaseName

, bs.recovery_model

, CASE bs.type

       WHEN 'D' THEN 'Full'

       WHEN 'I' THEN 'Differential'

       WHEN 'L' THEN 'Log'

       WHEN 'F' THEN 'File or Filegroup'

  END as BackupType

, bmf.physical_device_name as BackupPath

, bs.backup_start_date as BackupStartDate

, bs.backup_finish_date as BackupFinishDate

, CONVERT(varchar, DATEADD(ms, DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) * 1000,0), 108) [Backup Duration HH:MM:SS] -- use 114 instead of 108 if you wish to see milliseconds

, CONVERT(NUMERIC (20,2), bs.backup_size/1024/1024) [Backup Size MB]

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

       ON bs.media_set_id = bmf.media_set_id

WHERE bs.backup_finish_date >= GETDATE() - 3 -- backups in the past 3 days

Script: QueryingBackupHistories.sql

Screen-shot: Single vs Multi-file Backups

Backup duration comparisons:

Note that the examples here are for relatively small database backups. Both of them are less than 3,000MB. As backup sizes increase exponentially, the benefits in the shortened backup duration will be more apparent.

Datavail Script: Terms & Conditions

By using this SQL Server software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as set-forth herein.
2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.
4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

   You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.