June 13, 2004 at 5:11 am
Hi,
Can anyone tell me when we need to re-install a sql production a server to a new server with more Hw upgrades , whats the best method of transfering data?
Is it thru a another temp server attach it decommission the production and re-install production back online and then attach them again or ...
Any one can suggest me the best way I can get exactly full copy of previous sql server into new improved server. (new server will be diffrent logical drives)
can anyone tell me a command to use as I need to check the statistics also to compare with new and previous server for eg: how many stored procedures , how many indexses (sp_help , sp_helpdb) or any other best way to double check wether everything copied over to the new server?
Thanks !! lot in advance
June 13, 2004 at 8:53 pm
1. Look for sp_help_revlogin by searching the forums on http://www.sqlteam.com. Run this script in the master database on the old server to script out all the logins, database users, etc.
2. Backup the msdb database so you don't lose your DTS packages and jobs.
3. Detach the databases from the old SQL Server instances and copy the database files (.mdf and .ldf) for all user database to the new SQL Server
4. Attach the databases to the new SQL Server.
5. Restore the msdb database from backup.
6. Run the permissions script generated by the sp_help_revlogin to restore logins and users.
That will be all you need for the migration. You don't need to doublecheck anything. You moved the actual databases. I always run a DBCC CHECKDB() on each db after I do this to check integrity.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
June 14, 2004 at 2:50 pm
1. The sp_help_revlogin script is a real lifesaver. Definitely the way to move logins between servers. I usually use it to transfer the logins first, then move databases by using TSQL to RESTORE from the latest backup.
2. Jobs, operators, alerts can be scripted and the scripts used to recreate them on the new machine. The scripts can be modified if the machine name and/or passwords have changed using the global replace in QA.
3. Sadly, DTS may all have to be moved individually, either by resaving them in the new location, or saving them as a file, then opening and resaving them on the new server. Connections, etc. must be modified.
I have tried a couple times to move jobs and DTS by restoring msdb to a new server. The problem is, the new SQL Server then thinks its jobs belong to a target server (even though it isn't one), and will not allow them to be modified. I once inherited a server from another division where someone had done this, and I had to remove the jobs by deleting them manually from the system tables. They continued to exist in cache, generating log errors, and didn't go away until I could schedule a restart to flush them out.
Anyone out there have a way around this?
June 14, 2004 at 3:05 pm
DTS should be fine if the server names are the same.
sp_help_revlogin is available on MSDN (msdn.microsoft.com).
The above advice should work for you, but practice a few times.
June 14, 2004 at 8:15 pm
I think that the mean old dba has a good approach.. I have done this successfully several times using much the same process. The only difference is with the DTS packages. It is very easy to move them by opening the package and using the SAVE AS function to place them on a different server. This allows you to modify any functions or connections with the new server name if necessary. As Steve Jones stated this should not be necessary if the server names are the same. In fact that would open another can of worms. Either way the task is not as bad as you would first think. Just be careful and think through each step.
Good Luck
John (Another Mean "Old" DBA. And loving it)
June 14, 2004 at 10:36 pm
One other thing I forgot on my list (DOH). After you restore the msdb database, you need to update the originating_server column in the sysjobs table to the new server. Otherwise, it thinks your jobs are managed by an MSX server (or whatever it is) and won't let you edit them, which kind of sucks.
Also, the sp_help_revlogin script on msdb is different. The one on sqlteam has had several ummmmm "fixes" applied to it. The one on msdb uses the dbid instead of the dbname to map the default dbs, which in effect scrambles them. I also did something with the server role mapping I believe, but I can't remember because I'm getting old and cranky.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply