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:
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.
Well let's just go ahead and test it...
Added multiple file in the Backup Properties' General Tab
We'll add a comment to inform other of what we were trying to achieve.
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.
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
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).
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.
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.
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.
Even this tab doesn't reveal which files will be used.
Let's do the same as before an script out what we have clicked together.
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
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.
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.
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.
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 :
As per the link you shared :
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.
