SQL7 to new server
-
We are moving our production dbs to another server. I am guessing the best way to move the dbs is to detach/attach (or not).
My question is...what is the best way to move the logins with passwords and dts pkgs and scheduled jobs?
Thanks much.
Randy
-
Detach the DB's on the old server, copying them to the new (over the network, via tape, or whatever), then attaching them on the new is (usually) a piece of cake.
Logins can be scripted. I'm sure somewhere here in SQLServer Central is an example.
DTS packages can be save to your local hard drive then reloaded on the new server. I believe saving them as "structured storage files" then from the old server, then opening the saved file on the new one works fine. Caution here is that if there are any server references anywhere that may need to be modified.
Scheduled jobs? I know there's a way... but right now I can't think of it. If there aren't too many, rebuilding them wouldn't take too much time. I know the msdb database is the location of them.
-
I have done this countless times. On the new server create database names exactly how they are on the old server, run this attached logins script via QA, save, run in QA on new server. Then backup the dbs on old server, copy files to new server and restore them onto the new server. Then run sp_helpuser to make sure the logins are mapped correctly. Jobs, there is a way to script them out.. not sure how. Ours are simple enough that I just recreate them. DTS packages... I really don't have any experience of moving them... if the servername changes you will have to edit all of the packages to update the server name.. Here is the logon script. SET NOCOUNT ON
-
We are in the process of moving two of our sql7 servers to another box. Here is what we are doing:
1. recreate all the logins (sql, nt user, nt groups) on the new box with pubs being the default database
2. detach the user database from the old server, copy the mdf & ldf to the new server, reattach the database
3. run exec sp_change_users_login 'report' to determine there are any broken links, if any found run exec sp_change_users_login 'update_one','sql login name','sql login name' to fix
4. copy each related dts to the new server by opening dts in the old server in design mode and do a save as pointing the the new server (if you have many to copy you may use the freeware dtsbackup)
5. once your dts has been copied to the new server, open each one to correct any connections pointing to the old server
6. jobs can be scripted out by right clicking jobs, choose all task, generate scripts
7. we manually recreated all our maintenance plans
-
If you are only moving to new hardware.
Install SQLServer exactly as your original server (drives, directorylocations, ..), bring it to the same servicepack as original.
Now comes the rocket-science:
shut down the new sqlserver.
shut down the old sqlserver.
copy the original server's sqlserver-data- and log-files to the new server.
follow the "rename server" procedure and all should be fine.
I've done it a couple of times without any problems.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Very helpful everyone, Thanks.
Mark, that login script didnt post.
-
It doesn't seem to paste in, I can email it to you....
-
Install the new SQL server with latest sp and security patch. Then just on new server, open EM all tasks
-
Sorry, hit the Enter, continue, import everything (all object and Data) from old server. I don't know is there any problem with this way? I think this is the simplist way . Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply