Detach a remote database from a server

  • I am in an urgent need of some help

    I have a database named timetable on server1(domain 1) and have the same database timetable on server 2(domain2).

    Every Day i want to copy from server1 to server 2.

    These are the steps that i would like to follow unless someone could help me in an easier way.I want to write scripts on server 1 for everything.

    1. Detach database on server 1 and server 2

    2. Do a copy from server 1 to 2.

    3. Attach the database on Server 1 and server 2.

    I have done the following things

    1. Detach database on server 1.

    2. Did a copy of mdf file and ldf file to server 2(using xp_cmdshell and xcopy - but i had to use "net use command" to share)

    3. attach the database & ldf on server 1.

    Problem i am having is to detach database on server 2 because when i copy i get sharing violation. SO how can i detach the database on Server 2 from Server 1(Remotely).

    Thanks

    Liju

  • There are many ways to transfer a database from one server to another...

    1. Backup/Restore Method (recommended)

    2. Transfer Database Task in SSIS

    3. Copy Database Wizard

    4. Using SMO Objects

    I personally don't like using detach...attach method, 'cause you have to take the database offline and you have to do a few more steps than the backup/restore method.

    Problem i am having is to detach database on server 2 because when i copy i get sharing violation. SO how can i detach the database on Server 2 from Server 1(Remotely).

    Using SSMS, you can manage all the SQL Servers from your desktop. To detach, you have to do "Right Click on Database to Detach > Tasks > Detach".

    --Ramesh


  • Hi,

    Have you considered log shipping from Server A to Server B? You could schedule that to run in the early hours of the morning when things are quiet - much easier than having to detach/attach every day.

    Cath

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply