March 25, 2010 at 1:30 am
Hi,
I have some migration issues. My company has 2 servers.
Sql server 2000 is running on server A and sql server 2005 is running on server B
I have to migrate sql server logins, jobs and schema (not data) from server A to server B
I know it is possible to script sql server logins, jobs and schema by using the management studio.
But is there a way for me to come out with the scripts by programming using T-SQL? I know T-sql but I do not know where to draw out information about logins and jobs so that I can come out with scripts by programming
last thing, does any one know how to verify the migration is successful
thank you
March 25, 2010 at 9:48 am
Use method 2 described in this article to transfer logins: http://support.microsoft.com/kb/246133. As for jobs and schema, it's so much easier to script them in SSMS or Enterprise Manager that I can't recommend using T-SQL to do it. You could search the Scripts section of this site to try to find something.
Greg
March 25, 2010 at 10:38 am
You verify that the migration is successful by testing your applications, jobs, logins, etc.
As for migrating the jobs, You could try using REDGate SQL Data COmpare against the msdb database as well. It can also provide you with the scripts.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2010 at 7:32 pm
CirquedeSQLeil (3/25/2010)
You verify that the migration is successful by testing your applications, jobs, logins, etc.As for migrating the jobs, You could try using REDGate SQL Data COmpare against the msdb database as well. It can also provide you with the scripts.
my company does not want to spend any money. i am dead meat
March 25, 2010 at 8:05 pm
I know it is possible to script sql server logins, jobs and schema by using the management studio.
Logins cant be scripted directly from the management studio with the same password. For scripting use sp_help_revlogin. More details refer http://support.microsoft.com/kb/246133
Regards,
Raj
March 26, 2010 at 1:55 am
arr.nagaraj (3/25/2010)
I know it is possible to script sql server logins, jobs and schema by using the management studio.
Logins cant be scripted directly from the management studio with the same password. For scripting use sp_help_revlogin. More details refer http://support.microsoft.com/kb/246133
what about jobs?
March 26, 2010 at 3:57 am
You can always script all the jobs from Enterprise manager from
Management->SQL server agent->All tasks->Generate SQL scripts
generates the script for all the jobs on the server
Regards,
Raj
March 26, 2010 at 7:09 am
arr.nagaraj (3/26/2010)
You can always script all the jobs from Enterprise manager fromManagement->SQL server agent->All tasks->Generate SQL scripts
generates the script for all the jobs on the server
This is about the cheapest method.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2010 at 7:34 am
Why is it that companies never want to spend money on the backbone of the company. How long do you think a company would survive with no data? Ask them that.
Companies always seem to skimp on Servers and phones. Something that every company needs to survive. But they always want some miracle to happen when they give you junk hardware to work with.
Andrew SQLDBA
March 26, 2010 at 7:41 am
Jason:
This is about the cheapest method.
Recession has taught us that we need to resort to the cheapest option where ever possible 😀
Regards,
Raj
March 26, 2010 at 7:52 am
arr.nagaraj (3/26/2010)
Jason:
This is about the cheapest method.
Recession has taught us that we need to resort to the cheapest option where ever possible 😀
It's also probably the easiest. But the OP wants to figure out how to write a script that will extract that information.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2010 at 8:00 am
Oopsie..I didnt read the question correctly. Just saw OP's 'what abt jobs' and replied.
Apologies shinobigoh and Jason for my dumb response.
Regards,
Raj
March 26, 2010 at 8:04 am
arr.nagaraj (3/26/2010)
Oopsie..I didnt read the question correctly. Just saw OP's 'what abt jobs' and replied.Apologies shinobigoh and Jason for my dumb response.
I would disagree that it was a dumb response. It seems more logical to do it that way.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2010 at 8:25 am
Its logical.I was just trying to be self critical 🙂
The option I can think of, for jobs, in that case would be to create a dts package for transferring jobs.
Trigger it using xp_cmdshell and dtsrun. I havent tried it and I am not sure whether it would work for SQL 2k to SQL 2k5. No warrenty. Give it a try perhaps.
Regards,
Raj
March 26, 2010 at 11:08 am
Companies always seem to skimp on Servers and phones. Something that every company needs to survive. But they always want some miracle to happen when they give you junk hardware to work with.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply