When we do full data backup (using SSMS UI), at the bottom of the window we have the option to specify the destination as Disk and also to add multiple files.

My question is - does adding multiple files create duplicate copies of the full backup? or does it create a split backup - that is split the full backup into the specified files?

This book suggests it does a duplication where as this link suggests that it does a split. Please can someone clarify.

Don't rely on the behaviour of SSMS, which has historically been buggy or poorly documented and also sometimes changes from version to version.

The easiest way to be sure is to click the "script" button on top of the dialog and compare the output to the documentation.

For example when you click the script button after adding several files in the SSMS version that comes with SQL Server 2014 like this:

enter image description here

You can see the exact script that will be used is this:

BACKUP DATABASE [mydatabase] TO  
DISK = N'G:\location\file1.bak',  
DISK = N'G:\location\file2.bak' WITH NOFORMAT, NOINIT,  
NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

If you then refer to the documentation about backups you will see that this script creates a striped backup.

A mirrored backup would need the MIRROR TO clause like this

BACKUP DATABASE [mydatabase] TO  
DISK = N'G:\location\file1.bak'  
MIRROR TO DISK = N'G:\location\file2.bak' ;
GO

I don't know which version of SSMS your book is talking about as it's unavailable to me, but the bottom line is don't trust SSMS, use T-SQL and refer to the documentation.

  • So is the link (book) misleading? – variable Oct 12 at 9:27
  • 1
    @variable I don't know. Google shows me "you have reached a page that is unavailable or exceeded the maximum reading for this book". It could very well be that the book has some other version of management studio or some option selected. Don't rely on SSMS but use T-SQL – Tom V Oct 12 at 9:31

Well let's just go ahead and test it...

Back Up Database - StackExchange

General Tab

Added multiple file in the Backup Properties' General Tab

Backup Database - General Tab - Multiple Files

Backup Options

We'll add a comment to inform other of what we were trying to achieve.

Backup Database - Backup Options - Comment

Script Out The Backup

Just so we know what SSMS does in the background we'll script out the settings and run the script instead of clicking on OK. You achieve this by clicking on the Script button in the backup screen.

Back Up Database - Script Button

The Backup Script

BACKUP DATABASE [StackExchange] 
TO  
    DISK = N'C:\SQL\BACKUP\StackExchange_FULL_20181012_105600_1.bak',  
    DISK = N'C:\SQL\BACKUP\StackExchange_FULL_20181012_105600_2.bak',  
    DISK = N'C:\SQL\BACKUP\StackExchange_FULL_20181012_105600_3.bak' 
WITH  
    DESCRIPTION = N'StackExchange-Full Database Backup', 
    NOFORMAT, 
    NOINIT,  
    NAME = N'StackExchange-Full Database Backup', 
    SKIP, 
    NOREWIND, 
    NOUNLOAD,  
    STATS = 10
GO

Results of Backup Script

Here are the results of the backup:

10 percent processed.
21 percent processed.
31 percent processed.
42 percent processed.
52 percent processed.
62 percent processed.
72 percent processed.
83 percent processed.
90 percent processed.
Processed 3712 pages for database 'StackExchange', file 'StackExchange' on file 1.
Processed 16 pages for database 'StackExchange', file 'StackExchange_DefRO' on file 1.
100 percent processed.
Processed 2 pages for database 'StackExchange', file 'StackExchange_log' on file 1.
BACKUP DATABASE successfully processed 3730 pages in 0.085 seconds (342.744 MB/sec).

Restore the Database - StackExchange

So no we have multiple files, let's go ahead and try the restore using a similar procedure. We'll do everything in the SSMS console and then script it out.

Restore Database - StackExchange

General Tab

We'll leave everything as is. SSMS will generally take the last backup as a starting point and present you with more or less options.

Restore Database - General Tab - Nothing Special

Files Tab

While this could be an option to define the files you are restoring from, sadly nope. This is just the database files of the database.

Restore Database - Files Tab - Nothing Noteworthy

Options Tab

Even this tab doesn't reveal which files will be used.

Restore Database - Options Tab - Nothing Here Either

Script Out The Restore

Let's do the same as before an script out what we have clicked together.

The Restore Script

USE [master]
RESTORE DATABASE [StackExchange] 
FROM  
    DISK = N'C:\SQL\BACKUP\NB31710\StackExchange\FULL\StackExchange_FULL_20181012_105600_1.bak',  
    DISK = N'C:\SQL\BACKUP\NB31710\StackExchange\FULL\StackExchange_FULL_20181012_105600_2.bak',  
    DISK = N'C:\SQL\BACKUP\NB31710\StackExchange\FULL\StackExchange_FULL_20181012_105600_3.bak'
WITH  
    FILE = 1,  
    NOUNLOAD,  
    STATS = 5

GO

...and that looks pretty interesting. Let's delete one of the DISK = ... lines and see if the restore works.

USE [master]
RESTORE DATABASE [StackExchange] 
FROM  
    DISK = N'C:\SQL\BACKUP\NB31710\StackExchange\FULL\StackExchange_FULL_20181012_105600_1.bak',  
    DISK = N'C:\SQL\BACKUP\NB31710\StackExchange\FULL\StackExchange_FULL_20181012_105600_2.bak'  
WITH  
    FILE = 1,  
    NOUNLOAD,  
    STATS = 5

GO

The Restore Results With One DISK Parameter Deleted

Msg 3132, Level 16, State 1, Line 2 The media set has 3 media families but only 2 are provided. All members must be provided. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

Summary

Configuring the SSMS backup to back up to multiple files will stripe the backup. You require all files to restore the backup. The backup does not create duplicate copies.

  • So is the link (book) misleading? – variable Oct 12 at 9:26
  • Yes. It appears so, but I can't access the book. – hot2use Oct 12 at 9:27

Using SSMS to create multiple backup files will stripe the backup across those files, rather than duplicating the backup onto each file.

This means that each file is also needed for the restore - if you only keep one you'll only have a part of the data set.

  • So is the link (book) misleading? – variable Oct 12 at 9:27
  • 1
    @variable Personally I don't think so. Could you clarify exactly which bit of text you think is misleading? (Add the quote to the OP) – George.Palacios Oct 12 at 9:40
  • Actually is a diagram if you have a look at this and scroll down a bit youl see please thanks - books.google.co.in/… – variable Oct 12 at 9:55
  • @variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets. – George.Palacios Oct 12 at 10:11

This will create Striped backup.

Consider a scenario where you need to take backup of a db which is quite big to accommodate in a single disk currently. This option gives you a solution of backing up the db across multiple disks.

Creating a duplicate backup is done by below methods :

  1. Just copying the taken backup to multiple disks.
  2. Taking multiple backups , but changing the path each time.
  3. Use backup mirroring – process where a database backup file is written to two or more (up to 3) different locations simultaneously.

As per the link you shared :

  1. It is incorrect as it will just create 4 sets of split backups.
  2. If the keyword 'Mirror" was used, it would have created 4 copies of backup media with Full,Diff and Log backup in it.
  3. If you use the script in given link, to restore it back you will need to select all 4 files else there will be error thrown as 'Backup Media Set is not complete'

I would request you to download a copy of 'adventureworks' db and test as these Exam Ref books can have Printing errors which can be deadly. :) Good luck for the test.

  • I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/… – variable Oct 12 at 10:05
  • Edited the answer as per the link after testing it. – Ramakant Dadhichi Oct 12 at 10:42

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.