1

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?

3
  • Can you take it off line?
    – KCotreau
    Aug 4, 2011 at 3:02
  • Probably should go to SF.
    – surfasb
    Aug 4, 2011 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, 2011 at 3:10

2 Answers 2

2

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).

2
  • Why do you also copy the .ldf file? What does it do?
    – Shawn
    Aug 4, 2011 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, 2011 at 15:19
0

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.

You must log in to answer this question.

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