I did this with one of our other SQL databases (unrelated to Emerald) this
past weekend because it was originally created on the wrong partition and I
didn't realize it until a while later.
Because you're talking about the Emerald database here, you've got to be
real careful with it, so read the instructions below a few times before
attempting to do anything. Also, as much as you're going to hate this, do
it in the middle of the night when your usage is at a minimum and when no
one is going to be messing around with your Emerald database.
I don't know if this is the "easiest" way of doing it, but here is the
procedure I took to do it on one of our other databases (everything worked
flawlessly):
Nutshell sequence of events:
----------------------------
Transfer original database to a temporary database (under different name),
then delete the original database, then transfer the temporary one to the
new location using the same name as the original.
Emerald (orig) --> TEMP_Emerald --> Emerald (new)
Drive 1 (anywhere) Drive 2
Example existing/original database:
-----------------------------------
Emerald (device) 100 MB Drive C
EmeraldLogs (device) 50 MB Drive C
Create a new temporary DEVICE:
------------------------------
TEMP_Emerald 100 MB (put it anywhere where you have room)
(make sure that the TEMP_Emerald device has enough capacity)
TEMP_EmeraldLogs 50 MB (put it anywhere where you have room)
Create a new temporary database on the temporary devices:
---------------------------------------------------------
TEMP_Emerald
data --> TEMP_Emerald device, using all allocated space
logs --> TEMP_EmeraldLogs device, using all allocated space
Change a few options on the temporary database:
-----------------------------------------------
Double-click on the TEMP_Emerald database. Click on Options. Check the
following:
Select into / Bulk copy
No checkpoint on recovery
Truncate Log on checkpoint
Transfer (make a copy of) the database into the temp one:
---------------------------------------------------------
Right-click on Emerald and choose Transfer.
Source:
Source Server: servername
Source Database: Emerald
Desitantion Server: servername
Destination Database: TEMP_Emerald
Leave all other options alone.
Start Transfer (this takes a while)
When the transfer is complete, if there were no problems, you will still get
a message telling you to check your logs for one reason or another. Click
on "View Logs" and you'll see a bunch of messages mentioning "null
password". Those are normal.
*** If you get any other error messages (aside from the null
*** password messages), do NOT continue past this point!
*** Your original Emerald database has NOT been altered in
*** any way, so it'll continue operating as it has been.
Now you have a copy of your database.
At this point, I'd stop the MSSQLServer service then COPY the
\mssql\data\*.dat files (including master.dat) to a safe place on the same
machine just in case you need to restore the originals (I had to do this 2
years ago after my Emerald database got messed up and regular SQL restores
didn't work). Do NOT cancel/interrupt the file transfer.
Restart the MSSQLServer service.
Delete the Emerald (original) database and device:
--------------------------------------------------
Right-click on the Emerald (original) database and choose Delete.
Right-click on the Emerald (original) device and choose Delete.
If you have a logs device for the original Emerald that is currently needing
to be moved also, then delete that device as well.
Create the NEW Emerald device:
------------------------------
Emerald 100 MB (put it where you want it)
(make sure that the Emerald device has enough capacity)
EmeraldLogs 50 MB (put it where you want it)
Create the (new) Emerald database on the (new) Emerald device:
--------------------------------------------------------------
Emerald
data --> Emerald device, using all allocated space
logs --> EmeraldLogs device, using all allocated space
Change a few options on the new database:
-----------------------------------------
Double-click on the Emerald database. Click on Options. Check the
following:
Select into / Bulk copy
No checkpoint on recovery
Truncate Log on checkpoint
Transfer (make a copy of) the TEMP database into the NEW one:
-------------------------------------------------------------
Right-click on Emerald and choose Transfer.
Source:
Source Server: servername
Source Database: TEMP_Emerald
Desitantion Server: servername
Destination Database: Emerald
Leave all other options alone.
Start Transfer (this takes a while)
When the transfer is complete, if there were no problems, you will still get
a message telling you to check your logs for one reason or another. Click
on "View Logs" and you'll see a bunch of messages mentioning "null
password". Those are normal. Because this is the same server, the
logins/passwords shouldn't have been affected at all, eventhough the
messages make it seem as if there are no passwords now.
Your Emerald database should now be transferred.
Delete the temporary Emerald database and devices:
--------------------------------------------------
Right-click on the TEMP_Emerald database and choose Delete.
Right-click on the TEMP_Emerald device and choose Delete.
Right-click on the TEMP_EmeraldLogs device and choose Delete.
Delete the TEMP_Emerald.dat file from whereever you created the temporary
device.
Delete the TEMP_EmeraldLogs.dat file from whereever you created the
temporary device.
If everything is working correctly:
-----------------------------------
Delete (or move) the ORIGINAL Emerald.dat file (the one that was on the
drive that you were trying to move it FROM). Your "current" Emerald files
are in their new location.
If anyone is/was dialed-up or is trying to log on while you were doing this,
you're going to "not gain" and/or potentially lose a few Calls table records
(for the timespan that you're doing the transfers). Because of this, you
may need to go into Emerald and view the users who are currently online.
Compare the list in Emerald to the users actually logged onto your Maxes.
You can finger them from a command prompt (finger @max3.whatever.com) or you
can telnet into them and go into Terminal Server mode (hit ^d then e and
issue the "show users" command at the Max terminal server prompt). If you
see any users listed in Emerald that are not listed on your Maxes, highlight
that user and hit the Clear button. This indicates that your new database
doesn't have the Stop record sent by the Max sometime during the transfer.
You may need to restart RadiusNT and Serv-U FTP server (if you're using it).
Josh Hillman
hillman@talstar.com