May 11, 2004 at 11:14 am
Hello - I need to move my SQL 2k instance from one physical server to another. Detaching the DB from the old server and Attaching the DB on the new server is not a problem.
I need to know how I can move I can move any jobs/backup devices/logins associated with that DB to the new environment so that I don't have to recreate them manually.
Can this be done in the form of a package? I've seen it done by one of my more experienced DBA's, but time didn't permit for me to find out how it was done.
Thanks,
Ronnie
May 11, 2004 at 12:12 pm
Look to backup and restore the system databases on the new server from the old this will move across all logins and ensure that everything is as it was, you could alternatively use the "copy sql server objects" and "Transfer logins" tasks in DTS.
May 11, 2004 at 4:51 pm
vote for backup restore of all dbs. Move user dbs first, then system dbs.
May 12, 2004 at 7:14 am
Ronnie,
I just did this. A few points:
1) I tried to migrate the msdb db but was unable to detach it from the target server. I kept getting errors. Sorry I can't remember what they were now.
2) I ended up simply extracting scripts using Enterprise Manager. This worked fine.
3) The thing to look out for is many of my system jobs used the 'old' server name and specific directories in it. These had to be changed before the jobs would succeed on the new server.
Hope this helps... Scott
May 12, 2004 at 7:15 am
Thanks guys. I'm going to give it a try and I'll let you know how I made out.
Ronnie
May 12, 2004 at 7:18 am
Scott, can you explain step number 2? When you say "extracting scripts", what exactly do you mean.
thanks,
Ronnie
May 12, 2004 at 7:32 am
To move the system databases (including msdb) there are certain trace flags that need to be enabled, see http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&sd=tech for more details.
June 17, 2004 at 6:36 am
In sql server agent go to jobs. Select your job(s). Right click on them and you will see 'all tasks'. Click on that and select 'Generate SQL Scripts'.
June 18, 2004 at 10:27 am
Thanks to all. My migration was successful but one area I got into trouble with was the user ID's.
After detaching user databases and the msdb database and moving them over to the new server, I decided to do an export of data just to be on the safe side. After selecting Export Data, it brings up a DTS wizard to configure the export parameters. I chose to "Copy Objects and Data between Databases". The next screen that comes up allows you to chose your objects. There's a checkbox for "Use Default Options" which is select by default.
The problem I got into was I de-selected that option and chose to copy SQL Server logins (Windows and local SQL accounts). Well it exported fine, but the users had problems accessing the DB. When I tried to delete the accounts and re-add them, I would get an error indicating the accout was already there. In order for me to correct the problem, I had to delete the accounts from the sysuser table. What a mess.
Question: Should I have just allowed the user accounts to migrate over when I detatched the user DB or was there something else I should have done that would have left the user accounts in tact?
June 18, 2004 at 11:59 am
Ronnie,
I believe below is what might have happened in your case. SQL Server manages security on two layers, logins and users. A login grants you access in the server but this does not mean that you have access to the database. To be granted access to the database you need to create a user in the database which is mapped to the login.
Now, in your case when you moved over the database from say, Server A to Server B, the users got moved over also from Server A to Server Bas they exist in the database but the mapping to the login when it got moved over to Server B was wrong(as this referenced the mapping to Syslogins for Server A) Hence the user couldnot log in. You can fix the mappings by using
sp_change_users_login 'Auto_fix','User_name'
This would map the user to the login on the existing server.
Be careful not to confuse between login and user.
June 18, 2004 at 12:10 pm
Thanks for the info. If I have multiple users, can I include them all in one script, maybe separated by a commas (,)?
June 18, 2004 at 12:17 pm
you cannot separate the users with a comma. But you can open a cursor and go through each and every row one at a time.
June 18, 2004 at 1:10 pm
I'm not sure I totally understand what you mean when you say open a cursor and go through each line.
June 18, 2004 at 2:00 pm
Ronnie, sa24 means something like that:
To add users:
declare mycursor cursor for
select name from master.dbo.syslogins
where name not in (list of names or select statement goes here,
'sa')
and name in (
list of names or select statement goes here)
declare @myvar varchar(30)
open mycursor
fetch mycursor into @myvar
while (@@fetch_status = 0)
begin
print @myvar
execute sp_grantdbaccess @myvar, 'you other parameters go here, be sure to be in the correct database'
fetch mycursor into @myvar
end
close mycursor
deallocate mycursor
You can do the similar thing to add proper logins using sp_addlogin
Regards,Yelena Varsha
June 29, 2004 at 5:20 am
Hi Ronnie,
I am too facing the same situation like you as we bought a new server need to create that as production server.
Pls refer to the K artile 298897 mapsids.exe .
my email is hemamalir@hotmail.com you can write to me for further details as i do work on same situation.
Thanks
chami
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply