Moving MSDE/SQL using ghost or changing the servers name.

[Vetran]

Has anyone managed to move MSDE (SQL server) using a cloning technology such as ghost.


I'm told by M$ its going to be difficult, the system tables store the computer name. Any help appreciated.

[DimPrawn]

Re: Moving MSDE/SQL using ghost or changing the servers name

SQL Server does not like having the machine name changed.

If you change the machine name you will have to rerun the SQL Server setup in order to get the database server up and running again.

Ghost is NOT the way to move databases from one machine to another. Try backup/restore or detach/attach.

[AtW]

Re: Moving MSDE/SQL using ghost or changing the servers name

oh dear - great "feature" :lol

btw talking of MSDE - they have 2Gig restriction on database sizes :x

[DimPrawn]

Re: Moving MSDE/SQL using ghost or changing the servers name

Atw,

Yes, but you can have as many databases as you like and the limit is 2GB per database (the full blown SQL Server does not have a limit).

So if you think your DB is going to grow beyond 2GB (2GB is quite a lot of records by the way...), partition your schema across multiple databases and join tables using fully qualified name.

e.g. MyDB1.dbo.Table1 inner join MyDB2.dbo.Table2

Does this help?

DP.

[AtW]

Re: Moving MSDE/SQL using ghost or changing the servers name

> (2GB is quite a lot of records by the way...),

I did not ask just because of academical interest
My area of work is datamining - so lots and lots of rows and always have to make compromises (ie look at short period of time)

yeah I know - can create view that will select data from multiple databases as if it was one logical table. I am concerned on efficient use of indices though - I generally avoid views so I do not know their side-effects well. Any experience to share?

Edit: thinking of it now, it may require some application side logic to ensure that clustered index is efficient. hmmm

cheers

[Vetran]

Re: Moving MSDE/SQL using ghost or changing the servers name

OK thanks DP, I feared that was the case as AtW say's 'great feature' anyone who has ghost or partition magic's enterprise stuff will know how much time it saves.

Nice of M$ to make it easy to roll it out to a few hundred people and get regular backups. - class, now a real DB like Oracle comes across like a little lamb if you tickle the right bits.

[Cojak]

Re: Moving MSDE/SQL using ghost or changing the servers name

Ooohhh... now I know why my PC guru doesn't set up my HP training machines using ghost.

What a pain the @rse.

I'll just have to grin and put up with training on a shared database in future...

[DimPrawn]

Re: Moving MSDE/SQL using ghost or changing the servers name

Out of curiosity guys, are you talking about SQL Server 7 or 2000?

For SQL 2000, you only need to run (SQL):

sp_dropserver old_name
GO
sp_addserver new_name, local
GO

As SQL 2K recognises the machine name change at startup.

For SQL 7, It's a bit more involved:

Run SQL Server 7.0 Setup from the original product CD.

Doing so will not reinstall SQL Server or any components; however, it will update SQL Server 7.0 internally to reflect the new Windows computer name. You also need to update SQL Server's internal servername by running the following two stored procedures:

sp_dropserver <old_name>
go
sp_addserver <newname>, local
go
&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp
If SQL Server is configured to listen on either the Multiprotocol or NWLink IPX/SPX network libraries, you'll need to remove them, and then add them back using the SQL Server Network Utility.


Hope this helps.

[Vetran]

Re: Moving MSDE/SQL using ghost or changing the servers name

its MSDE (2 I think) so that is SQL2000 cut down. Do you do that

1/before changing the name ?
2/ Before rebooting ?
3/ after changing name & rebooting?

are there any system tables that need to change?

how can I fully check its working properly (obviously I can attach and do a query but would that be enough?)

(SQL server isn't my choice, I had it 'thrust upon me')

[DimPrawn]

Re: Moving MSDE/SQL using ghost or changing the servers name

3