Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-19 : 11:18:43
|
| chris writes "HiI am getting into DBA and have yet to move a DB to a new machine. Recently, I was assigned to do this task and before I started I wanted to do some research to get any help if possible. My task is to take all information contained in a SQL 7.0 database, and completely port all of it (including tables, stored procedures and DTS scripts) to a different machine running SQL 7.0. I was thinking to use SQL backup and restore functionality, but I wasn't sure if there were any catches to this approach that I should know about (such as not exporting ALL of the data and associated procedures). Any and all advice is appreciated.Thanks" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-03-19 : 11:30:16
|
| what you're trying to do should be straight forward....2 points to watch out for seem to be 'collation order'...and 'upper/lower case sensitivity'...one or both of which seems to be a server installation property....I've seen references to both here before, as 'be-aware-of' type issues.others are wiser and more able to pad this answer out. |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-19 : 11:54:54
|
| you should be fine with a backup/restore. it is the most complete method.make sure the server wide settings are the same, particularly collation, and be aware that DTS packages are server-specific, and are stored in their own database (msdb). You would want to save them to the destination server manually, as a backup and restore would destroy any packages on the destination server.setBasedIsTheTruepath<O> |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-03-19 : 12:13:50
|
| I just finished up a project of this nature. Here's what I did. 1) Install SQL on the new box with the same options, service packs, and hotfixes as the old server.2) Restore a backup to the new server of the user databases you are planning to move.3) Import DTS Packages to the new server.4) Transfer operators, alerts, and jobs to the new server.5) Test test test test test. I ran the new server for about a week. Verified that all jobs ran successfully, all DTS package could be executed and scheduled successfully, backups were valid, and that the application ran without a problem.6) Schedule down time for the move. 7) Kick everyone out of the old server.8) Disable all jobs on the new server9) Drop the user databases on the new server10) Use sp_detach_db to detach the databases on the old server.11) Copy the database files to the new server.12) Use sp_attach_db to bring the databases online.13) Shutdown SQL services on old server to be sure no one is accessing it.13) Test the application against the new server14) Enable all jobs on the new server15) Allow users back in.16) Sit back with a nice tall glass of Jack Daniels.This was done with 3 user databases totaling 50 Gig. The important message here is to lay out a very detailed plan so you don't forget anything and to test the crap out of it. Detaching and attaching can save a lot of time over backup/restore if your databases are fairly large. HTHJeff BanschbachConsultant, MCDBA |
|
|
|
|
|
|
|
|
Moving an SQL Server to a new machine - implications