July 7, 2003 at 9:36 am
I need to migrate all content from 1 SQL7 to a new installed server also running SQL7. The webserver contains about 12 databases. I restored all these databases to the new server. The main problem now is the users and logins. Many applications are accessing the server and the documentation regarding the logins is non-existing. I would like to know if there is any way to recover the logins and users from the old server and implement them on the new server. Just copying the content from sysusers and sysxlogins won't work i presume 😉
Any help would be appreciated. I need to have this migration done as soon as possible but don't have much time on my hands. I'm filling in for our DBA here that is getting a nice tan some place far away, but nobody filling in for my job :s
July 7, 2003 at 9:45 am
use xp_help_revlogin. You can find a copy here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
July 7, 2003 at 11:31 am
Also check out this...
http://www.sqlservercentral.com/columnists/rsharma/copydatabase.asp
It's a pretty good article and part way through it talks about moving logins from one server to another.
-SQLBill
July 8, 2003 at 7:45 am
It sounds to me like you are trying to clone a server. I think the quickest, easiest way to migrate the whole server is to do the following: This is assuming that the new server has all the same drive letters/paths as the old one.
Stop the SQL Server Service on both machines.
Copy all of the databases files and transaction log files to the new server. Make sure all of your paths are correct and that you get all of the system database files also.
Restart the SQL Server service on machine # 2.
In query Analyzer, run the following script to reset SQL Server to the new server name:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName','local'
go
Restart SQL Server to make change active.
Run the following to verify the name is changed: SELECT @@servername
All the accounts will be in the new server along with all of the databases, DTS Packages and scheduled tasks.
More info on renaming servers: http://support.microsoft.com/default.aspx?scid=kb;en-us;317241
July 8, 2003 at 7:50 am
I had same scenario of moving 8 databases from a SQL 7 server to a newly built SQL 7 server some time ago. I used the article Greg Larsen provided (246133) and it worked fantastic. Two additional articles that were of help are:
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b314546
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b240872
Both above articles have links to other MS articles that can be of assistance.
Dave Gradoville, MCDBA
Dave Gradoville, MCDBA
July 8, 2003 at 8:11 am
Good info in there Greg, Bill.
Now my twist.
Destination = brand new W2k machine running SQL200
Source = old Nt4 machine running SQL7
Source DB = 140 GB
Lots of DTS pkgs
Lots of Agent jobs
A few Logins.
I've done a small test of Backing up a SQL 7 db (6 Gb) and restoring to a SQL 2000 server and it seems to work okay.
(Backup / Restore with Move..)
Are there any special methods/considerations regarding copying of DTS, Logins, Agent Jobs?
Should I be doing this in any particular order, (logins, then db, then dts?)
I'm especially interested in any SQL7->2000 tips/tricks/traps.
Thanks
Bruce
July 8, 2003 at 8:18 am
I always do logins first. I use sp_help_revlogin to accomplish the moving of logins. More info here about sp_help_revlogin
http://www.databasejournal.com/features/mssql/article.php/2228611
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
July 13, 2003 at 11:30 pm
Jarick, I tried your suggestion for cloning a server.
It fails for me on the last step, with unable to add new server because it already exists. I then find that all the logins are from the old server but there are no databases.
I used
sp_dropserver 'ABC'
go
sp_addserver 'CDE','local'
go
Where ABC is the machine name of the old server and CDE is the machine name of the sql server on the new machine.
What am I missing?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply