Moving a Database in SQL Server 7.0
-
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!
-
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
-
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.
-
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."
-
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