Jeff Binkley ( )
Thu, 04 Feb 1999 06:58:00 -0500

A more correct way to do this is by the following:

1. Backup the database(s) on the .dat file (i.e. device) using
a disk or tape dump.
2. Dump the DDL for the databases(s) and device or just make sure you write
down all of the settings.
3. Make sure no applications are using the database(s) on the .dat file
4. Drop the database(s) .
5. Drop the device
6. Stop and restart SQL (not a requirement but doesn't hurt)
7. Rebuild the device in the new location
8. Rebuild the database(s) onto the device
9. Load the diskdumps back into the newly built databases

Jeff Binkley
ASA Network Computing

-> Josh Hillman wrote:
-> >
-> > From: Dale E. Reed Jr. <>
-> > >I'm not sure. You could do a dump of the DB, drop the DB and devices, >
-> >delete the old files, re-create the new devices and db, then restore > >the
-> DB. Not pretty, but would work.
-> >
-> > I don't know why I didn't think of this before, but won't the following
-> also > work?:
-> I don't like trying to "trick" SQL Server. The below might work, but I
-> can't recommend it.
-> > Stop SQL Server.
-> > Copy the appropriate .dat files from C: to a temporary location. > Start
-> SQL Server.
-> > Delete the database and device.
-> > Create a new device and datbase with the same info, except
-> > for the physical location (changing it to S:)
-> > Stop SQL Server.
-> > Replace the newly created .dat files in S: with the original
-> > .dat files.
-> > Start SQL Server.
-> >
-> > 2 years ago, our Emerald datbase got corrupted and I had to restore the >
-> actual .dat files off of tape (the diskdump restores didn't work; forget >
-> why). Obviously, the only way to get those .dat files on tape to begin with
-> > was by shutting off SQL for a few minutes, while making copies of the .dat
-> > files so that they'd be picked up in the tape-backup since the real ones
-> are > always in use. Replacing the .dat files worked (although obviously,
-> some > data had to be re-entered because of the timing). The difference
-> here was > that the Emerald.dat file was restored to the same location that
-> it was to > begin with.
-> >
-> > Josh Hillman
-> >
-> >
-> > For more information about this list, including removal,
-> > see this url:

For more information about this list, including removal,
see this url: