locked
How to move database from one server to another RRS feed

  • Question

  • Hi,

    My system is having less disk space. I want to move some databases from that server to another. Can I move databases from one server to another and I do not want to change the connection string.

    Sunday, January 8, 2012 10:59 AM

Answers

  • Hello,

    The connection string contains the server name (or ip), so if you move the database you have to change the connection string as well.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Marked as answer by Vinay05 Monday, January 9, 2012 10:48 AM
    Sunday, January 8, 2012 11:05 AM

All replies

  • Hello,

    The connection string contains the server name (or ip), so if you move the database you have to change the connection string as well.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Marked as answer by Vinay05 Monday, January 9, 2012 10:48 AM
    Sunday, January 8, 2012 11:05 AM
  • Sometimes people create aliases to the server and reference it with in a connection string..... 

    http://msdn.microsoft.com/en-us/library/ms190445.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, January 8, 2012 11:43 AM
    Answerer
  • Here are some additional things you want to consder when moving a database between two SQL Servers: http://www.karaszi.com/SQLServer/info_moving_database.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    • Proposed as answer by Horizon_Net Sunday, January 8, 2012 6:22 PM
    Sunday, January 8, 2012 2:12 PM
  • Is that your production server or test server or other.

    If production, you should never encounter such scenario as this is a very vital thing, you should consider this and talk to your administrators/management.  However if it's dev/test/other database, you can move the database with the ideas proposed considering the scenario.

    You should change the connection string. either use alias or change machine name/IP.

    Ensure, you consider collation of the other instance. I have run through the same problem but fortunately for me it was just a Query only database.

    Sunday, January 8, 2012 5:55 PM
  • Is it not possible to Detach the database and move it to some shared location of another server. And then attach the database giving the path of that shared location.
    Monday, January 9, 2012 11:10 AM
  • That will only take care of the move of the database, it will not change anything regarding what the connection string points to or other dependencides that the database has outside that database (listed in my article). Personally, I prefer backup and restore, again, reasons listed in my article.
    Tibor Karaszi, SQL Server MVP | web | blog
    Monday, January 9, 2012 11:29 AM