locked
SQL migrating Database from ONE Server to New Server RRS feed

  • Question

  • Hi Experts,

    Simple Questions : I want to move one database out of 2 to another new server....

    But the thing is....what is the stepwise process...

    Like stopping NODES of the ongoing server and copying or backing up.

    And what abt user, schema ..triggers..index....agent job...SSIS package replication.....again taking new server to Application....etc etc...

    Has anyone done this?

    Saturday, September 20, 2014 3:29 AM

Answers

All replies

  • You can find the steps here:

    How to move databases between computers that are running SQL Server

    --

    but for more info, please use follow links:

    Database

    The most simple way is:

    1. Backup database
    2. move backup file to new server 
    3. restore it

    more info: 

    Back Up and Restore of SQL Server Databases

    Second way is using Detach and Attach.

    more inf: 

    How to use Detach and Attach functions to move SQL Server databases

    Other way is using Copy Database Wizard.

    more info:

    Use the Copy Database Wizard

    SSIS packages:

    A Stored Procedure to Move SSIS Packages Between Servers

    Agent jobs:

    How-To Copy SQL Agent Jobs between servers with script


    Saeid Hasani [sqldevelop]


    • Edited by Saeid Hasani Saturday, September 20, 2014 5:10 AM
    • Proposed as answer by Shanky_621MVP Saturday, September 20, 2014 11:26 AM
    • Marked as answer by Charlie Liao Monday, September 29, 2014 10:24 AM
    Saturday, September 20, 2014 5:09 AM
  • thanks for the reply...and what about the nodes.....do we need to stop and start....or any oother replication
    Saturday, September 20, 2014 12:59 PM
  • Hi Prakhyan,

    According to your description above, you want to know the stepwise process to move one database from ongoing server to new server. Right?

    First make sure that you don't have any pending commands to replicate and no user connections while you are doing a cut-over to new server.

    Then follow steps below to migrate the database:

    1. Copy the database to new server. (You can use detaching and attaching, backup and restore, or other ways that Saeid Hasani has mentioned above. When the database is copied, all database level objects such as users, schemas, triggers and indexes will be copied too. )
    2. Copy the log-ins from existing environment to the new one. How to transfer logins and passwords between instances of SQL Server: http://support.microsoft.com/kb/246133
    3. Set up the replication/log shipping.
    4. Copy all additional objects from the existing environment, such as: jobs, DTS/SSIS packages, etc. (Please refer to the methods that Saeid Hasani has mentioned above)

    If you follow the above instructions, the databases in the new environment are synchronized and can be switched in seconds/minutes.

    If you have any question, please feel free to let me know.

    Regards,

    Donghui Li

    • Marked as answer by Charlie Liao Monday, September 29, 2014 10:24 AM
    Tuesday, September 23, 2014 11:24 AM