Post reply

Moving a Database in SQL Server 7.0

  • January 26, 2005 at 7:41 am

    #87569

    I have a SQL Server 7.0 Database that resides on one machine.  There are stored procedures, users, tables, etc. that reside in this database.  I need to copy it to another machine running SQL Server 7.0, as the old machine is being decommissioned.  What is the easiest way to accomplish this?  Again, the new machine currently does not have this database. 

    Thanks!

  • January 26, 2005 at 11:33 am

    #539214

    Quickest way: run sp_detach on the database, copy the files to the new machine, then use sp_attach on the new box

    Next-quickest: Create a complete backup, then restore the backup on the new box.

    Complications: logins may be different or need to be reconfigured on the new box. If you use SQL authentication, you may need to work with sp_change_users_login. Also, scheduling downtime for the move may be an issue, depending on your organization.

    All the above routines are well documented in BOL. Proceed with caution (never delete, just rename!) and you should be Ok.

       Philip

     

  • January 26, 2005 at 12:40 pm

    #539227

    That sounds perfect. Backup the old one and restore onto the new server.

    You woulkd get database users with the backup.

    Transfer logins from the old server's master..sysxlogins to the new server's master..sysxlogins after sp_configure 'allow updates', 1 and reconfigure.

    sp_configure 'allow updates', 0 after the transfer.

     

    Kindest Regards,

    M Suresh Kumar

  • January 27, 2005 at 9:45 am

    #539372

    For logins check out this link: http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 27, 2005 at 10:04 am

    #539388

    thanks for all the help. i have moved the database and logins successfully.  thanks again!

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

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