Sql-server – SQL server database backup – Destination Disk – Adding multiple files – does it duplicate or split backup into the files

backupsplitsql server

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.

Best Answer

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.

Related Question