How to move a SQL Server Database from One Server to another server

[sierrasoft]

How to move (or Duplicate) a Microsoft SQL Server Database from One Physical Server to another server (like backup) in vb.net 2.0 ?

[4advanced]

There are a few options but one of the simplest is to detach the database from the server, copy the phisical files to the new dataserver's datapath and attach it to the new dataserver.

For those actions you have to import Microsoft.SqlServer.Management.Smo.SmoApplication and use the EnumAvailableSqlServers() to find the available sql servers (from MSDE2000 up to SQL2008).

Found the correct server, you can call sp_attach_db on an active connection and include the correct physical-filenames (for the mdf & ldf file) to attach it.....

Regards,

Richard
The Netherlands

[samir_ibrahim]

How to move (or Duplicate) a Microsoft SQL Server Database from One Physical Server to another server (like backup) in vb.net 2.0 ?

First, Move is totally different from Duplicate
Move is deleting DB from first server and put it is second server
Duplicate is both server having the same DB
So,
First you choose which one you want (Move or Duplicate)
Second, you want to do that by vb.net coding or manual?

[Comatose]

When dealing with servers (which are usually important to the company) the safest way to move the database, is to duplicate it first.... then later remove it from the source. So while you are right, moving and duplicating are different.... would you think it is wise from an administration point of view to move the data? No. You duplicate the data... then if everything goes well, you delete the old data. So, assuming we are people who have server administration experience (could be a big assumption), I'd say it's a safe bet to not even talk about the act of "moving" data directly.........

[samir_ibrahim]

Hi Comatose

When dealing with servers (which are usually important to the company) the safest way to move the database, is to duplicate it first....

That is not necessary.
De-Attach and Attach are safe method and you can do it with no fear of data lost unless the database you want to move has a replication.

then later remove it from the source. So while you are right, moving and duplicating are different.... would you think it is wise from an administration point of view to move the data? No. You duplicate the data... then if everything goes well, you delete the old data.

I agree, always take a backup before doing such thing

There is three kind of copying/duplicating the DB from one server to another. Backup/Restore,De-attach/Attach, Copy Directly from source to destination.
Each one has it is own characteristics and depends on the situation(error in replication, server hardware failure, etc..) he should choose which one he should use.

So, assuming we are people who have server administration experience (could be a big assumption), I'd say it's a safe bet to not even talk about the act of "moving" data directly.........

I have Microsoft certificate in MSSQL Server Admin in 2003 ad I have 8 years of experience in SQL Server Administrator :)