Striped Backups in SQL Server

Trolling is a common technique for trying to catch fish. Trolling involves motoring slowly through the water while pulling one or more fishing lines. Usually rod holders secure the rod in the boat and allow you to space the lures apart. You’ll set a line deep, a line shallow, and a line in a medium depth, and you can also use different lure types and different colors. With this great variety of bait in the water, you have a much better chance of giving the fish something that they are interested in biting. I’ve never been a fan of trolling because you generally aren’t holding the rods, so you don’t feel the fish strike. I kind of view it as motoring around in circles, then reeling in a fish when it strikes. That said, I certainly understand how it can be more efficient than casting.
Similarly, SQL Server gives us the ability to be more efficient with our backups by writing them out with multiple lines, or striping them. Using this technique, the backups are written to multiple files. The total space used is equal to a single file, but since each file can be written to and read from simultaneously, the backups and restores may end up being faster if I/O throughput is an issue. Before I show an example, there is one important caveats to mention:

  • All of the backup files are necessary to do a restore. These aren’t like a mirrored RAID where you get redundancy. Therefore, if you lose one file, the entire backup is broken.

Ok, now on to the demonstration. To make a good test you’re going to want a fairly large database that has a high percentage of space used. If you need to artificially fill a database for testing, try using my script below to create a table and fill it with junk data:


CREATE TABLE StripedBackupTest
    (
      ID UNIQUEIDENTIFIER DEFAULT NEWID()
                          PRIMARY KEY ,
      FixedNumber DECIMAL(38, 10) ,
      MuskieFishingRocks CHAR(100) ,
      ReallyLongBlob VARCHAR(MAX) ,
     ReallyLongBlob2 VARCHAR(MAX) ,
     ReallyLongBlob3 VARCHAR(MAX)
    )
    CREATE INDEX IX_AllCols ON dbo.StripedBackupTest 
    (
    MuskieFishingRocks,
    FixedNumber,
    ID
    ) INCLUDE (ReallyLongBlob)
   
DECLARE @i INT = 1
WHILE @i <= 100000
   BEGIN
       INSERT INTO dbo.StripedBackupTest
               ( FixedNumber ,
                 MuskieFishingRocks ,
                 ReallyLongBlob ,
                 ReallyLongBlob2 ,
                 ReallyLongBlob3
               )
       SELECT  ABS(CAST(NEWID() AS BINARY(6))%1000000000)*1000000000.0 
                   + ABS(CAST(NEWID() AS BINARY(6))%1000000000)
                   + ABS(CAST(NEWID() AS BINARY(6))%1000000000)*.0000000001, --A random number with 18 digits left of the decimal and 10 right of the decimal
               'MuskieFishingRocks',
               REPLICATE(CONVERT(VARCHAR(255),NEWID()) + CHAR(10) + CHAR(13),500),
               REPLICATE(CONVERT(VARCHAR(255),NEWID()) + CHAR(10) + CHAR(13),500),
               REPLICATE(CONVERT(VARCHAR(255),NEWID()) + CHAR(10) + CHAR(13),500)
   END

I’m going to use an existing test database that is almost 28 GB in size, with almost 4 GB of free space. This gives me about 24 GB in used space.
20150219 Striped Backups Final DB Size First I’ll take a normal, one file backup to get a baseline.

BACKUP DATABASE cwi_warehouse TO  DISK = N'T:\TempBackups\SingleBackupTest.bak' 
   WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'DB_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

20150219 Striped Backups Single File Backup Time You can see this backup took 334 seconds to run. It’s also a great idea to perform a restore-verify only consistency check on a backup when you take it. I also ran that and got an additional 3:51 amount of time:

DECLARE @backupSetId AS INT
SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = N'cwi_warehouse'
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = N'cwi_warehouse'
                            )
IF @backupSetId IS NULL
    BEGIN
        RAISERROR(N'Verify failed. Backup information for database ''cwi_warehouse'' not found.', 16, 1)
    END
RESTORE VERIFYONLY FROM  DISK = N'T:\TempBackups\SingleBackupTest.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

20150219 Striped Backups Single File Verify Time

The last step is to run a full restore:

USE [master]
RESTORE DATABASE [cwi_warehouse] FROM  DISK = N'T:\TempBackups\SingleBackupTest.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

GO

20150219 Striped Backups Single File Restore Time

This ran in 306 seconds. Now we have our baseline. It can be a good idea to run each statment three times and take the average when calculating a baseline, just because other things can affect the results, such as processes, network traffic, etc. Next I’ll run a striped backup using four separate files:

BACKUP DATABASE cwi_warehouse TO  DISK = N'T:\TempBackups\MultiBackup_1.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_2.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_3.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_4.bak' 
   WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'DB_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This time the backup ran in 340 seconds.
20150219 Striped Backups Multi File Backup Time Next I run the backup verification.

DECLARE @backupSetId AS INT
SELECT  @backupSetId = position
FROM    msdb..backupset
WHERE   database_name = N'cwi_warehouse'
        AND backup_set_id = ( SELECT    MAX(backup_set_id)
                              FROM      msdb..backupset
                              WHERE     database_name = N'cwi_warehouse'
                            )
IF @backupSetId IS NULL
    BEGIN
        RAISERROR(N'Verify failed. Backup information for database ''cwi_warehouse'' not found.', 16, 1)
    END
RESTORE VERIFYONLY FROM  DISK = N'T:\TempBackups\MultiBackup_1.bak',
   DISK = N'T:\TempBackups\MultiBackup_2.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_3.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_4.bak'  WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

20150219 Striped Backups Multi File Verify Time This ran in 3:37.
Lastly I run the full Restore.

USE [master]
RESTORE DATABASE [cwi_warehouse] FROM  DISK = N'T:\TempBackups\SingleBackupTest.bak',
   DISK = N'T:\TempBackups\MultiBackup_2.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_3.bak' ,
   DISK = N'T:\TempBackups\MultiBackup_4.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

GO

20150219 Striped Backups Multi File Restore Time This ran in 352 seconds.
You can see that the multi-file backup configuration didn’t really give us any significant time savings. The last point to make is that multi-file backups can be a huge advantage when backup disk space is an issue. If you have a particularily large backup and need to spread it across multiple physical disks to get it to fit. You can see here the sum of the four backups were about equal in size to the single backup file.

20150219 Striped Backups Single File Size

20150219 Striped Backups Multi File Size

If I had four disks with five GB available on each, I would not be able to do a backup of this database with a single file, but could with multiple files. Just remember, all the files are needed to be able to restore the database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s