Post reply

2 Servers Same Data, Different Config... Restore?

  • June 4, 2004 at 7:20 am

    #82978

    Hi!

    I have two servers with the same data and schema but they were configured differently ... data kept on different drives because of space differences on the 2 servers.   I want to backup data on one and restore to the other.  Do I need to do something special? ... what is the backup command line I might use for hte backup and restore?

     

    Thanks!!!!! So Much for any reply .... in advance

     

  • June 4, 2004 at 10:05 am

    #509049

    Simple answer would be "WITH MOVE" option. Here is from BOL

    MOVE 'logical_file_name' TO 'operating_system_file_name'

    Specifies that the given logical_file_name should be moved to operating_system_file_name. By default, the logical_file_name is restored to its original location. If the RESTORE statement is used to copy a database to the same or different server, the MOVE option may be needed to relocate the database files and to avoid collisions with existing files. Each logical file in the database can be specified in different MOVE statements.

    I do the same thing and made a DTS package to do that and if you want to do that, make sure to kill users on the target database before you restore and also if you have different login id, make sure to resync them. If you have enough space, you can also restore as different name and just swap it. That will reduce the downtime for target database. Hope that helps.

     

  • June 4, 2004 at 10:28 am

    #509054

    You are wonderful! Would you mind giving me sampe backup and restore command line code using the MOVE clause in directory H:\A in from system and in D:\X in the other.

    Obviously I am new to this environment.

     

  • June 4, 2004 at 10:50 am

    #509057

    For Backup Commend, there is no special about it. You can use any way to backup. Just create backup device and do like this.

    BACKUP DATABASE MyDB to MyDBBackup_Device

    And put options in it after that. And the assume "MyDBBackup_Device" points to '\\FILESERVER\SQLBackups\MyDB.bak' location here is the script for that.

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

    RESTORE FILELISTONLY

    FROM DISK='\\FILESERVER\SQLBackups\MyDB.bak'

    GO

    RESTORE DATABASE MyDB

    FROM  DISK='\\FILESERVER\SQLBackups\MyDB.bak'

     WITH  MOVE 'MyDB_Data' TO 'D:\X\MyDB.mdf',

      MOVE 'MyDB_Log' TO 'D:\X\MyDB_log.ldf',

      REPLACE

     

  • June 4, 2004 at 11:30 am

    #509061

    Thanks!  Some in ohio loves you. : )

  • June 4, 2004 at 11:41 am

    #509065

    You are welcome

    BTW, I put this script to find out database file information. I am sure you already knows it but just to be clear

    RESTORE FILELISTONLY

    FROM DISK='\\FILESERVER\SQLBackups\MyDB.bak'

    GO

     

  • June 4, 2004 at 11:49 am

    #509068

    Quoting Sgt Schultz I must say that 'I know notttthing' ... new to this stuff.   Any links you could give me to learn would get you in my prayers and close to my will .

    Thanks!

  • June 4, 2004 at 12:18 pm

    #509072

    Perhaps, some other folks knows good links about this. I usually uses BOL, Books Online, from SQL Server and try all different thing. I know sometimes, BOL is not easy to read for all the format like that but once you get used to it, it is really what you want for task like this. (someone said that to me same thing but for new subjects, I am still think that BOL is not the eaisest manual in the world )

  • June 9, 2004 at 2:19 am

    #509596

    you can also make this from Enterprise Manager..

    on source DB .. right click on your DB and choose all tasks .. Backup .. and save it to file

    and take this file to the other server

    and on destination DB .. right click on your DB and choose all tasks .. Restore .. and choose this file

    Important:

    - do you think before about Static replication .. that is to copy data while working to the other server

    I hope this help u


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 9 posts - 1 through 8 (of 8 total)

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