locked
Migrate SQL Server 2000 from one server to another RRS feed

  • Question

  • User214706206 posted
    Hello All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>I think this may have been asked here before but I need to get advice from all the SQL Server experts here. Here is the situation, due to the server load and unreliability, we have decided to move the application from one server to another. The system has asp.net web application, web services and windows services. I am ok with moving the asp.net application but need advice regarding what is the best practice to move the sql server 2000.<o:p></o:p>Based on my experience with the sql server 2000 and my understanding, here are the different ways to move the database.<o:p></o:p>1.Export the database using DTS.I have used this to create the test environments for my application.<o:p></o:p>2.detach and attach the files to the new location. I have used this and this works well.<o:p></o:p>3.This method describes copying the MSSQL/Data from the original location to the new location. I have never used this.<o:p></o:p>http://vyaskn.tripod.com/moving_sql_server.htm<o:p></o:p><o:p> </o:p>so, what is the best practice to move the db ?or any other method that I don’t know and didn’t mention in this post.<o:p></o:p><o:p> </o:p>Thanks in advance.<o:p></o:p>
    Wednesday, November 5, 2008 2:37 PM

Answers

  • User-611445092 posted

    Hi, Since you are move DB from One Server to another server, I will suggest any one of the following approaches: 1. Backup and Restore the Database from Old to new server 2. Detach and attach the database from old to new server
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 6, 2008 12:06 AM
  • User1464216657 posted

    The advantage of the backup/restore is that all the constraints, triggers, stored procedures, functions, users, roles, etc will be restored and you can change the path to the data and log files.

    So basically you only end up having to create logins at the server level and you are good to go.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 7, 2008 2:50 PM

All replies

  • User1464216657 posted

    I usually backup the db and then restore the backup to the new db on the new server, forcing the file path and overwrite of logs.

    Wednesday, November 5, 2008 4:10 PM
  • User1096912014 posted

    We did the same too (Backup and restore with override).

    Wednesday, November 5, 2008 10:37 PM
  • User-611445092 posted

    Hi, Since you are move DB from One Server to another server, I will suggest any one of the following approaches: 1. Backup and Restore the Database from Old to new server 2. Detach and attach the database from old to new server
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 6, 2008 12:06 AM
  • User214706206 posted

    Thans everyone for your input.As I see,you have adviced Backup and Restore.As per this atricle - http://support.microsoft.com/default.aspx?scid=kb;en-us;314546

    There are three methods 1.Backup and Restore 2.Detach/Attach and 3.Export/Import.

    are there any advantages in using the backup/restore method over the other two?I have used the second(Detach) and third( Export) in past and these worked wll for me.

    Also,I understand these methods will not move the logins and there a process to move the login in that atricle.If I follow that will that just move the logins or the access permissions attached to these logins as wel?

    Thanks for your help.

     

     

    Friday, November 7, 2008 2:33 PM
  • User1464216657 posted

    The advantage of the backup/restore is that all the constraints, triggers, stored procedures, functions, users, roles, etc will be restored and you can change the path to the data and log files.

    So basically you only end up having to create logins at the server level and you are good to go.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 7, 2008 2:50 PM
  • User-1673793073 posted

    I like to use detach and attach and move the logins using the sp supplied by microsoft.

    Does anyone know how to moved objects such as linked servers and jobs?

    Wednesday, November 26, 2008 3:01 PM