How can I make a usable copy of an SQL Server database on Windows Server 2003?

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?

Shawn

Posted 2011-08-04T02:29:31.950

Reputation: 1 219

Can you take it off line? – KCotreau – 2011-08-04T03:02:34.843

Probably should go to SF. – surfasb – 2011-08-04T03:03:01.287

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 – 2011-08-04T03:10:35.003

Answers

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

KCotreau

Posted 2011-08-04T02:29:31.950

Reputation: 24 985

Why do you also copy the .ldf file? What does it do? – Shawn – 2011-08-04T13:33:06.017

@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 – 2011-08-04T15:19:41.643

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.

Richard

Posted 2011-08-04T02:29:31.950

Reputation: 8 152