Post reply

Copying databases from one server to another.

  • July 30, 2004 at 2:57 pm

    #360521

    Hello All,

    I am a SQL server newbie. Please explain me how to copy database from one server (SQL 2000/Win2K) to another (SQL 2000/Win2k3).

    I've read up on the copy database wizard and backup/restore but havent got a clear idea. When I tired using Copy Database Wizard...it gave me the 'Failed to create the OMWWIZE share'.  I figured it to be a permissions issue. I dont know how to get that fixed. My source server has system account as the start up account and the destination server has my windows account (and I am an administrator on that machine).

    I dont know how to do the backup/restore method. I did try something like this: created a backup of a database in the source server and copied that back up file to the destination server and used the restore option in the Enterprise Manager but it bombed becoz of the default path conflict and directed me to use the WITH MOVE option. So then I used the query analyzer to do the restore and this is what I ran unsuccessfuly:

    RESTORE Database FuseTalk from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FTBKUP.bak'

    With Recovery, move 'FuseTalk.mdf' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FuseTalk.mdf',

    move 'FuseTalk.ldf' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FuseTalk.ldf'

     

    Please let me know, step-by-step (newbie remember?) how to copy databases from one server to another.

     

    Thanks a bunch.

     

    Ram.

     

     

     

     

    Thanks,

    Ram Ganesan

  • July 30, 2004 at 3:33 pm

    #517106

    What error did you receive when you attempted to run the restore with move?  The syntax looks ok.

    Steve

  • July 30, 2004 at 3:38 pm

    #517107

    The MOVE clause needs to refer to the logical filename, not the physical file name.  If you don't know what the logical filename is you can execute sp_helpdb 'FuseTalk' on the source server and it will display them for you.  They are typically something like 'FuseTalk_Data' and 'FuseTalk_Log'

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • July 30, 2004 at 4:15 pm

    #517111

    Thanks for your replies. I changed the RESTORE statement per dcpeterson's reply (ofcoz after ensuring what the logical file names were) and this is the error I get:

    Logical file 'FuseTalk' is not part of the database 'FuseTalk'. Use RESTORE FILELISTONLY to list the logical filenames.

    RESTORE DATABASE is terminating abnormally.

    I have to tell you this...I didn't know how to do a backup and restore to start with. SO this is what I did: I used the Enterprise Manager(EM) to do a backup of the database 'FuseTalk' in the source server. I named the backup file as FTBKUP. Then I (manually) copied this file over to the BACKUP folder in the destination server. Is this the right way to do this?

     

    Thanks again for the help.....Ram.

    Thanks,

    Ram Ganesan

  • July 30, 2004 at 4:32 pm

    #517114

    It worked!! Sorry...I made an typo in specifying the logical filename. When I corrected it, it worked!

    I just have couple questions though.....

    1. what is the file format of the back up file....In my RESTORE statement I specified only the name FTBKUP whereas in some places I saw syntaxes which had filename.bak or filename.dat

    2. by doing this backup/restore process am I in anyway altering anything in the original database in the source server?

    Also please don't forget to spill a few words on my previous post's question:

    I didn't know how to do a backup and restore to start with. So this is what I did: I used the Enterprise Manager(EM) to do a backup of the database 'FuseTalk' in the source server. I named the backup file as FTBKUP. Then I (manually) copied this file over to the BACKUP folder in the destination server. Is this the right way to do this?

    THANKS PEOPLE.

    RAM.

     

    Thanks,

    Ram Ganesan

  • July 30, 2004 at 5:11 pm

    #517117

    By file format I assume you are asking about the file naming convention.  By convention, database backup files are named using the ".bak" extension and transaction log backups use ".trn", but this is completely optional.  Even having an extension is optional.

    No you will not alter anything on the source server, assuming you are not restoring there...

    Your method is fine, although the file certainly does not need to be in the backup folder in order to restore it, although it helps if you establish a standard method and stick to it otherwise you will end up with backup files all over the place.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

Viewing 6 posts - 1 through 5 (of 5 total)

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