none
How to move databases from one Instance to another?

    Question

  • Hello Everyone

    I need to move databases from one Instance to another. My first attempt was to make a backup of each database which I then tried to restore on the second Instance. This wasn´t working (I guess because of some relations in the master table(s)).

    Is there a conveinient way to do this?
    Monday, June 02, 2008 9:40 AM

Answers

  • You have the following options to move the databases,

    1. Backup & Restore
    You can use the below command to backup the database
    Code Snippet

    Backup database dbname to disk='Path\filename.bak'

    To restore the database you can use the below commands,
    a.) If the database you are going to restore already exist and if you wish to overwrite it use the below command,
    Code Snippet

    Restore database dbname from disk='Path\filename.bak' WITH REPLACE

    b.) If you wish to restore the database in a different name you can use the below command,

    (a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak'
        This command will give you the logical file names in the backup set.

    (b) RESTORE DATABASE Dbname
       FROM DISK = 'c:\xyz.bak'
       WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'D:\yourfolder\YourNewDatabaseName.mdf',
       MOVE 'LogicalLogfile name got from Step(a)'  TO  'D:\yourfolder\YourNewDatabaseName.ldf'

    2. You can also use detach & attach operation to move your databases as shown in the below links,
    http://support.microsoft.com/kb/224071
    http://msdn.microsoft.com/en-us/library/ms187858.aspx

    If you are trying to move system databases then ensure that you have the same build and same edition of Sql Server, refer this link for more info, http://support.microsoft.com/kb/264474 and refer this link for moving in Sql 2005, http://sql-articles.com/index.php?page=articles/msysdb.htm

    If you receive any errors please post the exact contents here..

    - Deepak




    Monday, June 02, 2008 10:05 AM
    Moderator

All replies

  • You have the following options to move the databases,

    1. Backup & Restore
    You can use the below command to backup the database
    Code Snippet

    Backup database dbname to disk='Path\filename.bak'

    To restore the database you can use the below commands,
    a.) If the database you are going to restore already exist and if you wish to overwrite it use the below command,
    Code Snippet

    Restore database dbname from disk='Path\filename.bak' WITH REPLACE

    b.) If you wish to restore the database in a different name you can use the below command,

    (a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak'
        This command will give you the logical file names in the backup set.

    (b) RESTORE DATABASE Dbname
       FROM DISK = 'c:\xyz.bak'
       WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'D:\yourfolder\YourNewDatabaseName.mdf',
       MOVE 'LogicalLogfile name got from Step(a)'  TO  'D:\yourfolder\YourNewDatabaseName.ldf'

    2. You can also use detach & attach operation to move your databases as shown in the below links,
    http://support.microsoft.com/kb/224071
    http://msdn.microsoft.com/en-us/library/ms187858.aspx

    If you are trying to move system databases then ensure that you have the same build and same edition of Sql Server, refer this link for more info, http://support.microsoft.com/kb/264474 and refer this link for moving in Sql 2005, http://sql-articles.com/index.php?page=articles/msysdb.htm

    If you receive any errors please post the exact contents here..

    - Deepak




    Monday, June 02, 2008 10:05 AM
    Moderator
  • Thanks a lot.

    We found out that we only had the Management Studio Express on our Server. With the full version I can just use the export-task which works very well now
    Monday, June 02, 2008 10:46 AM
  •  Macro81 wrote:
    Hello Everyone

    I need to move databases from one Instance to another. My first attempt was to make a backup of each database which I then tried to restore on the second Instance. This wasn´t working (I guess because of some relations in the master table(s)).

    Is there a conveinient way to do this?

     

    Once you restored all the database in the destination server, you need to fix orphan ID's so that the db user SID will get fixed with the loginname in master db. Check the link below

    How to fix orphaned SQL Server users

    Monday, June 02, 2008 3:43 PM
    Moderator