SQL server - 'cold' database clone, was this ok?

[andyjgw]

Hi

I was in the situation of cloning a SQL server database last night.
Today, a dependent application was behaving a little erratically though
most things were fine after sorting out some ntfs permissions. I've
recovered this database from backup exec before from a hot-backup, this
is the first time I've attempted this procedure though.

Could I check with the experts here as to whether what I did was OK?
Been googling to see if it is really the right thing to do, haven't
come up with anything definitive...

Assume server SOURCE and DEST:

install DEST to same specification, OS, SQL patch level as SOURCE. Same
paths where applicable.

shut down SQL server on both

Copy all datafile folder (d:\sqldata) which is the full database
including MASTER etc all over to DEST.

Shut SOURCE down.

Rename DEST to SOURCE, disable SQL server starting, set network
settings to those of SOURCE and reboot.

Wait for things to settle after reboot and name change, bring SQL
database up.

Client application now connects, I can't see any problems, but there
have been some strange anomalies in satellite applications today. All
have been bounced to see if it's just a change they needed to notice,
to no avail.

Was that an OK operation to have done, or is there something just about
to bite me when I'm not looking?

Be grateful for any advice, I spent today pulling a lot of hair out.

Thanks in advance :)

AW

[Erland Sommarskog]

(an*****@gmail.com) writes:

Client application now connects, I can't see any problems, but there
have been some strange anomalies in satellite applications today. All
have been bounced to see if it's just a change they needed to notice,
to no avail.

Was that an OK operation to have done, or is there something just about
to bite me when I'm not looking?

This might not really the right way to do it. Then again it sounds
sound to me. Without knowing about the anomalies, it's difficult to
say what might be wrong.

One thing to check though is @@servername. If it's wrong, drop the
name with sp_dropserver. If NULL, add it with sp_addserver with the
"local" parameter. See Books Online for details.

This KB article may be helpful:
http://support.microsoft.com/default...b;EN-US;224071
(although you probably should have seen it earlier!)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[andyjgw]

Thanks for that Erland.

@@servername is fine, though I'd suspect it may be dynamic enough to
query the machine rather than a value stored in the DB somewhere?

I had seen that page, but detaching/re-attaching databases was
something I had decided against as I would have had to follow the
procedures for exporting/recreating all the user accounts (among other
things). With my (ssshhh!) Oracle hat on, this is a perfectly OK
procedure and I must confess that is what drove me to take this
solution.

Things seem a little more stable today, save for one application which
is still odd. I'm in the process of installing it clean to a virtual
server to see if I can isolate the problem.

Thanks for the advice.

AW

Erland Sommarskog wrote:

(an*****@gmail.com) writes:

Client application now connects, I can't see any problems, but there
have been some strange anomalies in satellite applications today. All
have been bounced to see if it's just a change they needed to notice,
to no avail.

Was that an OK operation to have done, or is there something just about
to bite me when I'm not looking?

This might not really the right way to do it. Then again it sounds
sound to me. Without knowing about the anomalies, it's difficult to
say what might be wrong.

One thing to check though is @@servername. If it's wrong, drop the
name with sp_dropserver. If NULL, add it with sp_addserver with the
"local" parameter. See Books Online for details.

This KB article may be helpful:
http://support.microsoft.com/default...b;EN-US;224071
(although you probably should have seen it earlier!)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(an*****@gmail.com) writes:

@@servername is fine, though I'd suspect it may be dynamic enough to
query the machine rather than a value stored in the DB somewhere?

No, I think it digs it out of the registry at startup. It's not
an uncommon problem that @@servername gets messed up, if you rename
the Windows name, or move the installation from one box to another
box with a different name.

Also, reading it from Windows wouldn't do on a cluster, and then you
have named instances where the servername is XXX\INSTANCE_NAME.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp