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 |
|
keithc
Starting Member
35 Posts |
 Posted - 2005-04-08 : 01:32:50
|
| Been a few since I have last posted here but hoping I can get some good advice or a link to a tutorial on a migration I need to do on a sql 2000 sp3 production box that has a variety of apps tied in with the DB's. Basicly I need to move all system/user db's to temporary storeage from the production box then format the box then add all user and system db's back on to newly setup system without breaking any apps. I will have a backup to a backup for the db's just to be as safe as i can in case any problems arise so i just need advice on how to accomplish this. There are catalogs/maintenance plans index's/views/sql as well as windows authentication accounts basicly the works on this box and I'd like to get all of it back to fresh installed box with same computer name as it had before it was formatted. Thnks a ton i'd really appreciate advice/links anything you guys forsee to keep me from messing this up. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-08 : 01:39:09
|
| Backup the database, including the system database. The msdb is the only system database you'll need to restore though. This will give you your DTS packages, jobs, etc back. Also, run sp_help_revlogin on the production box and save the corresponding logins script. AFTER you restore the databases, run the logins script you got on the destination server. This will correctly place all the logins on the new server. Here is a link to the sp_help_revlogin scripts:http://support.microsoft.com/default.aspx?scid=kb;en-us;246133Make sure you also document all the settings such as TCP/IP ports used, etc. I'm not sure how you'll get the windows accounts back. I assume you have them saved somewhere??MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
keithc
Starting Member
35 Posts |
Posted - 2005-04-08 : 02:18:39
|
| Derrick I wrote a quick project outline for this migration.I was wondering if anything stands out and if perhaps you wouldnt mind adding to this or removing any unnessecary step most particularly within the migration section of the outline. I really need to make this migration as smooth as possible and can use any suggestions possible. I'm still pretty new to sql and haven't done much work on prod box's and am not concerned so much with losing data just conerned with making this a can of worms and causing downtime Thanks a ton for your reply. I really apreciate your help now along with your help in the past.Phase1: PreparationSteps:• Immediately Backup all System/User Databases preferably to 2 separate locations EX:(One backup to Tape one to Disk) or (1 Backup ea on separate physical disks) • Check for existing Full Text catalogs as they are not included within any backup/restore operations.• Check existing SQL disk space usage/adjust accordingly for new SQL Install• Verify system meets SQL 2000 sp3 requirements• Check existing configuration for any replication/General server settings changes/Review logins /Backup Jobs/Alert Agents• Check data/log file locations (Recommend raid 5 or at the very least 2 separate physical disk) If possible mirror OS• Log any settings such as ansi_nulls etc• Log location/Type of current backup device/sPhase1 Concerns/Questions• What edition of SQL Server will be installed?• How big a partition to create for SQL and will Logs/Data be on same physical disk• Existing SA account password? Any existing Windows SQL admins groups? Mixed Mode or standard authentication?Phase2 Installation• Install SQL at default collation• Apply Sp3 (Reboot system)Phase3: Migration• Copy system/User DB’s off network on to newly created SQL box• Stop SQL service• Enter single user mode sqlservr –m –c (Leave cmd window open)• Enter EM select master and restore using master backup• May have to use Task manager to end task for EM to avoid error message• Select Service manager then start SQL service• Restore MSDB (Contains backup.job information) (*Make sure SQL Agent service is Stopped)• Restore Model Database if needed |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-08 : 02:36:43
|
| Phase1: PreparationSteps:• Immediately Backup all System/User Databases preferably to 2 separate locations EX:(One backup to Tape one to Disk) or (1 Backup ea on separate physical disks) --After you back them up, run a restore verify only to make sure the backups are good.• Check for existing Full Text catalogs as they are not included within any backup/restore operations.• Check existing SQL disk space usage/adjust accordingly for new SQL Install• Verify system meets SQL 2000 sp3 requirements• Check existing configuration for any replication/General server settings changes/Review logins /Backup Jobs/Alert Agents• Check data/log file locations (Recommend raid 5 or at the very least 2 separate physical disk) If possible mirror OS• Log any settings such as ansi_nulls etc• Log location/Type of current backup device/sPhase1 Concerns/Questions• What edition of SQL Server will be installed?• How big a partition to create for SQL and will Logs/Data be on same physical disk--Try to have OS RAID 1, log, tempdb, and data on different RAID sets if you can afford it.• Existing SA account password? Any existing Windows SQL admins groups? Mixed Mode or standard authentication?--The sp_help_revlogin will take care of all this for you. Just make sure you document and local server users that access SQL Server.Phase2 Installation• Install SQL at default collation--Make sure. This isn't always true. We have a bunch that run CP437-BIN (don't ask)• Apply Sp3 (Reboot system)--I would then apply MDAC 2.7 SP1 Refresh or MDAC 2.9.--I would then apply the .818 patch.Phase3: Migration• Copy system/User DB’s off network on to newly created SQL box• Stop SQL service• Enter single user mode sqlservr –m –c (Leave cmd window open)--Wouldn't do this.• Enter EM select master and restore using master backup--Wouldn't do this. Just use the login script from sp_help_revlogin to restore logins.• May have to use Task manager to end task for EM to avoid error message• Select Service manager then start SQL service• Restore MSDB (Contains backup.job information) (*Make sure SQL Agent service is Stopped)• Restore Model Database if neededLooks pretty good. I added a few comments in here and there.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
SQL Migration