We are migrating a browser based application developed
with the Enterprise version to a server running the
standard edition. There will be some development on the
standard version before a new vresion of the application
is deployed. Can anyone help me to understand what
issues/problems we should look out for?Since most of the difference between Standard Edition and Enterprise Edition
involves scalability and availability features (>4 processors, large memory
support, clusters) there generally will be no problems migrating
applications from Enterprise to Standard. The few small gotchas include
Transparent Materialized Views and Distributed Partitioned Views, both of
which are only included in Enterprise Edition. (For completeness, Analysis
Services has some similar differences between Standard and Enterprise). If
you have not used these few scalability-related features then you will not
notice application level differences between these editions.
See "Features Supported by the Editions of SQL Server 2000" in Books Online.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"rez" <rezam@.go.com> wrote in message
news:039601c37bc5$bda02a30$a401280a@.phx.gbl...
> We are migrating a browser based application developed
> with the Enterprise version to a server running the
> standard edition. There will be some development on the
> standard version before a new vresion of the application
> is deployed. Can anyone help me to understand what
> issues/problems we should look out for?
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
Migrating from Developer Edition to Standard Edition
Hello,
I have Reporting Sevices Developer Edition running on a Windows XP Pro
workstation. I want to migrate it to Reporting Services/SQL Server Standard
Edition on a Windows 2003 server. Can this be done? Since the edition of RS
changes, and the edition of SQL Server changes, and the hardware changes, I
need to know if this is feasible, or if it would be better to redeploy the
reports to the new server. BOL isn't very helpful in this area, and I don't
have time to learn C# to use the RS utility.
Thanks in advance.Given that it is so easy to redeploy and the fact that everything is
changing, my advice is to install new and then redeploy.
Bruce L-C
"Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> Hello,
> I have Reporting Sevices Developer Edition running on a Windows XP Pro
> workstation. I want to migrate it to Reporting Services/SQL Server
Standard
> Edition on a Windows 2003 server. Can this be done? Since the edition of
RS
> changes, and the edition of SQL Server changes, and the hardware changes,
I
> need to know if this is feasible, or if it would be better to redeploy the
> reports to the new server. BOL isn't very helpful in this area, and I
don't
> have time to learn C# to use the RS utility.
> Thanks in advance.|||Others would need to jump in for your answer. I can't really help you with
that.
Bruce L-C
"Cable Guy" <CableGuy@.discussions.microsoft.com> wrote in message
news:40C1D674-60C1-49AB-B8B6-5483EE8BDE2F@.microsoft.com...
> Hi Bruce,
> Thanks for the reply. That is what I was initially persuaded to do, but
> redeploying introduces the possibility of something being overlooked, and
> that is what we want to avoid. Is it possible to use the RS utility to do
> what we need it to do (i.e. copy the logical contents of the database to a
> new database/server/edition) and have it work properly? Do you know of
> any
> good documentation on the RS utility?
> "Bruce Loehle-Conger" wrote:
>> Given that it is so easy to redeploy and the fact that everything is
>> changing, my advice is to install new and then redeploy.
>> Bruce L-C
>> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
>> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
>> > Hello,
>> >
>> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
>> > workstation. I want to migrate it to Reporting Services/SQL Server
>> Standard
>> > Edition on a Windows 2003 server. Can this be done? Since the edition
>> > of
>> RS
>> > changes, and the edition of SQL Server changes, and the hardware
>> > changes,
>> I
>> > need to know if this is feasible, or if it would be better to redeploy
>> > the
>> > reports to the new server. BOL isn't very helpful in this area, and I
>> don't
>> > have time to learn C# to use the RS utility.
>> >
>> > Thanks in advance.
>>|||Hi Bruce,
Thanks for the reply. That is what I was initially persuaded to do, but
redeploying introduces the possibility of something being overlooked, and
that is what we want to avoid. Is it possible to use the RS utility to do
what we need it to do (i.e. copy the logical contents of the database to a
new database/server/edition) and have it work properly? Do you know of any
good documentation on the RS utility?
"Bruce Loehle-Conger" wrote:
> Given that it is so easy to redeploy and the fact that everything is
> changing, my advice is to install new and then redeploy.
> Bruce L-C
> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> > Hello,
> >
> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
> > workstation. I want to migrate it to Reporting Services/SQL Server
> Standard
> > Edition on a Windows 2003 server. Can this be done? Since the edition of
> RS
> > changes, and the edition of SQL Server changes, and the hardware changes,
> I
> > need to know if this is feasible, or if it would be better to redeploy the
> > reports to the new server. BOL isn't very helpful in this area, and I
> don't
> > have time to learn C# to use the RS utility.
> >
> > Thanks in advance.
>
>sql
I have Reporting Sevices Developer Edition running on a Windows XP Pro
workstation. I want to migrate it to Reporting Services/SQL Server Standard
Edition on a Windows 2003 server. Can this be done? Since the edition of RS
changes, and the edition of SQL Server changes, and the hardware changes, I
need to know if this is feasible, or if it would be better to redeploy the
reports to the new server. BOL isn't very helpful in this area, and I don't
have time to learn C# to use the RS utility.
Thanks in advance.Given that it is so easy to redeploy and the fact that everything is
changing, my advice is to install new and then redeploy.
Bruce L-C
"Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> Hello,
> I have Reporting Sevices Developer Edition running on a Windows XP Pro
> workstation. I want to migrate it to Reporting Services/SQL Server
Standard
> Edition on a Windows 2003 server. Can this be done? Since the edition of
RS
> changes, and the edition of SQL Server changes, and the hardware changes,
I
> need to know if this is feasible, or if it would be better to redeploy the
> reports to the new server. BOL isn't very helpful in this area, and I
don't
> have time to learn C# to use the RS utility.
> Thanks in advance.|||Others would need to jump in for your answer. I can't really help you with
that.
Bruce L-C
"Cable Guy" <CableGuy@.discussions.microsoft.com> wrote in message
news:40C1D674-60C1-49AB-B8B6-5483EE8BDE2F@.microsoft.com...
> Hi Bruce,
> Thanks for the reply. That is what I was initially persuaded to do, but
> redeploying introduces the possibility of something being overlooked, and
> that is what we want to avoid. Is it possible to use the RS utility to do
> what we need it to do (i.e. copy the logical contents of the database to a
> new database/server/edition) and have it work properly? Do you know of
> any
> good documentation on the RS utility?
> "Bruce Loehle-Conger" wrote:
>> Given that it is so easy to redeploy and the fact that everything is
>> changing, my advice is to install new and then redeploy.
>> Bruce L-C
>> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
>> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
>> > Hello,
>> >
>> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
>> > workstation. I want to migrate it to Reporting Services/SQL Server
>> Standard
>> > Edition on a Windows 2003 server. Can this be done? Since the edition
>> > of
>> RS
>> > changes, and the edition of SQL Server changes, and the hardware
>> > changes,
>> I
>> > need to know if this is feasible, or if it would be better to redeploy
>> > the
>> > reports to the new server. BOL isn't very helpful in this area, and I
>> don't
>> > have time to learn C# to use the RS utility.
>> >
>> > Thanks in advance.
>>|||Hi Bruce,
Thanks for the reply. That is what I was initially persuaded to do, but
redeploying introduces the possibility of something being overlooked, and
that is what we want to avoid. Is it possible to use the RS utility to do
what we need it to do (i.e. copy the logical contents of the database to a
new database/server/edition) and have it work properly? Do you know of any
good documentation on the RS utility?
"Bruce Loehle-Conger" wrote:
> Given that it is so easy to redeploy and the fact that everything is
> changing, my advice is to install new and then redeploy.
> Bruce L-C
> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> > Hello,
> >
> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
> > workstation. I want to migrate it to Reporting Services/SQL Server
> Standard
> > Edition on a Windows 2003 server. Can this be done? Since the edition of
> RS
> > changes, and the edition of SQL Server changes, and the hardware changes,
> I
> > need to know if this is feasible, or if it would be better to redeploy the
> > reports to the new server. BOL isn't very helpful in this area, and I
> don't
> > have time to learn C# to use the RS utility.
> >
> > Thanks in advance.
>
>sql
Migrating DTS packages on instances
I have problem and don't know how to resolve it.
I'm running multiple instances(developpement,acceptation, production) on one server with the same db's on each one of them.
Now I'm trying to find out a way to transfer DTS packages from one instance to another (I know it is straight-forward if you don't have to change anything) but in my case I would want to change the connection properties within the dts package e.g.
on my server\developpement instance I've created a DTS package with connection properties server name server\developpement
Now I want to transfer it to my server\acceptation instance and there I want the connection properties server name to be server\acceptation .
I've tried doing it with a dynamic properties task but I can't seem to find the instance name where the package is saved. I can only find the server name(environment variable).
This of course is no problem if you have but a few packages, but if you have over 50 to transfer
Any help would be more than welcome.
Rosko
PS: I know it is possible between two different servers using (local) as the servername but that's not an option for my boss.I have a very similar situation. I currently have global variables. When I migrate, I am changing this from Package Properties.
If I do not find a solution, I plan to have a flat file and read the information from that file using ActiveScript.
Please let me know if you have a better solution.|||See
"LoadFromSQLServer' & 'SaveToSQLServer'
in SQLServer Books online or on the web.
this can be called in VB and will repopulate on the other server.
For more advanced changes the attached structe can be populated and manipulated in VB to create new DTS Packages.|||Correct me if I am wrong. I think this still does not address the issue.
The issue is to set the datasource property to the connections within the package(s) while the package is migrated from one instance to another.
LoadFromSQLServer and SaveToSQLServer seem to help in migrating from one instance to another.|||Unfortunately jayaramanc,
The connections held in the connections object must also be updated with the new server name, as does any ADODB connections in ActiveXScript in the Steps object.
I'm running multiple instances(developpement,acceptation, production) on one server with the same db's on each one of them.
Now I'm trying to find out a way to transfer DTS packages from one instance to another (I know it is straight-forward if you don't have to change anything) but in my case I would want to change the connection properties within the dts package e.g.
on my server\developpement instance I've created a DTS package with connection properties server name server\developpement
Now I want to transfer it to my server\acceptation instance and there I want the connection properties server name to be server\acceptation .
I've tried doing it with a dynamic properties task but I can't seem to find the instance name where the package is saved. I can only find the server name(environment variable).
This of course is no problem if you have but a few packages, but if you have over 50 to transfer
Any help would be more than welcome.
Rosko
PS: I know it is possible between two different servers using (local) as the servername but that's not an option for my boss.I have a very similar situation. I currently have global variables. When I migrate, I am changing this from Package Properties.
If I do not find a solution, I plan to have a flat file and read the information from that file using ActiveScript.
Please let me know if you have a better solution.|||See
"LoadFromSQLServer' & 'SaveToSQLServer'
in SQLServer Books online or on the web.
this can be called in VB and will repopulate on the other server.
For more advanced changes the attached structe can be populated and manipulated in VB to create new DTS Packages.|||Correct me if I am wrong. I think this still does not address the issue.
The issue is to set the datasource property to the connections within the package(s) while the package is migrated from one instance to another.
LoadFromSQLServer and SaveToSQLServer seem to help in migrating from one instance to another.|||Unfortunately jayaramanc,
The connections held in the connections object must also be updated with the new server name, as does any ADODB connections in ActiveXScript in the Steps object.
Wednesday, March 28, 2012
Migrating Clusters
Hi All,
We have a 2 node cluster running SQL Server 2000 on Windows 2000 which
needs replacing. The nature of our business is 24 hour operation, so
the downtime to do such needs to be as small as possible.
We have a new cluster ready and waiting in our data centre that is
built on Windows 2003 with SQL Server 2000.
Our issues are:
=B7Configuring all the applications to the new Cluster Name / IP
Address - In the past we have found things hard-coded
=B7Migrate all the data (~100GB) over to the new MSA
=B7Smallest downtime possible
The idea's we have come up with already are:
=B7Frigg DNS to point the old cluster name to the new cluster name and
then migrate all the applications to the new name as time permits.
=B7Rename the new nodes as <NAME>3/4 and add these into the existing
cluster; however the MSA is directly connected to Nodes 1/2.
=B7Remove 1 node from the old cluster, add a new node in, fail-over to
new node, remove 2nd old node, add new node, however I'm thinking
this may not work?
Another consideration is that we use SecurePath on the MSA's so this
could be a PITA.
Does anyone have any other suggestions which we could consider?
Thanks
Well, since you asked.
You could do a minimally disruptive replacement by replacing each node with
one of the new nodes. Uninstall SQL from the "other" node, evict the node,
add the node, run the SQL nstaller to install and configure SQL on the new
node. Wait for maintenance window and do a failover test. Repeat with the
other node.
The other process I use to migrate systems is to run a custom log ship
script (they aren't that hard to write, google will point to some nice free
examples.) to get them ready. Do a final log backup and put the old
databases into norecovery mode. Do a final catch up and go live on the new
system. Patch DNS (hint, use the SRV record type). and go. I can switch
over a typical server with 10-30 databases with less than 10 minutes of
downtime this way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve" <SMaxwell@.NationalExpress.com> wrote in message
news:1160476240.405910.76230@.e3g2000cwe.googlegrou ps.com...
Hi All,
We have a 2 node cluster running SQL Server 2000 on Windows 2000 which
needs replacing. The nature of our business is 24 hour operation, so
the downtime to do such needs to be as small as possible.
We have a new cluster ready and waiting in our data centre that is
built on Windows 2003 with SQL Server 2000.
Our issues are:
Configuring all the applications to the new Cluster Name / IP
Address - In the past we have found things hard-coded
Migrate all the data (~100GB) over to the new MSA
Smallest downtime possible
The idea's we have come up with already are:
Frigg DNS to point the old cluster name to the new cluster name and
then migrate all the applications to the new name as time permits.
Rename the new nodes as <NAME>3/4 and add these into the existing
cluster; however the MSA is directly connected to Nodes 1/2.
Remove 1 node from the old cluster, add a new node in, fail-over to
new node, remove 2nd old node, add new node, however I'm thinking
this may not work?
Another consideration is that we use SecurePath on the MSA's so this
could be a PITA.
Does anyone have any other suggestions which we could consider?
Thanks
We have a 2 node cluster running SQL Server 2000 on Windows 2000 which
needs replacing. The nature of our business is 24 hour operation, so
the downtime to do such needs to be as small as possible.
We have a new cluster ready and waiting in our data centre that is
built on Windows 2003 with SQL Server 2000.
Our issues are:
=B7Configuring all the applications to the new Cluster Name / IP
Address - In the past we have found things hard-coded
=B7Migrate all the data (~100GB) over to the new MSA
=B7Smallest downtime possible
The idea's we have come up with already are:
=B7Frigg DNS to point the old cluster name to the new cluster name and
then migrate all the applications to the new name as time permits.
=B7Rename the new nodes as <NAME>3/4 and add these into the existing
cluster; however the MSA is directly connected to Nodes 1/2.
=B7Remove 1 node from the old cluster, add a new node in, fail-over to
new node, remove 2nd old node, add new node, however I'm thinking
this may not work?
Another consideration is that we use SecurePath on the MSA's so this
could be a PITA.
Does anyone have any other suggestions which we could consider?
Thanks
Well, since you asked.
You could do a minimally disruptive replacement by replacing each node with
one of the new nodes. Uninstall SQL from the "other" node, evict the node,
add the node, run the SQL nstaller to install and configure SQL on the new
node. Wait for maintenance window and do a failover test. Repeat with the
other node.
The other process I use to migrate systems is to run a custom log ship
script (they aren't that hard to write, google will point to some nice free
examples.) to get them ready. Do a final log backup and put the old
databases into norecovery mode. Do a final catch up and go live on the new
system. Patch DNS (hint, use the SRV record type). and go. I can switch
over a typical server with 10-30 databases with less than 10 minutes of
downtime this way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Steve" <SMaxwell@.NationalExpress.com> wrote in message
news:1160476240.405910.76230@.e3g2000cwe.googlegrou ps.com...
Hi All,
We have a 2 node cluster running SQL Server 2000 on Windows 2000 which
needs replacing. The nature of our business is 24 hour operation, so
the downtime to do such needs to be as small as possible.
We have a new cluster ready and waiting in our data centre that is
built on Windows 2003 with SQL Server 2000.
Our issues are:
Configuring all the applications to the new Cluster Name / IP
Address - In the past we have found things hard-coded
Migrate all the data (~100GB) over to the new MSA
Smallest downtime possible
The idea's we have come up with already are:
Frigg DNS to point the old cluster name to the new cluster name and
then migrate all the applications to the new name as time permits.
Rename the new nodes as <NAME>3/4 and add these into the existing
cluster; however the MSA is directly connected to Nodes 1/2.
Remove 1 node from the old cluster, add a new node in, fail-over to
new node, remove 2nd old node, add new node, however I'm thinking
this may not work?
Another consideration is that we use SecurePath on the MSA's so this
could be a PITA.
Does anyone have any other suggestions which we could consider?
Thanks
Monday, March 26, 2012
Migrating a database
We have a database running under SQL Server 7 on a Win NT server. We
have recently added Win2K servers, created a new domain and installed
SQL Server 7 on one of the servers. I would like to be able to log into
either domain from a workstation, use Enterprise Manager to access
either database and use the migration tools to copy the database from
the NT server to the 2K server.
We have created a trust relationship that allows me to log into either
domain, but when I try to use Enterprise Manager to create a
registration to the database on the other domain, or try to use the
migration tool to copy the database, I get an error saying that my user
account cannot log into the other system. ( I have the same user name
and password on both domains and domain admin privileges).
Can this be done directly? How?
Do I need "linked servers"? -- It doesn't like my account to create this
either.
Do I need a "remote server"?
Reading the documentation creates more questions than answers. I would
appreciate someone pointing me in the right direction. ThanksWhat is the exact error you are seeing and which domain are you logging
into, the new one or the other one.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||When I'm logged into the NT domain and trying to register SQL Server on the 2K
server, I get "login failed for user 'patenaude'".
When I'm logged into the 2K domain and trying to register SQL server on the NT
server, I get " loged failed for user 'null'. Not associated with a trusted
SQL Server connection."
I get similar messages trying to set up a linked server.
"Rand Boyd [MS]" wrote:
> What is the exact error you are seeing and which domain are you logging
> into, the new one or the other one.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||When you are logged into the Windows NT domain are you trying to register
the SQL Server using NT or SQL authentication. The error indicates SQL
authentication. If so does the 'patenaude' SQL login exist? Is the password
correct?
When you are logging into the Win 2K domain, the error indicates that you
cannot be validated on the domain controller. That is where the null is
coming from. Check the security log of the domain controller for this
domain for errors related to this account. Also if you are logged on to the
workstation using a local account you will see this error.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I changed the system on the 2K server to use NT authentication, so now I get
the same error message about my user account not being valid. (The user id is
good, the password is good.) My workstation is a member of the NT domain and
the trust relationship is set up to the point where I can log into either
domain. The user name is the same on both domains. I have tried registering
across the domain from both directions, using the default log-in with NT
authentication, using the specific login with my user name and using the
specific log-in with each of the domains added ( NTdomain\user & 2Kdomain\user)
I get the same results each time. I looked in the event logs on the servers and
there is no mention of the attempt in any of the logs.
"Rand Boyd [MS]" wrote:
> When you are logged into the Windows NT domain are you trying to register
> the SQL Server using NT or SQL authentication. The error indicates SQL
> authentication. If so does the 'patenaude' SQL login exist? Is the password
> correct?
> When you are logging into the Win 2K domain, the error indicates that you
> cannot be validated on the domain controller. That is where the null is
> coming from. Check the security log of the domain controller for this
> domain for errors related to this account. Also if you are logged on to the
> workstation using a local account you will see this error.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
have recently added Win2K servers, created a new domain and installed
SQL Server 7 on one of the servers. I would like to be able to log into
either domain from a workstation, use Enterprise Manager to access
either database and use the migration tools to copy the database from
the NT server to the 2K server.
We have created a trust relationship that allows me to log into either
domain, but when I try to use Enterprise Manager to create a
registration to the database on the other domain, or try to use the
migration tool to copy the database, I get an error saying that my user
account cannot log into the other system. ( I have the same user name
and password on both domains and domain admin privileges).
Can this be done directly? How?
Do I need "linked servers"? -- It doesn't like my account to create this
either.
Do I need a "remote server"?
Reading the documentation creates more questions than answers. I would
appreciate someone pointing me in the right direction. ThanksWhat is the exact error you are seeing and which domain are you logging
into, the new one or the other one.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||When I'm logged into the NT domain and trying to register SQL Server on the 2K
server, I get "login failed for user 'patenaude'".
When I'm logged into the 2K domain and trying to register SQL server on the NT
server, I get " loged failed for user 'null'. Not associated with a trusted
SQL Server connection."
I get similar messages trying to set up a linked server.
"Rand Boyd [MS]" wrote:
> What is the exact error you are seeing and which domain are you logging
> into, the new one or the other one.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||When you are logged into the Windows NT domain are you trying to register
the SQL Server using NT or SQL authentication. The error indicates SQL
authentication. If so does the 'patenaude' SQL login exist? Is the password
correct?
When you are logging into the Win 2K domain, the error indicates that you
cannot be validated on the domain controller. That is where the null is
coming from. Check the security log of the domain controller for this
domain for errors related to this account. Also if you are logged on to the
workstation using a local account you will see this error.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I changed the system on the 2K server to use NT authentication, so now I get
the same error message about my user account not being valid. (The user id is
good, the password is good.) My workstation is a member of the NT domain and
the trust relationship is set up to the point where I can log into either
domain. The user name is the same on both domains. I have tried registering
across the domain from both directions, using the default log-in with NT
authentication, using the specific login with my user name and using the
specific log-in with each of the domains added ( NTdomain\user & 2Kdomain\user)
I get the same results each time. I looked in the event logs on the servers and
there is no mention of the attempt in any of the logs.
"Rand Boyd [MS]" wrote:
> When you are logged into the Windows NT domain are you trying to register
> the SQL Server using NT or SQL authentication. The error indicates SQL
> authentication. If so does the 'patenaude' SQL login exist? Is the password
> correct?
> When you are logging into the Win 2K domain, the error indicates that you
> cannot be validated on the domain controller. That is where the null is
> coming from. Check the security log of the domain controller for this
> domain for errors related to this account. Also if you are logged on to the
> workstation using a local account you will see this error.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
Friday, March 23, 2012
Migrate two databases from diferente Server into one consolidate
Hi,
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and ru
n
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.si
d
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest
).
> I Know how to backup and restore databases from one server to another, but
I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/feat...cle.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefe
r using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQ
droptable.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQ
droptable.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the postin
g
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides co
mpared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQ
droptable.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and ru
n
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.si
d
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest
).
> I Know how to backup and restore databases from one server to another, but
I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/feat...cle.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefe
r using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQ
droptable.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQ
droptable.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the postin
g
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides co
mpared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQ
droptable.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
Migrate two databases from diferente Server into one consolidate
Hi,
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio Campos
What i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/featu...le.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO
|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.droptable.co m...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO
|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com
|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.droptable.co m...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com
|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com
|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.droptable.co m...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio Campos
What i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/featu...le.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos
|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO
|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via droptable.com" <forum@.droptable.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.droptable.co m...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO
|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
Message posted via http://www.droptable.com
|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.droptable.co m...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.droptable.com
|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
Message posted via http://www.droptable.com
|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via droptable.com" <forum@.droptable.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.droptable.co m...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.droptable.com
Migrate two databases from diferente Server into one consolidate
Hi,
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/features/mssql/article.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQLMonster.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
--
Message posted via http://www.sqlmonster.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQLMonster.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.sqlmonster.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
--
Message posted via http://www.sqlmonster.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQLMonster.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.sqlmonster.comsql
I have two servers running Sql-Server, recently we bought a new server to
consolidate the databases (The new server in must powerful that the oldest).
I Know how to backup and restore databases from one server to another, but I
dont know how to move the logins, some body know how I can move o backup
login from two servers and then restore in only one.
Thanks,
Mauricio CamposWhat i do is create the same login names on the new server, I then go and run
this command after the restor is done. Then all i have to do is reset each
login with the correct default databases. Here's the command:
sp_configure 'Allow updates', 1
RECONFIGURE WITH OVERRIDE
go
begin
declare @.databasename varchar(50)
select @.databasename = 'Put your database name here'
declare @.statement varchar(255)
select @.statement = 'update '+ @.databasename + '.dbo.sysusers set sid = m.sid
from master.dbo.syslogins m
where ' + @.databasename + '.dbo.sysusers.name = m.name'
execute (@.statement)
end
go
sp_configure 'Allow updates', 0
RECONFIGURE WITH OVERRIDE
go
Hope this helps
"Mauricio Campos" wrote:
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the oldest).
> I Know how to backup and restore databases from one server to another, but I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||Hi,
Have a look into this article as well. Since you are populating the logins
from multiple sql servers you may need to do some manual sync using
sp_change_users_login system stored proc.
http://www.databasejournal.com/features/mssql/article.php/1438491
Thanks
Hari
SQL Server MVP
"Mauricio Campos" <MauricioCampos@.discussions.microsoft.com> wrote in
message news:17869C0F-FE6E-4E9D-BCBF-D59369A752C5@.microsoft.com...
> Hi,
> I have two servers running Sql-Server, recently we bought a new server to
> consolidate the databases (The new server in must powerful that the
> oldest).
> I Know how to backup and restore databases from one server to another, but
> I
> dont know how to move the logins, some body know how I can move o backup
> login from two servers and then restore in only one.
> Thanks,
> Mauricio Campos|||I've never gotten around to using it, but have a look at DTs - there is a
transfer login's task, I believe. Google / BOL it to get more detail (I'll
try and find somethings on it when I have a moment).
CiaO|||the problem with this task is that it doesn't keep the original SID. I prefer using sp_help_revlogin
which is fund in the KB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Regan Galbraith via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:052be1beaac14f44905f5fe6eaf03ab1@.SQLMonster.com...
> I've never gotten around to using it, but have a look at DTs - there is a
> transfer login's task, I believe. Google / BOL it to get more detail (I'll
> try and find somethings on it when I have a moment).
> CiaO|||Also, the DTS transfer logins task doesn't copy across the passwords.
The technique that I've used successfully in the past many times is a bit
fiddly but gives 100% success:
1. Use either a transfer logins task or SQLEM scripting to create logins
on the new server.
2. Use DTS to copy the sysxlogins table to a separate database on the new
server - we used to use Northwind
3. In the same DTS, run a looped statement to directly update the system
tables on the new server, overwriting the blank passwords with the
encrypted versions (don't forget to include a step to delete the temporary
table afterwards).
All the logins should now be on the server, but as Tibor pointed out, they
have new SIDs which will not match those in any restored databases.
4. In each restored database, run another 'fixusers' SP which we adapted
from some code on a forum several years ago to sync the SIDs (matches
logins by name only)
All of this is obviously quite risky, as you are directly updating system
tables, and I would only recommend this to be carried out on a non-
production server. It works for SQL7.x and 2000 but will not be possible
on SQL2005 due to the system tables being hidden.
I can include the SP code if required, but didn't want to bloat the posting
unnecessarily. Not sure if its possible to include the DTS as an
attachment, but it's fairly straigthforward anyway.
HTH
Andy H
--
Message posted via http://www.sqlmonster.com|||Andy,
I'm curious... Is there a reason you don't use sp_help_revlogins?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ec6f8ddb169e4892bac0f4d0c24c539e@.SQLMonster.com...
> Also, the DTS transfer logins task doesn't copy across the passwords.
> The technique that I've used successfully in the past many times is a bit
> fiddly but gives 100% success:
> 1. Use either a transfer logins task or SQLEM scripting to create logins
> on the new server.
> 2. Use DTS to copy the sysxlogins table to a separate database on the new
> server - we used to use Northwind
> 3. In the same DTS, run a looped statement to directly update the system
> tables on the new server, overwriting the blank passwords with the
> encrypted versions (don't forget to include a step to delete the temporary
> table afterwards).
> All the logins should now be on the server, but as Tibor pointed out, they
> have new SIDs which will not match those in any restored databases.
> 4. In each restored database, run another 'fixusers' SP which we adapted
> from some code on a forum several years ago to sync the SIDs (matches
> logins by name only)
>
> All of this is obviously quite risky, as you are directly updating system
> tables, and I would only recommend this to be carried out on a non-
> production server. It works for SQL7.x and 2000 but will not be possible
> on SQL2005 due to the system tables being hidden.
> I can include the SP code if required, but didn't want to bloat the posting
> unnecessarily. Not sure if its possible to include the DTS as an
> attachment, but it's fairly straigthforward anyway.
> HTH
> Andy H
> --
> Message posted via http://www.sqlmonster.com|||Tibor
Ignorance is my only excuse ;-)
You know how it goes - if it isn't broken, why fix it. This process was
developed in the early days of SQL 7 and DTS through necessity and
continued to work well on SQL 2000. I'd missed the publication of this KB
article. A much better way of doing things.
Thanks for the info.
To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
Regards
Andy
--
Message posted via http://www.sqlmonster.com|||Andy,
> You know how it goes - if it isn't broken, why fix it.
Sure do :-). I was just curious whether you had found significant upsides compared to
sp_help_revlogins...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andy Hughes via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20a0eede24724d2681acf9129d27fc7d@.SQLMonster.com...
> Tibor
> Ignorance is my only excuse ;-)
> You know how it goes - if it isn't broken, why fix it. This process was
> developed in the early days of SQL 7 and DTS through necessity and
> continued to work well on SQL 2000. I'd missed the publication of this KB
> article. A much better way of doing things.
> Thanks for the info.
> To save Mauricio some time: http://support.microsoft.com/kb/246133/#5
> Regards
> Andy
> --
> Message posted via http://www.sqlmonster.comsql
Migrate to New Domain
I migrated my SQL 7 server running on Win 2K Server from
the existing domain to a new domain using the Active
Directory Migration tool but now SQL won't start and I
cannot get it to start whether I log on to the old or new
domains. Does anyone have an insight?Wht error do yo get? I guess SQL Server service can't logo to Windows
domain. Check te username and password SQL Server service is using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:54af01c42cdd$7a56a5f0$a101280a@.phx.gbl...
> I migrated my SQL 7 server running on Win 2K Server from
> the existing domain to a new domain using the Active
> Directory Migration tool but now SQL won't start and I
> cannot get it to start whether I log on to the old or new
> domains. Does anyone have an insight?
the existing domain to a new domain using the Active
Directory Migration tool but now SQL won't start and I
cannot get it to start whether I log on to the old or new
domains. Does anyone have an insight?Wht error do yo get? I guess SQL Server service can't logo to Windows
domain. Check te username and password SQL Server service is using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:54af01c42cdd$7a56a5f0$a101280a@.phx.gbl...
> I migrated my SQL 7 server running on Win 2K Server from
> the existing domain to a new domain using the Active
> Directory Migration tool but now SQL won't start and I
> cannot get it to start whether I log on to the old or new
> domains. Does anyone have an insight?
Migrate to New Domain
I migrated my SQL 7 server running on Win 2K Server from
the existing domain to a new domain using the Active
Directory Migration tool but now SQL won't start and I
cannot get it to start whether I log on to the old or new
domains. Does anyone have an insight?
Wht error do yo get? I guess SQL Server service can't logo to Windows
domain. Check te username and password SQL Server service is using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:54af01c42cdd$7a56a5f0$a101280a@.phx.gbl...
> I migrated my SQL 7 server running on Win 2K Server from
> the existing domain to a new domain using the Active
> Directory Migration tool but now SQL won't start and I
> cannot get it to start whether I log on to the old or new
> domains. Does anyone have an insight?
sql
the existing domain to a new domain using the Active
Directory Migration tool but now SQL won't start and I
cannot get it to start whether I log on to the old or new
domains. Does anyone have an insight?
Wht error do yo get? I guess SQL Server service can't logo to Windows
domain. Check te username and password SQL Server service is using.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:54af01c42cdd$7a56a5f0$a101280a@.phx.gbl...
> I migrated my SQL 7 server running on Win 2K Server from
> the existing domain to a new domain using the Active
> Directory Migration tool but now SQL won't start and I
> cannot get it to start whether I log on to the old or new
> domains. Does anyone have an insight?
sql
Migrate to New Domain
I migrated my SQL 7 server running on Win 2K Server from
the existing domain to a new domain using the Active
Directory Migration tool but now SQL won't start and I
cannot get it to start whether I log on to the old or new
domains. Does anyone have an insight?Wht error do yo get? I guess SQL Server service can't logo to Windows
domain. Check te username and password SQL Server service is using.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:54af01c42cdd$7a56a5f0$a101280a@.phx.gbl...
> I migrated my SQL 7 server running on Win 2K Server from
> the existing domain to a new domain using the Active
> Directory Migration tool but now SQL won't start and I
> cannot get it to start whether I log on to the old or new
> domains. Does anyone have an insight?
the existing domain to a new domain using the Active
Directory Migration tool but now SQL won't start and I
cannot get it to start whether I log on to the old or new
domains. Does anyone have an insight?Wht error do yo get? I guess SQL Server service can't logo to Windows
domain. Check te username and password SQL Server service is using.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:54af01c42cdd$7a56a5f0$a101280a@.phx.gbl...
> I migrated my SQL 7 server running on Win 2K Server from
> the existing domain to a new domain using the Active
> Directory Migration tool but now SQL won't start and I
> cannot get it to start whether I log on to the old or new
> domains. Does anyone have an insight?
Migrate SQL Server 2000 to a new server
Hi all
My organisation has SQL Server 2000 running on Windows 2000.
We've just had most of our hardware replaced and SQL Server needs to
be transferred to a better machine running Windows Server 2003.
Are there any tools that could help in doing that? In my
understanding, the databases themselves can simply be backed up and
restored on the new machine. But how can all the existing settings,
users and roles be copied to the new server?
Thanks
Hi Manu,
You may want to take a look at this article. It includes information on how
to move databases, logins and passwords, jobs, DTS packages, etc.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
>
|||On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Manu,
> You may want to take a look at this article. It includes information on how
> to move databases, logins and passwords, jobs, DTS packages, etc.
> How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
Hi Ben
Thanks for the reply.
According to the link, in order to transfer usernames and passwords to
SQL Server running on a different machine, the instance should be
running under a domain account. But that is not the case with our
setup. SQL Server is running under local machine accounts.
Any thoughts?
Thanks
|||Manu,
That is only for the DTS Transfer Logins Tasks. You can still use the method
1 of the section 'A complete resolution to transfer logins and passwords
between different versions of SQL Server' and will get the additional benefit
of transfering the original login SID.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> Hi Ben
> Thanks for the reply.
> According to the link, in order to transfer usernames and passwords to
> SQL Server running on a different machine, the instance should be
> running under a domain account. But that is not the case with our
> setup. SQL Server is running under local machine accounts.
> Any thoughts?
> Thanks
>
|||Manu
http://vyaskn.tripod.com/moving_sql_server.htm
"Manu" <mandeep.sekhon@.gmail.com> wrote in message
news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
|||On Dec 17, 3:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Manuhttp://vyaskn.tripod.com/moving_sql_server.htm
> "Manu" <mandeep.sek...@.gmail.com> wrote in message
> news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
>
>
Hi Uri
Thanks for the reply.
The link that you sent me requires that SQL Server be running on the
same operating system on both the machines.
In my case, however, the OS is changing too. The new machine runs
Windows Server 2003 while the older one was running Windows 2000.
|||On Dec 14, 6:09 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Manu,
> That is only for the DTS Transfer Logins Tasks. You can still use the method
> 1 of the section 'A complete resolution to transfer logins and passwords
> between different versions of SQL Server' and will get the additional benefit
> of transfering the original login SID.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
>
>
>
>
>
Hi Ben
That worked beautifully.
As mentioned on the web page, default databases assigned to the logins
are different.
Is there any way to assign the default databases apart from writing a
script using 'sp_defaultdb' for each login?
Regards
My organisation has SQL Server 2000 running on Windows 2000.
We've just had most of our hardware replaced and SQL Server needs to
be transferred to a better machine running Windows Server 2003.
Are there any tools that could help in doing that? In my
understanding, the databases themselves can simply be backed up and
restored on the new machine. But how can all the existing settings,
users and roles be copied to the new server?
Thanks
Hi Manu,
You may want to take a look at this article. It includes information on how
to move databases, logins and passwords, jobs, DTS packages, etc.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
>
|||On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Manu,
> You may want to take a look at this article. It includes information on how
> to move databases, logins and passwords, jobs, DTS packages, etc.
> How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
Hi Ben
Thanks for the reply.
According to the link, in order to transfer usernames and passwords to
SQL Server running on a different machine, the instance should be
running under a domain account. But that is not the case with our
setup. SQL Server is running under local machine accounts.
Any thoughts?
Thanks
|||Manu,
That is only for the DTS Transfer Logins Tasks. You can still use the method
1 of the section 'A complete resolution to transfer logins and passwords
between different versions of SQL Server' and will get the additional benefit
of transfering the original login SID.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> Hi Ben
> Thanks for the reply.
> According to the link, in order to transfer usernames and passwords to
> SQL Server running on a different machine, the instance should be
> running under a domain account. But that is not the case with our
> setup. SQL Server is running under local machine accounts.
> Any thoughts?
> Thanks
>
|||Manu
http://vyaskn.tripod.com/moving_sql_server.htm
"Manu" <mandeep.sekhon@.gmail.com> wrote in message
news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
|||On Dec 17, 3:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Manuhttp://vyaskn.tripod.com/moving_sql_server.htm
> "Manu" <mandeep.sek...@.gmail.com> wrote in message
> news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
>
>
Hi Uri
Thanks for the reply.
The link that you sent me requires that SQL Server be running on the
same operating system on both the machines.
In my case, however, the OS is changing too. The new machine runs
Windows Server 2003 while the older one was running Windows 2000.
|||On Dec 14, 6:09 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Manu,
> That is only for the DTS Transfer Logins Tasks. You can still use the method
> 1 of the section 'A complete resolution to transfer logins and passwords
> between different versions of SQL Server' and will get the additional benefit
> of transfering the original login SID.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
>
>
>
>
>
Hi Ben
That worked beautifully.
As mentioned on the web page, default databases assigned to the logins
are different.
Is there any way to assign the default databases apart from writing a
script using 'sp_defaultdb' for each login?
Regards
Migrate SQL Server 2000 to a new server
Hi all
My organisation has SQL Server 2000 running on Windows 2000.
We've just had most of our hardware replaced and SQL Server needs to
be transferred to a better machine running Windows Server 2003.
Are there any tools that could help in doing that? In my
understanding, the databases themselves can simply be backed up and
restored on the new machine. But how can all the existing settings,
users and roles be copied to the new server?
ThanksHi Manu,
You may want to take a look at this article. It includes information on how
to move databases, logins and passwords, jobs, DTS packages, etc.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
>|||On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi Manu,
> You may want to take a look at this article. It includes information on how
> to move databases, logins and passwords, jobs, DTS packages, etc.
> How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
> > Hi all
> > My organisation has SQL Server 2000 running on Windows 2000.
> > We've just had most of our hardware replaced and SQL Server needs to
> > be transferred to a better machine running Windows Server 2003.
> > Are there any tools that could help in doing that? In my
> > understanding, the databases themselves can simply be backed up and
> > restored on the new machine. But how can all the existing settings,
> > users and roles be copied to the new server?
> > Thanks
Hi Ben
Thanks for the reply.
According to the link, in order to transfer usernames and passwords to
SQL Server running on a different machine, the instance should be
running under a domain account. But that is not the case with our
setup. SQL Server is running under local machine accounts.
Any thoughts?
Thanks|||Manu,
That is only for the DTS Transfer Logins Tasks. You can still use the method
1 of the section 'A complete resolution to transfer logins and passwords
between different versions of SQL Server' and will get the additional benefit
of transfering the original login SID.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> > Hi Manu,
> >
> > You may want to take a look at this article. It includes information on how
> > to move databases, logins and passwords, jobs, DTS packages, etc.
> >
> > How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> > "Manu" wrote:
> > > Hi all
> >
> > > My organisation has SQL Server 2000 running on Windows 2000.
> > > We've just had most of our hardware replaced and SQL Server needs to
> > > be transferred to a better machine running Windows Server 2003.
> >
> > > Are there any tools that could help in doing that? In my
> > > understanding, the databases themselves can simply be backed up and
> > > restored on the new machine. But how can all the existing settings,
> > > users and roles be copied to the new server?
> >
> > > Thanks
> Hi Ben
> Thanks for the reply.
> According to the link, in order to transfer usernames and passwords to
> SQL Server running on a different machine, the instance should be
> running under a domain account. But that is not the case with our
> setup. SQL Server is running under local machine accounts.
> Any thoughts?
> Thanks
>|||Manu
http://vyaskn.tripod.com/moving_sql_server.htm
"Manu" <mandeep.sekhon@.gmail.com> wrote in message
news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks|||On Dec 17, 3:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Manuhttp://vyaskn.tripod.com/moving_sql_server.htm
> "Manu" <mandeep.sek...@.gmail.com> wrote in message
> news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> > Hi all
> > My organisation has SQL Server 2000 running on Windows 2000.
> > We've just had most of our hardware replaced and SQL Server needs to
> > be transferred to a better machine running Windows Server 2003.
> > Are there any tools that could help in doing that? In my
> > understanding, the databases themselves can simply be backed up and
> > restored on the new machine. But how can all the existing settings,
> > users and roles be copied to the new server?
> > Thanks
Hi Uri
Thanks for the reply.
The link that you sent me requires that SQL Server be running on the
same operating system on both the machines.
In my case, however, the OS is changing too. The new machine runs
Windows Server 2003 while the older one was running Windows 2000.|||On Dec 14, 6:09 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Manu,
> That is only for the DTS Transfer Logins Tasks. You can still use the method
> 1 of the section 'A complete resolution to transfer logins and passwords
> between different versions of SQL Server' and will get the additional benefit
> of transfering the original login SID.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
> > On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> > wrote:
> > > Hi Manu,
> > > You may want to take a look at this article. It includes information on how
> > > to move databases, logins and passwords, jobs, DTS packages, etc.
> > > How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> > > Hope this helps,
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > > "Manu" wrote:
> > > > Hi all
> > > > My organisation has SQL Server 2000 running on Windows 2000.
> > > > We've just had most of our hardware replaced and SQL Server needs to
> > > > be transferred to a better machine running Windows Server 2003.
> > > > Are there any tools that could help in doing that? In my
> > > > understanding, the databases themselves can simply be backed up and
> > > > restored on the new machine. But how can all the existing settings,
> > > > users and roles be copied to the new server?
> > > > Thanks
> > Hi Ben
> > Thanks for the reply.
> > According to the link, in order to transfer usernames and passwords to
> > SQL Server running on a different machine, the instance should be
> > running under a domain account. But that is not the case with our
> > setup. SQL Server is running under local machine accounts.
> > Any thoughts?
> > Thanks
Hi Ben
That worked beautifully.
As mentioned on the web page, default databases assigned to the logins
are different.
Is there any way to assign the default databases apart from writing a
script using 'sp_defaultdb' for each login?
Regards
My organisation has SQL Server 2000 running on Windows 2000.
We've just had most of our hardware replaced and SQL Server needs to
be transferred to a better machine running Windows Server 2003.
Are there any tools that could help in doing that? In my
understanding, the databases themselves can simply be backed up and
restored on the new machine. But how can all the existing settings,
users and roles be copied to the new server?
ThanksHi Manu,
You may want to take a look at this article. It includes information on how
to move databases, logins and passwords, jobs, DTS packages, etc.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
>|||On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi Manu,
> You may want to take a look at this article. It includes information on how
> to move databases, logins and passwords, jobs, DTS packages, etc.
> How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
> > Hi all
> > My organisation has SQL Server 2000 running on Windows 2000.
> > We've just had most of our hardware replaced and SQL Server needs to
> > be transferred to a better machine running Windows Server 2003.
> > Are there any tools that could help in doing that? In my
> > understanding, the databases themselves can simply be backed up and
> > restored on the new machine. But how can all the existing settings,
> > users and roles be copied to the new server?
> > Thanks
Hi Ben
Thanks for the reply.
According to the link, in order to transfer usernames and passwords to
SQL Server running on a different machine, the instance should be
running under a domain account. But that is not the case with our
setup. SQL Server is running under local machine accounts.
Any thoughts?
Thanks|||Manu,
That is only for the DTS Transfer Logins Tasks. You can still use the method
1 of the section 'A complete resolution to transfer logins and passwords
between different versions of SQL Server' and will get the additional benefit
of transfering the original login SID.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> > Hi Manu,
> >
> > You may want to take a look at this article. It includes information on how
> > to move databases, logins and passwords, jobs, DTS packages, etc.
> >
> > How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> > "Manu" wrote:
> > > Hi all
> >
> > > My organisation has SQL Server 2000 running on Windows 2000.
> > > We've just had most of our hardware replaced and SQL Server needs to
> > > be transferred to a better machine running Windows Server 2003.
> >
> > > Are there any tools that could help in doing that? In my
> > > understanding, the databases themselves can simply be backed up and
> > > restored on the new machine. But how can all the existing settings,
> > > users and roles be copied to the new server?
> >
> > > Thanks
> Hi Ben
> Thanks for the reply.
> According to the link, in order to transfer usernames and passwords to
> SQL Server running on a different machine, the instance should be
> running under a domain account. But that is not the case with our
> setup. SQL Server is running under local machine accounts.
> Any thoughts?
> Thanks
>|||Manu
http://vyaskn.tripod.com/moving_sql_server.htm
"Manu" <mandeep.sekhon@.gmail.com> wrote in message
news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks|||On Dec 17, 3:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Manuhttp://vyaskn.tripod.com/moving_sql_server.htm
> "Manu" <mandeep.sek...@.gmail.com> wrote in message
> news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> > Hi all
> > My organisation has SQL Server 2000 running on Windows 2000.
> > We've just had most of our hardware replaced and SQL Server needs to
> > be transferred to a better machine running Windows Server 2003.
> > Are there any tools that could help in doing that? In my
> > understanding, the databases themselves can simply be backed up and
> > restored on the new machine. But how can all the existing settings,
> > users and roles be copied to the new server?
> > Thanks
Hi Uri
Thanks for the reply.
The link that you sent me requires that SQL Server be running on the
same operating system on both the machines.
In my case, however, the OS is changing too. The new machine runs
Windows Server 2003 while the older one was running Windows 2000.|||On Dec 14, 6:09 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Manu,
> That is only for the DTS Transfer Logins Tasks. You can still use the method
> 1 of the section 'A complete resolution to transfer logins and passwords
> between different versions of SQL Server' and will get the additional benefit
> of transfering the original login SID.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
> > On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> > wrote:
> > > Hi Manu,
> > > You may want to take a look at this article. It includes information on how
> > > to move databases, logins and passwords, jobs, DTS packages, etc.
> > > How to move databases between computers that are running SQL Serverhttp://support.microsoft.com/kb/314546/
> > > Hope this helps,
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > > "Manu" wrote:
> > > > Hi all
> > > > My organisation has SQL Server 2000 running on Windows 2000.
> > > > We've just had most of our hardware replaced and SQL Server needs to
> > > > be transferred to a better machine running Windows Server 2003.
> > > > Are there any tools that could help in doing that? In my
> > > > understanding, the databases themselves can simply be backed up and
> > > > restored on the new machine. But how can all the existing settings,
> > > > users and roles be copied to the new server?
> > > > Thanks
> > Hi Ben
> > Thanks for the reply.
> > According to the link, in order to transfer usernames and passwords to
> > SQL Server running on a different machine, the instance should be
> > running under a domain account. But that is not the case with our
> > setup. SQL Server is running under local machine accounts.
> > Any thoughts?
> > Thanks
Hi Ben
That worked beautifully.
As mentioned on the web page, default databases assigned to the logins
are different.
Is there any way to assign the default databases apart from writing a
script using 'sp_defaultdb' for each login?
Regards
Migrate SQL Server 2000 to a new server
Hi all
My organisation has SQL Server 2000 running on Windows 2000.
We've just had most of our hardware replaced and SQL Server needs to
be transferred to a better machine running Windows Server 2003.
Are there any tools that could help in doing that? In my
understanding, the databases themselves can simply be backed up and
restored on the new machine. But how can all the existing settings,
users and roles be copied to the new server?
ThanksHi Manu,
You may want to take a look at this article. It includes information on how
to move databases, logins and passwords, jobs, DTS packages, etc.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
>|||On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Manu,
> You may want to take a look at this article. It includes information on ho
w
> to move databases, logins and passwords, jobs, DTS packages, etc.
> How to move databases between computers that are running SQL Serverhttp://
support.microsoft.com/kb/314546/
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
>
>
Hi Ben
Thanks for the reply.
According to the link, in order to transfer usernames and passwords to
SQL Server running on a different machine, the instance should be
running under a domain account. But that is not the case with our
setup. SQL Server is running under local machine accounts.
Any thoughts?
Thanks|||Manu,
That is only for the DTS Transfer Logins Tasks. You can still use the method
1 of the section 'A complete resolution to transfer logins and passwords
between different versions of SQL Server' and will get the additional benefi
t
of transfering the original login SID.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> Hi Ben
> Thanks for the reply.
> According to the link, in order to transfer usernames and passwords to
> SQL Server running on a different machine, the instance should be
> running under a domain account. But that is not the case with our
> setup. SQL Server is running under local machine accounts.
> Any thoughts?
> Thanks
>|||Manu
http://vyaskn.tripod.com/moving_sql_server.htm
"Manu" <mandeep.sekhon@.gmail.com> wrote in message
news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks|||On Dec 17, 3:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Manuhttp://vyaskn.tripod.com/moving_sql_server.htm
> "Manu" <mandeep.sek...@.gmail.com> wrote in message
> news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
>
>
>
>
Hi Uri
Thanks for the reply.
The link that you sent me requires that SQL Server be running on the
same operating system on both the machines.
In my case, however, the OS is changing too. The new machine runs
Windows Server 2003 while the older one was running Windows 2000.|||On Dec 14, 6:09 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Manu,
> That is only for the DTS Transfer Logins Tasks. You can still use the meth
od
> 1 of the section 'A complete resolution to transfer logins and passwords
> between different versions of SQL Server' and will get the additional bene
fit
> of transfering the original login SID.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
Hi Ben
That worked beautifully.
As mentioned on the web page, default databases assigned to the logins
are different.
Is there any way to assign the default databases apart from writing a
script using 'sp_defaultdb' for each login?
Regards
My organisation has SQL Server 2000 running on Windows 2000.
We've just had most of our hardware replaced and SQL Server needs to
be transferred to a better machine running Windows Server 2003.
Are there any tools that could help in doing that? In my
understanding, the databases themselves can simply be backed up and
restored on the new machine. But how can all the existing settings,
users and roles be copied to the new server?
ThanksHi Manu,
You may want to take a look at this article. It includes information on how
to move databases, logins and passwords, jobs, DTS packages, etc.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/kb/314546/
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks
>|||On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Manu,
> You may want to take a look at this article. It includes information on ho
w
> to move databases, logins and passwords, jobs, DTS packages, etc.
> How to move databases between computers that are running SQL Serverhttp://
support.microsoft.com/kb/314546/
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
>
>
Hi Ben
Thanks for the reply.
According to the link, in order to transfer usernames and passwords to
SQL Server running on a different machine, the instance should be
running under a domain account. But that is not the case with our
setup. SQL Server is running under local machine accounts.
Any thoughts?
Thanks|||Manu,
That is only for the DTS Transfer Logins Tasks. You can still use the method
1 of the section 'A complete resolution to transfer logins and passwords
between different versions of SQL Server' and will get the additional benefi
t
of transfering the original login SID.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Manu" wrote:
> On Dec 14, 1:13 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> Hi Ben
> Thanks for the reply.
> According to the link, in order to transfer usernames and passwords to
> SQL Server running on a different machine, the instance should be
> running under a domain account. But that is not the case with our
> setup. SQL Server is running under local machine accounts.
> Any thoughts?
> Thanks
>|||Manu
http://vyaskn.tripod.com/moving_sql_server.htm
"Manu" <mandeep.sekhon@.gmail.com> wrote in message
news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
> Hi all
> My organisation has SQL Server 2000 running on Windows 2000.
> We've just had most of our hardware replaced and SQL Server needs to
> be transferred to a better machine running Windows Server 2003.
> Are there any tools that could help in doing that? In my
> understanding, the databases themselves can simply be backed up and
> restored on the new machine. But how can all the existing settings,
> users and roles be copied to the new server?
> Thanks|||On Dec 17, 3:06 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Manuhttp://vyaskn.tripod.com/moving_sql_server.htm
> "Manu" <mandeep.sek...@.gmail.com> wrote in message
> news:839ffaa6-d07f-4f5a-a224-4f338dbb55ff@.s19g2000prg.googlegroups.com...
>
>
>
>
Hi Uri
Thanks for the reply.
The link that you sent me requires that SQL Server be running on the
same operating system on both the machines.
In my case, however, the OS is changing too. The new machine runs
Windows Server 2003 while the older one was running Windows 2000.|||On Dec 14, 6:09 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Manu,
> That is only for the DTS Transfer Logins Tasks. You can still use the meth
od
> 1 of the section 'A complete resolution to transfer logins and passwords
> between different versions of SQL Server' and will get the additional bene
fit
> of transfering the original login SID.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
> "Manu" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
Hi Ben
That worked beautifully.
As mentioned on the web page, default databases assigned to the logins
are different.
Is there any way to assign the default databases apart from writing a
script using 'sp_defaultdb' for each login?
Regards
Migrate SQL Server 2000
Hello. We have a new database server machine that will replace our old
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
Jhttp://www.microsoft.com/downloads/...&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/...&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and i
p
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to b
e
> retired machine to our new machine? Perhaps setting up an empty SQL 2000
on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
> http://www.microsoft.com/downloads/...&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/...&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>|||You could either:
-. Do a back up the database on your old database and restore of your
database on your new server.
-. Do an dettach and attach of your database data and log file.
Personally, I usually choose the backup and restore approach, just as you
said. Also you might need to restore the system databases: master, and msdb
(at the very least), if you want to bring over the login information, and
also the job information. If you want you can also backup and restore the
model database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Lucas
For info on backup and restore system database:
"J" wrote:
> Actually both old and new server will be SQL 2000. So I guess I can look
at
> my first task as a full restore based on all of the backups?
> I think we will eventually upgrade to SQL 2005 so I'll definitely keep you
r
> helpful info in mind. Thanks for the info Maninder. I totally appreciate
> it.
> J
> "Maninder" <msdhanjal@.gmail.com> wrote in message
> news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
>
>|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...[vbcol=seagreen]
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
>
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
Jhttp://www.microsoft.com/downloads/...&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/...&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and i
p
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to b
e
> retired machine to our new machine? Perhaps setting up an empty SQL 2000
on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
> http://www.microsoft.com/downloads/...&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/...&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>|||You could either:
-. Do a back up the database on your old database and restore of your
database on your new server.
-. Do an dettach and attach of your database data and log file.
Personally, I usually choose the backup and restore approach, just as you
said. Also you might need to restore the system databases: master, and msdb
(at the very least), if you want to bring over the login information, and
also the job information. If you want you can also backup and restore the
model database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Lucas
For info on backup and restore system database:
"J" wrote:
> Actually both old and new server will be SQL 2000. So I guess I can look
at
> my first task as a full restore based on all of the backups?
> I think we will eventually upgrade to SQL 2005 so I'll definitely keep you
r
> helpful info in mind. Thanks for the info Maninder. I totally appreciate
> it.
> J
> "Maninder" <msdhanjal@.gmail.com> wrote in message
> news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
>
>|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...[vbcol=seagreen]
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
>
Migrate SQL Server 2000
Hello. We have a new database server machine that will replace our old
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
J
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and ip
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to be
> retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J
|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegro ups.com...
> http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>
|||You could either:
-. Do a back up the database on your old database and restore of your
database on your new server.
-. Do an dettach and attach of your database data and log file.
Personally, I usually choose the backup and restore approach, just as you
said. Also you might need to restore the system databases: master, and msdb
(at the very least), if you want to bring over the login information, and
also the job information. If you want you can also backup and restore the
model database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Lucas
For info on backup and restore system database:
"J" wrote:
> Actually both old and new server will be SQL 2000. So I guess I can look at
> my first task as a full restore based on all of the backups?
> I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
> helpful info in mind. Thanks for the info Maninder. I totally appreciate
> it.
> J
> "Maninder" <msdhanjal@.gmail.com> wrote in message
> news:1169493039.530765.30020@.s34g2000cwa.googlegro ups.com...
>
>
|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...[vbcol=seagreen]
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
J
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and ip
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to be
> retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J
|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegro ups.com...
> http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>
|||You could either:
-. Do a back up the database on your old database and restore of your
database on your new server.
-. Do an dettach and attach of your database data and log file.
Personally, I usually choose the backup and restore approach, just as you
said. Also you might need to restore the system databases: master, and msdb
(at the very least), if you want to bring over the login information, and
also the job information. If you want you can also backup and restore the
model database.
http://msdn2.microsoft.com/en-us/library/ms190190.aspx
Lucas
For info on backup and restore system database:
"J" wrote:
> Actually both old and new server will be SQL 2000. So I guess I can look at
> my first task as a full restore based on all of the backups?
> I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
> helpful info in mind. Thanks for the info Maninder. I totally appreciate
> it.
> J
> "Maninder" <msdhanjal@.gmail.com> wrote in message
> news:1169493039.530765.30020@.s34g2000cwa.googlegro ups.com...
>
>
|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...[vbcol=seagreen]
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
Migrate SQL Server 2000
Hello. We have a new database server machine that will replace our old
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
Jhttp://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and ip
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to be
> retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
> http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>> Hello. We have a new database server machine that will replace our old
>> database server machine running SQL 2000 (will take on the same name and
>> ip
>> address). I was wondering if anyone could recommend or point me to some
>> info on what is the best method in getting the data from our old soon to
>> be
>> retired machine to our new machine? Perhaps setting up an empty SQL 2000
>> on
>> the new machine and then taking the backup files from the old machine and
>> doing a restore on each database starting with the master db?...just
>> wondering if this will affect any of the system databases (master, temp,
>> msdb, model) like the sql/window logins/passwords and/or other backend
>> things that should or would need to remain in tact?
>> Thanks in advance.
>> J
>|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
>> Actually both old and new server will be SQL 2000. So I guess I can look
>> at
>> my first task as a full restore based on all of the backups?
>> I think we will eventually upgrade to SQL 2005 so I'll definitely keep
>> your
>> helpful info in mind. Thanks for the info Maninder. I totally
>> appreciate
>> it.
>> J
>> "Maninder" <msdhanjal@.gmail.com> wrote in message
>> news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
>> > http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
>> > This is What you can Read or goto
>> > http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
>> >
>> > This is Helpful.
>> >
>> > Rest in Short: Just Detach and attach the Databases to the new SQL2K5
>> > Server and change the Compatability Level to 90
>> >
>> > As for the DTS PAckages you might have on SQL2000, Well its a whole new
>> > Story. DTS Packages without several connections are easy to migrate,
>> > but Some require to be recreated from Scratch, if you have on of those
>> > smart Developers with you, He will figure out a process in time.
>> >
>> > Maninder
>> > MCDBA
>> >
>> >
>> > On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>> >> Hello. We have a new database server machine that will replace our
>> >> old
>> >> database server machine running SQL 2000 (will take on the same name
>> >> and
>> >> ip
>> >> address). I was wondering if anyone could recommend or point me to
>> >> some
>> >> info on what is the best method in getting the data from our old soon
>> >> to
>> >> be
>> >> retired machine to our new machine? Perhaps setting up an empty SQL
>> >> 2000
>> >> on
>> >> the new machine and then taking the backup files from the old machine
>> >> and
>> >> doing a restore on each database starting with the master db?...just
>> >> wondering if this will affect any of the system databases (master,
>> >> temp,
>> >> msdb, model) like the sql/window logins/passwords and/or other backend
>> >> things that should or would need to remain in tact?
>> >>
>> >> Thanks in advance.
>> >>
>> >> J
>> >
>>
database server machine running SQL 2000 (will take on the same name and ip
address). I was wondering if anyone could recommend or point me to some
info on what is the best method in getting the data from our old soon to be
retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
the new machine and then taking the backup files from the old machine and
doing a restore on each database starting with the master db?...just
wondering if this will affect any of the system databases (master, temp,
msdb, model) like the sql/window logins/passwords and/or other backend
things that should or would need to remain in tact?
Thanks in advance.
Jhttp://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
This is What you can Read or goto
http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
This is Helpful.
Rest in Short: Just Detach and attach the Databases to the new SQL2K5
Server and change the Compatability Level to 90
As for the DTS PAckages you might have on SQL2000, Well its a whole new
Story. DTS Packages without several connections are easy to migrate,
but Some require to be recreated from Scratch, if you have on of those
smart Developers with you, He will figure out a process in time.
Maninder
MCDBA
On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
> Hello. We have a new database server machine that will replace our old
> database server machine running SQL 2000 (will take on the same name and ip
> address). I was wondering if anyone could recommend or point me to some
> info on what is the best method in getting the data from our old soon to be
> retired machine to our new machine? Perhaps setting up an empty SQL 2000 on
> the new machine and then taking the backup files from the old machine and
> doing a restore on each database starting with the master db?...just
> wondering if this will affect any of the system databases (master, temp,
> msdb, model) like the sql/window logins/passwords and/or other backend
> things that should or would need to remain in tact?
> Thanks in advance.
> J|||Actually both old and new server will be SQL 2000. So I guess I can look at
my first task as a full restore based on all of the backups?
I think we will eventually upgrade to SQL 2005 so I'll definitely keep your
helpful info in mind. Thanks for the info Maninder. I totally appreciate
it.
J
"Maninder" <msdhanjal@.gmail.com> wrote in message
news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
> http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
> This is What you can Read or goto
> http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
> This is Helpful.
> Rest in Short: Just Detach and attach the Databases to the new SQL2K5
> Server and change the Compatability Level to 90
> As for the DTS PAckages you might have on SQL2000, Well its a whole new
> Story. DTS Packages without several connections are easy to migrate,
> but Some require to be recreated from Scratch, if you have on of those
> smart Developers with you, He will figure out a process in time.
> Maninder
> MCDBA
>
> On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>> Hello. We have a new database server machine that will replace our old
>> database server machine running SQL 2000 (will take on the same name and
>> ip
>> address). I was wondering if anyone could recommend or point me to some
>> info on what is the best method in getting the data from our old soon to
>> be
>> retired machine to our new machine? Perhaps setting up an empty SQL 2000
>> on
>> the new machine and then taking the backup files from the old machine and
>> doing a restore on each database starting with the master db?...just
>> wondering if this will affect any of the system databases (master, temp,
>> msdb, model) like the sql/window logins/passwords and/or other backend
>> things that should or would need to remain in tact?
>> Thanks in advance.
>> J
>|||Thanks for the insightful info Lucas. Mucha appreciated.
Take care,
J
"Lucas Kartawidjaja" <LucasKartawidjaja@.discussions.microsoft.com> wrote in
message news:A92C9C5E-10EE-4182-8ED3-2B44450BC793@.microsoft.com...
> You could either:
> -. Do a back up the database on your old database and restore of your
> database on your new server.
> -. Do an dettach and attach of your database data and log file.
> Personally, I usually choose the backup and restore approach, just as you
> said. Also you might need to restore the system databases: master, and
> msdb
> (at the very least), if you want to bring over the login information, and
> also the job information. If you want you can also backup and restore the
> model database.
> http://msdn2.microsoft.com/en-us/library/ms190190.aspx
> Lucas
> For info on backup and restore system database:
>
> "J" wrote:
>> Actually both old and new server will be SQL 2000. So I guess I can look
>> at
>> my first task as a full restore based on all of the backups?
>> I think we will eventually upgrade to SQL 2005 so I'll definitely keep
>> your
>> helpful info in mind. Thanks for the info Maninder. I totally
>> appreciate
>> it.
>> J
>> "Maninder" <msdhanjal@.gmail.com> wrote in message
>> news:1169493039.530765.30020@.s34g2000cwa.googlegroups.com...
>> > http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
>> > This is What you can Read or goto
>> > http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en
>> >
>> > This is Helpful.
>> >
>> > Rest in Short: Just Detach and attach the Databases to the new SQL2K5
>> > Server and change the Compatability Level to 90
>> >
>> > As for the DTS PAckages you might have on SQL2000, Well its a whole new
>> > Story. DTS Packages without several connections are easy to migrate,
>> > but Some require to be recreated from Scratch, if you have on of those
>> > smart Developers with you, He will figure out a process in time.
>> >
>> > Maninder
>> > MCDBA
>> >
>> >
>> > On Jan 22, 1:54 pm, "J" <IDontLikeS...@.Nowhere.com> wrote:
>> >> Hello. We have a new database server machine that will replace our
>> >> old
>> >> database server machine running SQL 2000 (will take on the same name
>> >> and
>> >> ip
>> >> address). I was wondering if anyone could recommend or point me to
>> >> some
>> >> info on what is the best method in getting the data from our old soon
>> >> to
>> >> be
>> >> retired machine to our new machine? Perhaps setting up an empty SQL
>> >> 2000
>> >> on
>> >> the new machine and then taking the backup files from the old machine
>> >> and
>> >> doing a restore on each database starting with the master db?...just
>> >> wondering if this will affect any of the system databases (master,
>> >> temp,
>> >> msdb, model) like the sql/window logins/passwords and/or other backend
>> >> things that should or would need to remain in tact?
>> >>
>> >> Thanks in advance.
>> >>
>> >> J
>> >
>>
Migrate SQL 7 on W2K server to SQL 2000 on W2003 server
We are currently running SQL7 with OLAP on a windows 2000 server. I would
like to migrate everything over to a new Windows 2003 server running SQL2000.
Are their any whitepapers on such a move? Especially on migrating over the
cubes from SQL7 to SQL 2000 on a different server.
migrate to SQL 2005 w/ SP2 and cumulateive update 2187.
"nf" <nf@.discussions.microsoft.com> wrote in message
news:5BCEA93F-9BA6-461E-B9A7-F6D061C4FD7B@.microsoft.com...
> We are currently running SQL7 with OLAP on a windows 2000 server. I would
> like to migrate everything over to a new Windows 2003 server running
> SQL2000.
> Are their any whitepapers on such a move? Especially on migrating over the
> cubes from SQL7 to SQL 2000 on a different server.
like to migrate everything over to a new Windows 2003 server running SQL2000.
Are their any whitepapers on such a move? Especially on migrating over the
cubes from SQL7 to SQL 2000 on a different server.
migrate to SQL 2005 w/ SP2 and cumulateive update 2187.
"nf" <nf@.discussions.microsoft.com> wrote in message
news:5BCEA93F-9BA6-461E-B9A7-F6D061C4FD7B@.microsoft.com...
> We are currently running SQL7 with OLAP on a windows 2000 server. I would
> like to migrate everything over to a new Windows 2003 server running
> SQL2000.
> Are their any whitepapers on such a move? Especially on migrating over the
> cubes from SQL7 to SQL 2000 on a different server.
Migrate SQL 7 on W2K server to SQL 2000 on W2003 server
We are currently running SQL7 with OLAP on a windows 2000 server. I would
like to migrate everything over to a new Windows 2003 server running SQL2000.
Are their any whitepapers on such a move? Especially on migrating over the
cubes from SQL7 to SQL 2000 on a different server.migrate to SQL 2005 w/ SP2 and cumulateive update 2187.
"nf" <nf@.discussions.microsoft.com> wrote in message
news:5BCEA93F-9BA6-461E-B9A7-F6D061C4FD7B@.microsoft.com...
> We are currently running SQL7 with OLAP on a windows 2000 server. I would
> like to migrate everything over to a new Windows 2003 server running
> SQL2000.
> Are their any whitepapers on such a move? Especially on migrating over the
> cubes from SQL7 to SQL 2000 on a different server.sql
like to migrate everything over to a new Windows 2003 server running SQL2000.
Are their any whitepapers on such a move? Especially on migrating over the
cubes from SQL7 to SQL 2000 on a different server.migrate to SQL 2005 w/ SP2 and cumulateive update 2187.
"nf" <nf@.discussions.microsoft.com> wrote in message
news:5BCEA93F-9BA6-461E-B9A7-F6D061C4FD7B@.microsoft.com...
> We are currently running SQL7 with OLAP on a windows 2000 server. I would
> like to migrate everything over to a new Windows 2003 server running
> SQL2000.
> Are their any whitepapers on such a move? Especially on migrating over the
> cubes from SQL7 to SQL 2000 on a different server.sql
Wednesday, March 21, 2012
Migrate from user instance to native db in SQL Server Express ?
Hello,
I am currently looking at migrating a database which has been running in
"user instance" mode, onto the standard sql server express mode. Basically I
need to be able to connect to the database while it's running from other
scripts and the management studio, which I cannot do nicely while the DB is
running in user mode.
Is there any tools or good instructions on doing this?
At this point I've re-created the database from scratch and imported data,
however I am looking for a cleaner/effective way of doing this. Any
suggestions?Hi
I am not a SQLExpress expert, ut SSEUTIL will allow you to attach to a user
instance of SQL Express.
http://www.microsoft.com/downloads/...&DisplayLang=en
You could then detach (sp_detach_db) the database and re-attach
(sp_attach_db) it to the main instance of SQL Server.
You would need to change the connection string in the application that
creates the user instance to reference the main instance of SQL Server and
not create the user instance.
John
"NSiggel" wrote:
> Hello,
> I am currently looking at migrating a database which has been running in
> "user instance" mode, onto the standard sql server express mode. Basically
I
> need to be able to connect to the database while it's running from other
> scripts and the management studio, which I cannot do nicely while the DB i
s
> running in user mode.
> Is there any tools or good instructions on doing this?
> At this point I've re-created the database from scratch and imported data,
> however I am looking for a cleaner/effective way of doing this. Any
> suggestions?
>sql
I am currently looking at migrating a database which has been running in
"user instance" mode, onto the standard sql server express mode. Basically I
need to be able to connect to the database while it's running from other
scripts and the management studio, which I cannot do nicely while the DB is
running in user mode.
Is there any tools or good instructions on doing this?
At this point I've re-created the database from scratch and imported data,
however I am looking for a cleaner/effective way of doing this. Any
suggestions?Hi
I am not a SQLExpress expert, ut SSEUTIL will allow you to attach to a user
instance of SQL Express.
http://www.microsoft.com/downloads/...&DisplayLang=en
You could then detach (sp_detach_db) the database and re-attach
(sp_attach_db) it to the main instance of SQL Server.
You would need to change the connection string in the application that
creates the user instance to reference the main instance of SQL Server and
not create the user instance.
John
"NSiggel" wrote:
> Hello,
> I am currently looking at migrating a database which has been running in
> "user instance" mode, onto the standard sql server express mode. Basically
I
> need to be able to connect to the database while it's running from other
> scripts and the management studio, which I cannot do nicely while the DB i
s
> running in user mode.
> Is there any tools or good instructions on doing this?
> At this point I've re-created the database from scratch and imported data,
> however I am looking for a cleaner/effective way of doing this. Any
> suggestions?
>sql
Subscribe to:
Posts (Atom)