Re: [NTISP] MOVING SQL .DAT FILES

Dale E. Reed Jr. ( (no email) )
Thu, 04 Feb 1999 08:40:17 -0800

Jeff Binkley wrote:
>
> 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

Isn't that what I originally recommended when this thread started? :)

> Jeff Binkley
> ASA Network Computing
>
> -> Josh Hillman wrote:
> -> >
> -> > From: Dale E. Reed Jr. <daler@iea-software.com>
> -> > >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
> -> > hillman@talstar.com
> -> >
> -> > For more information about this list, including removal,
> -> > see this url: http://www.iea-software.com/maillist.html
>
> For more information about this list, including removal,
> see this url: http://www.iea-software.com/maillist.html

-- Dale E. Reed Jr.  (daler@iea-software.com)_________________________________________________________________       IEA Software, Inc.      |  RadiusNT, Emerald, and NT FAQs Internet Solutions for Today  |   http://www.iea-software.com

For more information about this list, including removal,see this url: http://www.iea-software.com/maillist.html