Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Migrating SQL to a new Server

Answered Migrating SQL to a new Server

  • Wednesday, May 02, 2012 2:12 PM
     
     
    Sign In to Vote
    0
    Hi,

    We currently have a SQL 2008 datawarehouse server with about 15 different databases.

    We were given a new Server (faster,more memory,etc..) with a fresh copy of SQL 2008 R2 installed.

    I will need to move all of the databases from the old SQL server to the new SQL server. The issue that i have is that on the current SQL server, we have a C: and D: drive. Some of the databases and logs are on C: and others on D:. On the new SQL server we have C:, D:, and E: drives.

    What is the best way to move the databases from the old SQL server to the new SQL server and have the files reside on a different drive? For example, If i have database called ABC and the data and log files are stored on C:\DataFiles. I want to move them to the new SQL server and have the data file on D:\DataFiles and the log file on E:\LogFiles.

    I will also be moving the system databases as well. Those all reside on C: and will be on C: on the new server. I imagine i will have to move those first.

    Since this is a datawarehouse, i don't have any issues with stopping the service if I need to.

    Thanks
     

All Replies

  • Wednesday, May 02, 2012 3:05 PM
     
     
    Sign In to Vote
    0

    Hello

    If you want to move the databases to the new server and place the data and log files on different folders you can use two methods

    1- Backup the databases on server A and restore them to server B using WITH MOVE option

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

    2- Copy the mdf and ldf files from server A to the new locations on server B and use ATTACH (not that you have to either stop sql server service or detach the databases on server A to be able to handle the files)

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

    Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

     
  • Wednesday, May 02, 2012 3:11 PM
     
     
    Sign In to Vote
    0

    you can either use Detach&Attach or Backup&Restore.

    in both attach and restore you can define a different location for the files and therefore you're free to change the layout,

    Restore: use the MOVE option

    see the following KB for detach & attach database to a different server How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

    Don't forget that you've to transfer logins and any SQL jobs too.

    I will also be moving the system databases as well. Those all reside on C: and will be on C: on the new server. I imagine i will have to move those first.

    moving system database is not really a good idea !!

    Why do you believe that you've to transfer the system database as well ?

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



     
  • Wednesday, May 02, 2012 3:32 PM
     
     
    Sign In to Vote
    0
    I figured that all the jobs and logins would come over with the system databases, otherwise i will have to bring those over separately.  What issues would i encounter by bringing over the system databases and overwriting the ones on the new server?
     
  • Wednesday, May 02, 2012 3:38 PM
     
     Answered
    Sign In to Vote
    0
    I figured that all the jobs and logins would come over with the system databases, otherwise i will have to bring those over separately.  What issues would i encounter by bringing over the system databases and overwriting the ones on the new server?

    Personally I would never move system database from one server to another server to avoid any issues.

    To transfer logins you can you any tools to transfer object from one SQL Server to another.

    a detailed blog related to moving database to another server Move a database from one server to another server in SQL Server 2008

    here's a description how to move system database to another server Moving System Databases

    as you see that would not become a trivial task and it is definitively easier to transfer logins and jobs only.

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


     
  • Friday, May 04, 2012 9:55 AM
     
     
    Sign In to Vote
    0

    could you please refer the below link for your reference, it may help you.

    http://vyaskn.tripod.com/moving_sql_server.htm