Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
--
PLTry backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:
> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
--
PL|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:
> Sean McCown wrote:
> > Try backing up and restoring MSDB and changing the appropriate fields.
> > We do this with jobs every week to keep 2 servers in sync and this is what
> > we use to bring the jobs up to date. I would imagine that it could be just
> > as easy for SSIS packages, though I haven't tried it officially.
> >
> > UPDATE sysjobs
> > SET originating_server = 'MyServer'
> >
> > So just run that on the new server once you have MSDB restored on there and
> > you should be OK. And just check the SSIS tables to discover similar entries
> > that may need to be changed.
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>
Thursday, March 29, 2012
copy many ssis packages between servers
Hi.
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
PL
Try backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:
> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>
|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
PL
|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:
> Sean McCown wrote:
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>
sqlsql
Is there any simple way of copying all of the ssis packages between two
servers?
I have just installed sql 2005 on a new, better equipped machine and I have
to move there all the data. Including ssis of course. There are about 40-50
packages to do (stored in the database, not in filesystem) and I hope there
is a better way than just to import packages (one by one) to a text file
and export them to another server.
BTW, I'm going to copy the job definitions as well, there are about 20 jobs
defined on the server, is there any handy way of doing this? Any hints?
PL
Try backing up and restoring MSDB and changing the appropriate fields.
We do this with jobs every week to keep 2 servers in sync and this is what
we use to bring the jobs up to date. I would imagine that it could be just
as easy for SSIS packages, though I haven't tried it officially.
UPDATE sysjobs
SET originating_server = 'MyServer'
So just run that on the new server once you have MSDB restored on there and
you should be OK. And just check the SSIS tables to discover similar entries
that may need to be changed.
"Piotr Lipski" wrote:
> Hi.
> Is there any simple way of copying all of the ssis packages between two
> servers?
> I have just installed sql 2005 on a new, better equipped machine and I have
> to move there all the data. Including ssis of course. There are about 40-50
> packages to do (stored in the database, not in filesystem) and I hope there
> is a better way than just to import packages (one by one) to a text file
> and export them to another server.
> BTW, I'm going to copy the job definitions as well, there are about 20 jobs
> defined on the server, is there any handy way of doing this? Any hints?
> --
> PL
>
|||Sean McCown wrote:
> Try backing up and restoring MSDB and changing the appropriate fields.
> We do this with jobs every week to keep 2 servers in sync and this is what
> we use to bring the jobs up to date. I would imagine that it could be just
> as easy for SSIS packages, though I haven't tried it officially.
> UPDATE sysjobs
> SET originating_server = 'MyServer'
> So just run that on the new server once you have MSDB restored on there and
> you should be OK. And just check the SSIS tables to discover similar entries
> that may need to be changed.
Right. But how to restore the msdb database? When I try to do it from
SMS (right click on database -> tasks -> restore) I get failure message
"Exclusive access could not be obtained because the database is in
use.", which makes sense but does not resolve the problem. There is also
no option to go off-line or detach for that database.
Should I just replace the msdb datafiles (mdf/ldf) with the ones from
'old' server?
PL
|||OK, there are a couple things here...
first, to restore MSDB just startup SQL with -T3608 and -m. 3608 prevents
startup of all system DBs except master. -m is single-user mode. So you
should now be able to restore MSDB with no problem. If you're doing it from
the command line, then use -c.
Second, You can always just setup a linked server and copy the raw data over
without restoring.
Good luck.
"Piotr Lipski" wrote:
> Sean McCown wrote:
> Right. But how to restore the msdb database? When I try to do it from
> SMS (right click on database -> tasks -> restore) I get failure message
> "Exclusive access could not be obtained because the database is in
> use.", which makes sense but does not resolve the problem. There is also
> no option to go off-line or detach for that database.
> Should I just replace the msdb datafiles (mdf/ldf) with the ones from
> 'old' server?
> --
> PL
>
sqlsql
Copy maintenance plan to two different servers
SQL Server 2005:
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
Mark
Satya SKJ has written about this in another forum.
"Create a SSIS package to perform this maintenance plan task and use DTUTIL
to deploy on multiple servers."
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:
> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
Mark
Satya SKJ has written about this in another forum.
"Create a SSIS package to perform this maintenance plan task and use DTUTIL
to deploy on multiple servers."
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:
> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>
Copy maintenance plan to two different servers
SQL Server 2005:
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
MarkSatya SKJ has written about this in another forum.
"Create a SSIS package to perform this maintenance plan task and use DTUTIL
to deploy on multiple servers."
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:
> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
MarkSatya SKJ has written about this in another forum.
"Create a SSIS package to perform this maintenance plan task and use DTUTIL
to deploy on multiple servers."
http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:
> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>
Copy Logins from Server1 to Server2
Hello,
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
sqlServer?
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
sqlServer?
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
quote:
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
quote:|||There is a really good doc under Books On line, search for Log shipping...
> Thanks
> Jose.
>
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
quote:
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
quote:
> Thanks
> Jose.
>
Copy Logins from Server1 to Server2
Hello,
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>|||There is a really good doc under Books On line, search for Log shipping...
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>
I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
there a way that I can export the logins acount info to the other sqlServer?
Thanks
Jose.DTS let you copy logins.
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:%23aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>|||There is a really good doc under Books On line, search for Log shipping...
Moving logins from one server to another is part of the failover process for
log shipping so the process is really documented well...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jose Ines Cantu Arrambide" <nospam@.nospam> wrote in message
news:#aETDE52DHA.3416@.tk2msftngp13.phx.gbl...
> Hello,
> I have 2 SqlServers, one has a lot of Login/Users/Permissions data. Is
> there a way that I can export the logins acount info to the other
sqlServer?
> Thanks
> Jose.
>
Copy local sql 05 DB to production machine
Hi SQL folks,
I've googled for this the whole yesterday and I couldn't find a complete solution.
I'm having a sql2005 database in my development machine and I need to copy all the content "Tables, Diagrams, PK columns and other data" to the remote production machine.
Using the Import/Export method didn't copied the relationships between tables, also it turned the PKs into just a standard columns
Can anyone help?
The only missing thing now is the relationships, How can I copy it?
I've copied the DB diagram but the relationships simply disapeared!
sqlsql
Subscribe to:
Posts (Atom)