Mixing
SQL server database backup - Destination Disk - Adding multiple files - does it duplicate or split backup into the files?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
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.
sql-server backup split
asked 2 hours ago
variable
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
favorite
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.
sql-server backup split
asked 2 hours ago
variable
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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.
sql-server backup split
asked 2 hours ago
variable
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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.
sql-server backup split
sql-server backup split
asked 2 hours ago
variable
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
variable
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 2 hours ago
asked 2 hours ago
variable
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
variable
1498
asked 2 hours ago
variable
1498
1498
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
variable is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
add a comment |Â
4 Answers
4
active
oldest
votes
up vote
3
down vote
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 Options
We'll add a comment to inform other of what we were trying to achieve.
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.
The Backup Script
BACKUP DATABASE [StackExchange]
TO
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPStackExchange_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.
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.
Options Tab
Even this tab doesn't reveal which files will be used.
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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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.
answered 1 hour ago
hot2use
7,74151952
So is the link (book) misleading?
– variable
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
add a comment |Â
up vote
3
down vote
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:locationfile1.bak',
DISK = N'G:locationfile2.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:locationfile1.bak'
MIRROR TO DISK = N'G:locationfile2.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.
answered 1 hour ago
Tom V
13.3k74373
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
add a comment |Â
up vote
2
down vote
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.
answered 1 hour ago
George.Palacios
1,338618
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
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
58 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
add a comment |Â
up vote
1
down vote
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 :
- Just copying the taken backup to multiple disks.
- Taking multiple backups , but changing the path each time.
- 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 :
- It is incorrect as it will just create 4 sets of split backups.
- If the keyword 'Mirror" was used, it would have created 4 copies of backup media with Full,Diff and Log backup in it.
- 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.
answered 51 mins ago
Ramakant Dadhichi
936315
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
add a comment |Â
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
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 Options
We'll add a comment to inform other of what we were trying to achieve.
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.
The Backup Script
BACKUP DATABASE [StackExchange]
TO
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPStackExchange_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.
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.
Options Tab
Even this tab doesn't reveal which files will be used.
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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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.
answered 1 hour ago
hot2use
7,74151952
So is the link (book) misleading?
– variable
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
add a comment |Â
up vote
3
down vote
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 Options
We'll add a comment to inform other of what we were trying to achieve.
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.
The Backup Script
BACKUP DATABASE [StackExchange]
TO
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPStackExchange_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.
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.
Options Tab
Even this tab doesn't reveal which files will be used.
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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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.
answered 1 hour ago
hot2use
7,74151952
So is the link (book) misleading?
– variable
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
add a comment |Â
up vote
3
down vote
up vote
3
down vote
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 Options
We'll add a comment to inform other of what we were trying to achieve.
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.
The Backup Script
BACKUP DATABASE [StackExchange]
TO
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPStackExchange_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.
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.
Options Tab
Even this tab doesn't reveal which files will be used.
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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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.
answered 1 hour ago
hot2use
7,74151952
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 Options
We'll add a comment to inform other of what we were trying to achieve.
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.
The Backup Script
BACKUP DATABASE [StackExchange]
TO
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPStackExchange_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.
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.
Options Tab
Even this tab doesn't reveal which files will be used.
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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_2.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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:SQLBACKUPNB31710StackExchangeFULLStackExchange_FULL_20181012_105600_1.bak',
DISK = N'C:SQLBACKUPNB31710StackExchangeFULLStackExchange_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.
answered 1 hour ago
hot2use
7,74151952
answered 1 hour ago
hot2use
7,74151952
answered 1 hour ago
hot2use
7,74151952
answered 1 hour ago
hot2use
7,74151952
7,74151952
So is the link (book) misleading?
– variable
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
add a comment |Â
So is the link (book) misleading?
– variable
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
So is the link (book) misleading?
– variable
1 hour ago
So is the link (book) misleading?
– variable
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
Yes. It appears so, but I can't access the book.
– hot2use
1 hour ago
add a comment |Â
up vote
3
down vote
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:locationfile1.bak',
DISK = N'G:locationfile2.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:locationfile1.bak'
MIRROR TO DISK = N'G:locationfile2.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.
answered 1 hour ago
Tom V
13.3k74373
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
add a comment |Â
up vote
3
down vote
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:locationfile1.bak',
DISK = N'G:locationfile2.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:locationfile1.bak'
MIRROR TO DISK = N'G:locationfile2.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.
answered 1 hour ago
Tom V
13.3k74373
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
add a comment |Â
up vote
3
down vote
up vote
3
down vote
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:locationfile1.bak',
DISK = N'G:locationfile2.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:locationfile1.bak'
MIRROR TO DISK = N'G:locationfile2.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.
answered 1 hour ago
Tom V
13.3k74373
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:locationfile1.bak',
DISK = N'G:locationfile2.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:locationfile1.bak'
MIRROR TO DISK = N'G:locationfile2.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.
answered 1 hour ago
Tom V
13.3k74373
edited 1 hour ago
answered 1 hour ago
Tom V
13.3k74373
answered 1 hour ago
Tom V
13.3k74373
answered 1 hour ago
Tom V
13.3k74373
13.3k74373
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
add a comment |Â
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
So is the link (book) misleading?
– variable
1 hour ago
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
@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
1 hour ago
add a comment |Â
up vote
2
down vote
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.
answered 1 hour ago
George.Palacios
1,338618
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
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
58 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
add a comment |Â
up vote
2
down vote
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.
answered 1 hour ago
George.Palacios
1,338618
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
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
58 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
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.
answered 1 hour ago
George.Palacios
1,338618
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.
answered 1 hour ago
George.Palacios
1,338618
answered 1 hour ago
George.Palacios
1,338618
answered 1 hour ago
George.Palacios
1,338618
answered 1 hour ago
George.Palacios
1,338618
1,338618
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
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
58 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
add a comment |Â
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
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
58 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
So is the link (book) misleading?
– variable
1 hour ago
So is the link (book) misleading?
– variable
1 hour ago
@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
1 hour ago
@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
1 hour ago
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
58 mins ago
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
58 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
@variable in that case no - the diagram clearly shows that the separate backup files are striped with the same backup sets.
– George.Palacios
42 mins ago
add a comment |Â
up vote
1
down vote
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 :
- Just copying the taken backup to multiple disks.
- Taking multiple backups , but changing the path each time.
- 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 :
- It is incorrect as it will just create 4 sets of split backups.
- If the keyword 'Mirror" was used, it would have created 4 copies of backup media with Full,Diff and Log backup in it.
- 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.
answered 51 mins ago
Ramakant Dadhichi
936315
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
add a comment |Â
up vote
1
down vote
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 :
- Just copying the taken backup to multiple disks.
- Taking multiple backups , but changing the path each time.
- 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 :
- It is incorrect as it will just create 4 sets of split backups.
- If the keyword 'Mirror" was used, it would have created 4 copies of backup media with Full,Diff and Log backup in it.
- 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.
answered 51 mins ago
Ramakant Dadhichi
936315
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
add a comment |Â
up vote
1
down vote
up vote
1
down vote
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 :
- Just copying the taken backup to multiple disks.
- Taking multiple backups , but changing the path each time.
- 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 :
- It is incorrect as it will just create 4 sets of split backups.
- If the keyword 'Mirror" was used, it would have created 4 copies of backup media with Full,Diff and Log backup in it.
- 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.
answered 51 mins ago
Ramakant Dadhichi
936315
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 :
- Just copying the taken backup to multiple disks.
- Taking multiple backups , but changing the path each time.
- 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 :
- It is incorrect as it will just create 4 sets of split backups.
- If the keyword 'Mirror" was used, it would have created 4 copies of backup media with Full,Diff and Log backup in it.
- 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.
answered 51 mins ago
Ramakant Dadhichi
936315
edited 12 mins ago
answered 51 mins ago
Ramakant Dadhichi
936315
answered 51 mins ago
Ramakant Dadhichi
936315
answered 51 mins ago
Ramakant Dadhichi
936315
936315
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
add a comment |Â
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
I was confused reading book (see the image on the next page), it appeared like it would duplicate books.google.co.in/…
– variable
48 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
Edited the answer as per the link after testing it.
– Ramakant Dadhichi
11 mins ago
add a comment |Â
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
variable is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219936%2fsql-server-database-backup-destination-disk-adding-multiple-files-does-it%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
