Re: [NTISP] Moving SQL .dat files

Josh Hillman ( (no email) )
Wed, 3 Feb 1999 10:44:46 -0500

>Josh Hillman wrote:
>> Is it possible to relocate/move the .dat files associated with a SQL
>> device/database? I just realized that one of our databases is sitting in
>> the root of C on the machine and it belongs in S. How can I move the
>> files and have MSSQL Server (6.5 SP4) be aware of it?

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
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: