Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.