Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to make a usable copy of an SQL Server database on Windows Server 2003 for testing purposes. There is a backup tool built into Enterprise Manager but I can't seem to make it work.

I've tried "Right-click on database -> All Tasks -> Add... -> Enter a new name D:\Whatever\ 20110803.bak -> OK

but got the message

Microsoft SQL-DMO (OCBD SQLSTATE:42000)

The volume on device "D:\whatever\ preexistant.bak is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.

BACKUP DATABASE is terminating abnormally | OK

Q: What is a multiple family media set and why do I need the old existing backup file to be a multiple family media set if I'm creating a new backup?

Q: What is BACKUP WITH FORMAT? I'm pretty sure I don't want to format anything. Again, I'm trying to create a new backup file.

I click OK and see that a new destination has appeared in the Destination list, so I Select it and click OK.

I get the following message:

Microsoft SQL-DMO (ODBC SQLState: 42000)

Cannot open backup device "D:\whatever\ preexistant.bak. Device error or device off-line. See the SQL Server error log for more details.

BACKUP DATABASE is terminating abnormally. | OK

Q: Wherer are whese error logs?

share|improve this question
Can you take it off line? – KCotreau Aug 4 '11 at 3:02
Probably should go to SF. – surfasb Aug 4 '11 at 3:03
Yes I can "Detach" it and copy the ".mdf" file. Doest that copy everything ? (users, groups, permissions, configuration, etc). Or are you saying I should make a backup while the database is detached? – Shawn Aug 4 '11 at 3:10
up vote 2 down vote accepted

I would just detach it, and copy both the .mdf and .ldf to a new location. I do that all the time. When it is detached, they are just like regular files. I do it this way because it is A LOT faster.

If I can't take it off-line, I usually just use my backup software (Backup Exec usually) to make a new copy by redirecting the restore to a new location (MAKE SURE YOU DO NOT OVERWRITE THE ORIGINAL LOCATION).

share|improve this answer
Why do you also copy the .ldf file? What does it do? – Shawn Aug 4 '11 at 13:33
@Shawn It is the transaction file...In other words, anything that has changed, but has not been committed to the database. You can probably do it without that, but I am just used to copying both to a new location, and then re-attaching. – KCotreau Aug 4 '11 at 15:19

Rather than explaining the UI, it is probably easier to just open a query window and use SQL:

    backup database DatabaseName
    to disk = 'path-of-backup=file'
    with format, name='backup-name', copy_only

To restore to new DB:

  • Right click on the databases node in SQL Management Studio's Object Explorer and select Restore.
  • Enter the name of the new database in the "To database"
  • Select "From Device" and use the "..." button to add the backup file and return to main dialogue.
  • Select the backup to restore from in the list box (probably only one)

If you need to do this often, I would use the "script" option on the restore dialogue to generate the SQL, which is much quicker to re-use.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .