Results 1 to 5 of 5

Thread: How to move db to another server?

  1. #1
    Contributing Member
    Join Date
    Apr 2007
    Answers
    58

    Smile How to move db to another server?

    We need to move one db to another server to save diskspace on MS SQL server. ie. move only db & transaction log files from server1 to y:\dbfiles\ folder where y: is \\server2\Remote.
    MSSQL is not installed on server2. So do I need to set any permissions on y:\dbfiles or any special setting for it? Plz reply.


  2. #2
    Junior Member
    Join Date
    Apr 2007
    Answers
    8

    Re: How to move db to another server?

    If your only intention is to save diskspace by transferring entire dabase to another server and not want to use this database on other server. Then you don't have to bother about sql server and use the first part of this reply to save your diskspace: steps:

    1. Enter sql server enterprie manager

    2. Right click on the database you want to transfer

    3. Choose all tasks -> backup database

    4. In the dialog box "select-database complete" radio button and in the name textbox enter the appropriate backup name.

    5.in the destination section click add to select file where to save the database backup.

    6. Choose filename radiobutton and clilck on eclipse button to chhose destination folder
    and filename for the backup. If file name dosn't appear when you click ok then go to end of the textbox and append a filename

    7. Click on ok

    8. In overwrite section choose append media or overwrite media according to your plan.

    9. Now click on ok.

    10. Find the backup file in the destination folder and move it to another server.

    11. Delete the database from first server using enterprise manager. Make sure u uncheck the option of delete backup and restore history now the the first server doesn't have the the database.

    If you want to use this database for further transaction then make sure you install the sql server on the second server and follow the steps given below to restore the database.
    1.open the sql enterprise manager-> unfold the server instance-> right click on the database folder-> choose all tasks->choose restore database

    2.in the dialog box choose give a name for the database to restore e.g. Mydb. In the restore section choose from device.

    3. In the parameter section click on select device and select the backup file by clicking add and click ok twice.

    4. Choose restore bckup set and choose database-complete

    5. Now click ok. Your database will be restore and now you are ready serve the database.

    If this reply doesn't serve your purpose then please do inform me. If you have any suggestions that is welcome.--> bhaskar jha

    happy programming

    Last edited by bhaskarjha; 04-13-2007 at 04:49 AM.

  3. #3
    Contributing Member
    Join Date
    Apr 2007
    Answers
    58

    Re: How to move db to another server?

    Hi bhaskar jha,

    Thanks for your interest. I think, I failed to explain my actual purpose in the post. What I trying to do is keep only the live database files on another server to save diskspace on current sql server. ie. place live database files on a network share. So that SQL server & Database on same system but only database files on a networkshare on another system as the growth of db will not reduce the free space on SQL server. I think now you are clear about my problem.. plz help..

    Regards,
    Anoop :)
    If its useful, dont forget to [COLOR="Red"]THANK[/COLOR] me :cool:

  4. #4
    Junior Member
    Join Date
    Apr 2007
    Answers
    8

    Re: How to move db to another server?

    Hi
    here is the another solution..

    First, you have to make sure that no one else but the administator is currently using this database.

    Next, create a backup of the database. For this open query analyzer and type down following command to add dump device for backup :

    sp_addumpdevice 'disk','mybackup','c:\backup.dat'
    go

    now backup the database

    backup database 'mydatabase' to mybackup
    go

    here mydatabase is the name of database and backup is the name of dump device we created earlier. After the backup now restore the database to use further

    --determine the number of the files to restore

    restore filelistonly from mybackup
    go

    --now restore the database

    restore database mydatabase from mybackup with norecovery,
    move mydatabase_data to server\\path\mydata.mdf,
    move mydatabase_sec to server\\path\mydatasec.ndf
    go

    after the restoration process refersh the server and checkout the location using:

    sp_helpfile 'mydatabase_data'
    go

    if there is log to recover then recover it through

    restore log mydatabase from mylog with recovery
    go

    make sure the current database is master .

    i think that will solve your problem. Please do reply with what ever your take on this solution.

    happy programming....


    Last edited by bhaskarjha; 04-21-2007 at 08:17 PM.

  5. #5
    Junior Member
    Join Date
    Apr 2007
    Answers
    5

    Re: How to move db to another server?

    First import db from ms sql serevr to ms access file one machine and copy the same database in another machine through network. To copy data from sql to excel. You better to use dts import/export wizard. Thanking you, d.venkateswarlu


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact