I want to move a database server to server without using Backup and restore?

  • I want to move a database server to server without using Backup and restore?

    Example:

    I am having a XXX database in ABC server, I want to move this database to XYZ server which do not have the XXX database.

  • What is wrong with backup and restore? It by far the easiest and safest way to do this.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • You can detach and attach. I would not proceed down that path without having a good backup though. I like to proceed cautiously and make sure I have an option for recovery.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • AAKR (7/5/2012)


    I want to move a database server to server without using Backup and restore?

    Example:

    I am having a XXX database in ABC server, I want to move this database to XYZ server which do not have the XXX database.

    the easiest way is to

    take the database offline

    copy (don't move) the files to the new server

    Bring original database online

    attach the db files on the target server

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • SQLRNNR (7/5/2012)


    You can detach and attach. I would not proceed down that path without having a good backup though. I like to proceed cautiously and make sure I have an option for recovery.

    The backup piece of this was pretty much my point. You need to backup your database first. This is usually the reason this type of request comes up because the backup process can cause blocking and such. Since this has to be done first anyway you really don't gain much at that point by copying the files and attaching. You might as well just copy the bak file and restore.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (7/5/2012)


    SQLRNNR (7/5/2012)


    You can detach and attach. I would not proceed down that path without having a good backup though. I like to proceed cautiously and make sure I have an option for recovery.

    The backup piece of this was pretty much my point. You need to backup your database first. This is usually the reason this type of request comes up because the backup process can cause blocking and such. Since this has to be done first anyway you really don't gain much at that point by copying the files and attaching. You might as well just copy the bak file and restore.

    I didn't see your post. Our posts hit simultaneously 😉

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thank you all of you guys.

    * copy files from server to server it takes more time...

    I heard an option with using of Sanpshot replication we can create the database.

    any one of you know this option how to we work it out

  • AAKR (7/5/2012)


    Thank you all of you guys.

    * copy files from server to server it takes more time...

    I heard an option with using of Sanpshot replication we can create the database.

    any one of you know this option how to we work it out

    Do you actually want to move the database or just copy it? Is this a "one off" or do you need to do this on a regular basis? Are both systems on the same SAN by any chance?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • AAKR (7/5/2012)


    * copy files from server to server it takes more time...

    Yes it does, but if you want to move or copy a DB from one server to another, there's just about no way to get around the fact that you are going to have to copy the data over. Whether that's in a backup file, the database files or snapshot replication's snapshot files, the data has to move.

    Safest and simplest - backup the DB, copy the backup, restore on other server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • AAKR (7/5/2012)


    Thank you all of you guys.

    * copy files from server to server it takes more time...

    I heard an option with using of Sanpshot replication we can create the database.

    any one of you know this option how to we work it out

    What is it you are trying to accomplish? If you can outline the goal and the requirements, I am sure we can come up with alternatives that would meet the requirements.

    You asked about SAN snapshot - which is an option if your volumes were set up that way to begin with and will be dependent on the SAN you are using. But, that may not be the best option - again, depending on the requirements.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You're generally stuck with 2 options mentioned.

    Option1

    Detach or offline database (generally almost instantaneous)

    Copy files (takes time)

    Reattach files (generally almost instantaneous)

    Option2

    Take compressed backup (can take time)

    Copy compressed back (usually quick)

    Restore backup (generally takes same time as backup)

    Depending upon your database size and network speed one may be quicker than the other.

    In my experience on a fast network detach\attach is usually quicker. But depends on what state you want source db to be left in.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • What about taking compressed backup over network?

  • Daxesh Patel (7/5/2012)


    What about taking compressed backup over network?

    If the network is stable, it would work. I personally prefer to do backups to a local resource as even a minor network issue could cause a backup failure. If you have a good fast network, however, I would consider doing the restore of a compressed backup over the network. SQL Server seems to be more tolerant of minor network issues during a restore than a backup.

  • SQL Server seems to be more tolerant of minor network issues during a restore than a backup.

    100% agree, thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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