Sensible way to duplicate a DB on remote server

[hanchilicious]

Hi,

I have a SQL 2000 server and another server in a remote location.

What is the most sensible way to duplicate the database on the local server and send it to the remote server?

At the moment, I'm sharing the directory with the local backup, and mapping from the remote server and restoring from there.

Is this the most sensible way to replicate the db on a remote server?

Thanks

 

[hanchilicious]

What I'm finding when I attempt to migrate the database this way, is that I'm looking at an 88GB file in the shared directory when looking from the local server.

But when I look from the remote server, the same file in the same directory shows up as 4GB, and is the only file that cannot be read from the share...

 

[hanchilicious]

(Sorry for all the posts)...

I'm sure the DTS should be a better way to go, but I have no idea how to get it to recognise a remote SQL Server as a datasource...

 

[philhege]

Have you tried the IP address?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[hanchilicious]

I have, but the destination IP doesn't get recognised by the DTS wizard.

 

[philhege]

Can you raise the server by IP in Query Analyzer?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[jbenson001]

Try to ping the server using the IP address to see if you can reach it.

 

[hanchilicious]

I can ping the server okay. But if I try to add the server's IP in the Client Network Utility and register it as a server group in Enterprise Manager, connection to the server is unsuccessful.

 

[philhege]

I think this is a matter of trust.

If the servers are registered to domains, is there a trust between the domains?

This isn't technical knowledge talking, but I don't think that you can willy-nilly connect to SQL anymore, post-Slammer.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[ESquared]

BACKUP DATABASE [MyDatabase] TO DISK = 'C:\MyDatabaseBackup.dat'

--copy the file to new server

RESTORE DATABASE [MyDatabase] FROM DISK = 'C:\MyDatabaseBackup.dat'

It's that easy.

You might look at DBCC SHRINKDATABASE also...

If you need to change the database name just change it in the restore database command.

If you need to change the actual .mdf and .ldf filenames on disk,

RESTORE DATABASE MyDatabase
   FROM DISK = 'C:\MyDatabase.dat'
   WITH RECOVERY,
   MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseNewName.mdf', 
   MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseNewName_log.ldf'

If you need to change the logical file names after restoring,

ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase, NEWNAME = MyDatabaseNewName)
ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase_log, NEWNAME = MyDatabaseNewName_log)

 

[hanchilicious]

Thanks for your help guys, particularly ESquared.

I tried using sp_detatch_db/sp_attach_db with little joy, and finally used ESquared's approach, which worked.

I was mapping the target machine back to the source to pick up the backup, and I guess a key point to remember is that when mapping between machines in this manner both systems should be running under admin accounts (not local systems accounts), and the mapping should be identified by the machine name/ip and not the drive letter.

Thanks again.

 

[ESquared]

Attaching and detaching is not the recommended way to back up. There's a greater chance of running into problems such as having the database start up in suspect mode.

 

[ESquared]

Oh... for what it's worth I have no idea what the file extension should be for sql server database backup files. At work they use blank extensions. Using .Dat is probably misleading, but I don't know what else to call it. bak? DBB for database backup?

Anyone?