In SQL Server 2005, via the GUI, I wish to backup a database to an additional disk file (there is already an existing backup disk file for this database), so that I can have more than one backup. I've added the new disk file name, highlighted it, and clicked OK.
I get an immediate error (see below). Note, the 2nd error message is specifying the existing backup disk file, not the new one I'm attempting to create.
"Backup failed for Server 'WCS-DEV-TPA'. (Microsoft.SqlServer.Smo)"
"System.Data.SqlClient.SqlError: The volume on device 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\WCS_ADV_Longmont.bak' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. (Microsoft.SqlServer.Smo)"
Does anyone know what causes this and how to correct it?
Try removing all but the new disk file name. Also you can just copy the first backup(s) to another drive.|||While that would be a good workaround, it won't meet my needs.
(a) The users will need to make multiple backups and retain several older versions in order to be able to restore a specific version if necessary. (b) The users who will be making the backups will not have access to the file system on the computer that will host the backup disk files.
Is there a way to correct this problem so that we can have multiple disk backups via the GUI? (The users in question won't be knowledgeable (or privileged) enough to create SQL scripts to do the backups on this 'ad-hoc' basis.)
|||From another post:
You can use the UI to create a Maintenance Plan(s) with multiple backups. If these are adhoc backups they'll have to be separately.
Kevin Farlee
Posts 224
Was this post helpful ?
When you back up to two files, you are creating a stripe set. The restriction is that all backups sent to a media set must have the same number of stripes. That's the meaning of your error.
You need to create a media set with the number of stripes you want to use. You can't add members later.
The method for creating a new media set is to use the WITH FORMAT option on your backup command. This is the equivalent of reformatting a tape for backups. It wipes out any previous data in the file(s), and sets up the headers correctly.
So, if you issue the same command in your script, adding WITH FORMAT for ONE TIME ONLY, the first time you use that media family, you should be good to go. From then on, you can just use it as your script is now.
Microsoft SQL Server Storage Engine PM
[1] This is for use in ad-hoc backups and need to be done by fairly inexperienced users as desired before running application software that will modify the database.
[2] We don't want to backup the database to a multiple disk file set - we want to be able to specify a new disk file to receive the database backup, giving us the capability of seeing multiple versions of the database (based on the backup snapshot in each disk file) and restoring (if necessary) from any one of the backup disk files. Doing this through the GUI is necessary because of this will be done with less privileged and knowledgeable users.
|||Sorry I can't be of more help.
The only solution I can see is for your users to use the UI to back up to only one "location" at a time. They will just have to use the UI to twice, each time backing up to one location.
|||Does this mean that the problem I'm reporting is a known flaw (bug?) in the SQL Server management GUI? It can't be corrected to allow the specification of several backup disk files?|||I would just say that the Backup Database dialog only allows for one backup at a time to either a single file or device or a multiple family media set. It has to be used two times for two backups.
You can use the Maintenance Plan UI to create a plan with two backup tasks, to two different folders, that run in series and you can schedule that plan.
The UI for the Maintenance Plan - Backup Database Task is similiar to the Backup Database dialog.:
|||
OK, I see. It looks like we'll have to re-evaluate how the permissions will be allocated to users in consideration of this. Thank you (all of y'all) for your help.
Regards,
Douglas
|||Help me understand more about what you're trying to accomplish:
Are you trying to get two files, each containing exactly the same backup content?
or are you wanting to have a collection of backups of the same database at different times?
If you want the former, you want to use the MIRROR TO clause in the Backup command. I can't find a button on the GUI for it, but if you set up a backup, and specify two files, then click on the "script" button, you'll get a backup command with two disk files separated by a comma. i.e.
BACKUP DATABASE foo TO DISK = N'C:\somefile.bak', DISK = N'D:\AnotherFile.BAK' WITH FORMAT;
if you replace the comma with 'MIRROR TO', as in
BACKUP DATABASE foo TO DISK = N'C:\somefile.bak' MIRROR TO DISK = N'AnotherFile.BAK' WITH FORMAT;
then hit "GO" you'll get two copies of a backup of database foo instead of a stripe set.
|||We're wanting to "have a collection of backups of the same database at different times".
