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 |
|
mdesai2005
Starting Member
4 Posts |
 Posted - 2005-05-11 : 13:38:41
|
I need to move my reporting server from old server A to new server B. Server A is setup identical to the old one (same service packs, same version of SQl, etc.) with the exception of no E: drive on new server (B). The old server (A) had a maintenance plan that dumped one of the DB's onto that partition (E:\). I do not plan on doing the same thing on the new server.What is the best way to accomplish this? I tried just copying the mssql7 directory (and the db files) to the new server and it would hang on startup of the sql service. I am thinking of running a couple of scripts that i have found to import the users, DTS packages, and jobs. If possible, i would like to change the owners of some of the packages and jobs that run as well since they are running under a user which no longer works here (instead of a service account). Any help or opinion is appreciated. -Mook |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-05-11 : 20:31:03
|
| If you don't have the same drive setup, you will need to restore user databases (not master, msdb, model, tempdb) onto the new server using the WITH MOVE option. You can copy logins using the DTS task (really easy), or you can use sp_helprevlogin (downloadable from lots of places).Now I know, and knowing is half the battle!http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48013 |
 |
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2005-05-12 : 14:36:01
|
| I like Backup and Restore for moving DBs between Servers. Basically, I go the the destination Server and create a new Database - making sure logical file names for the data and transaction log files are the same as the source - then I recreate the users on the Destination server - this link --> http://support.microsoft.com/kb/246133 explains how to script out your users on the source server and recreate them on the destination serverOnce the users are in place on the destination server, Backup the database that you want to move on your source sql server, move the backup file over to your destination sql server and restore it. If your user permissions are not quite right after the restore (ie, orphaned logins or permissions missing.......) run EXEC sp_change_users_login 'Auto_fix', 'username' and you should be all set.. works for me anyway |
|
|
|
mdesai2005
Starting Member
4 Posts |
Posted - 2005-05-23 : 15:53:50
|
| Dude. Thats awesome help and i am well on my way. I was trying to run a DTS package that copies pretty much everything from server a to server b. It fails in the middle stating that user or role 'x' does not exist on the database. After searching through everything on the old db server, i cant seem to find any references to that account or role. Any ideas? Thanks!BTW, i used the login script referenced in the earlier post. |
|
|
|
|
|
|
|
|
Moving A SQL Server for Newbies