Is there anyway to copy all the DTS packages and jobs in one step from one server to another. I usvally copy one DTS package at a time. As our packages count grown bigger, it became a painful process.
If anyone knows a way to copy these packages and jobs in one then it would be very helpful.
ThanksI don't think you can for DTS: Steps I have used in the past:
Save DTS package to hard drive:
Save DTS package as file on the disk, then copy this file on the target SQL Server and load this DTS package on the target machine. Dont forget to save in SQL Server.
Or...
Run DTS Export Wizard, and transfer this select statement:
SELECT * FROM sysdtspackages WHERE name = 'DTSPackageName'
from the msdb database on the source server to the msdb database on the destination server to transfer the DTSPackageName package.
You may be able to add multi packageName, I have never play around with the code. (Maybe someone else will have an idea)
And...
As for jobs you can create a DTS package and under Task select 15 - Transfer job task and follow the steps. You can do all of your jobs at once.
Hope it works out for you.
Thanks
Lystra|||As for the DTS packages go to http://www.sqldts.com/default.aspx?242 This is a free tool to migrate the DTS packages in one step.
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Wednesday, March 28, 2012
migrating db
I have an ms sql 2000 db which I am migrating from one shared environment to another. How specifcally can I do this?
I have a copy of enterprise but not a full version of SQL on my local desktop.
The hosting company from which I am moving has placed the db as a zip file on the root of my site via ftp. I have unzipped it and it appears as a backup copy (.bak).
I appreciate any detail steps, I assume via enterprise, that anyone can give me. Thank you.Create a database on your server, and then restore the backup to it.
For detailed steps, see Books Online.
I have a copy of enterprise but not a full version of SQL on my local desktop.
The hosting company from which I am moving has placed the db as a zip file on the root of my site via ftp. I have unzipped it and it appears as a backup copy (.bak).
I appreciate any detail steps, I assume via enterprise, that anyone can give me. Thank you.Create a database on your server, and then restore the backup to it.
For detailed steps, see Books Online.
Wednesday, March 21, 2012
Migrate MSDE data to SQL 2000
I have local copy of MSDE 2000 on an XP workstation.
My program has already been reconfigured to point to a remote (from the XP
viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
uses and is already populating those databases with data.
The question is, how do I get the "old" data from MSDE on the XP box into
the "new" databases on the remote SQL server?
When I try to use DTS and choosing the default of OLE Provider for SQL for
the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
I am using the correct "sa" password for the MSDE database.
Robert
You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
I prefer to backup on the old server, copy the backup file to the new
server, and restore it on the new server. This is fairly easy to do. Just
make sure you create the users from the old server on the new server first,
and then run sp_change_users_login to eliminate orphaned users after you do
the restore.
"Robert Gandrud" wrote:
> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from the XP
> viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
> uses and is already populating those databases with data.
> The question is, how do I get the "old" data from MSDE on the XP box into
> the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for SQL for
> the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
> I am using the correct "sa" password for the MSDE database.
> Robert
|||As far as I know, the only user is the "sa" user in the old MSDE database.
What then?
"Dan" wrote:
[vbcol=seagreen]
> You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
> I prefer to backup on the old server, copy the backup file to the new
> server, and restore it on the new server. This is fairly easy to do. Just
> make sure you create the users from the old server on the new server first,
> and then run sp_change_users_login to eliminate orphaned users after you do
> the restore.
> "Robert Gandrud" wrote:
|||hi Robert,
Robert Gandrud wrote:
> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from
> the XP viewpoint) SQL 2000 server. As such, it has created the 2
> databases that it uses and is already populating those databases with
> data.
> The question is, how do I get the "old" data from MSDE on the XP box
> into the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for
> SQL for the source, I can't connect to the XP MSDE, which is using
> SQL Auth. I know I am using the correct "sa" password for the MSDE
> database.
>
MSDE installs by default disabling network protocols...in order to them
after installation you have to run the
Server Network Utility (svrnetcn.exe) and enabling the desired
protocol(s)...
Win XP Windows Firewall could be an issue too and please have a look at
http://support.microsoft.com/kb/841251/en-us in order to enable remote
connectivity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
My program has already been reconfigured to point to a remote (from the XP
viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
uses and is already populating those databases with data.
The question is, how do I get the "old" data from MSDE on the XP box into
the "new" databases on the remote SQL server?
When I try to use DTS and choosing the default of OLE Provider for SQL for
the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
I am using the correct "sa" password for the MSDE database.
Robert
You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
I prefer to backup on the old server, copy the backup file to the new
server, and restore it on the new server. This is fairly easy to do. Just
make sure you create the users from the old server on the new server first,
and then run sp_change_users_login to eliminate orphaned users after you do
the restore.
"Robert Gandrud" wrote:
> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from the XP
> viewpoint) SQL 2000 server. As such, it has created the 2 databases that it
> uses and is already populating those databases with data.
> The question is, how do I get the "old" data from MSDE on the XP box into
> the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for SQL for
> the source, I can't connect to the XP MSDE, which is using SQL Auth. I know
> I am using the correct "sa" password for the MSDE database.
> Robert
|||As far as I know, the only user is the "sa" user in the old MSDE database.
What then?
"Dan" wrote:
[vbcol=seagreen]
> You have a few options: DTS, Detatch/Reattach, or Backup/Restore.
> I prefer to backup on the old server, copy the backup file to the new
> server, and restore it on the new server. This is fairly easy to do. Just
> make sure you create the users from the old server on the new server first,
> and then run sp_change_users_login to eliminate orphaned users after you do
> the restore.
> "Robert Gandrud" wrote:
|||hi Robert,
Robert Gandrud wrote:
> I have local copy of MSDE 2000 on an XP workstation.
> My program has already been reconfigured to point to a remote (from
> the XP viewpoint) SQL 2000 server. As such, it has created the 2
> databases that it uses and is already populating those databases with
> data.
> The question is, how do I get the "old" data from MSDE on the XP box
> into the "new" databases on the remote SQL server?
> When I try to use DTS and choosing the default of OLE Provider for
> SQL for the source, I can't connect to the XP MSDE, which is using
> SQL Auth. I know I am using the correct "sa" password for the MSDE
> database.
>
MSDE installs by default disabling network protocols...in order to them
after installation you have to run the
Server Network Utility (svrnetcn.exe) and enabling the desired
protocol(s)...
Win XP Windows Firewall could be an issue too and please have a look at
http://support.microsoft.com/kb/841251/en-us in order to enable remote
connectivity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Monday, February 20, 2012
Microsoft SQL Server Mobile Edition data source
i just installed a copy of microsoft sql server 2005 in my pc and decided to follow the newbie's guide:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true
anyway, on the 5th exercise wherein i'm supposed to add a new data connection through the server explorer.. when i chose the option of adding a new data source and was prompted to select a data source i couldnt find the Microsoft SQL Server Mobile Edition data source..
is there anything else that i need to download in order to have that data source?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true
anyway, on the 5th exercise wherein i'm supposed to add a new data connection through the server explorer.. when i chose the option of adding a new data source and was prompted to select a data source i couldnt find the Microsoft SQL Server Mobile Edition data source..
is there anything else that i need to download in order to have that data source?
1) Did your installation include "SQL Server 2005 Mobile Edition". Can you see SQL Mobile traces in "Add/Remove Programs" directory. Some thing like "SQL Server Mobile Developer Tools".
2) If yes, then can you please follow the thread@. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=260599&SiteID=1, and see if it helps you out.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||uh yeah. but mine says "Microsoft SQL Server Mobile 2005 Mobile [ENU] Server Tools" i got the sql server 2005 mobile edition by accessing the sqlce30setupen.msi file. also i already tried to add the following lines to the machine.config file but i still couldnt find it when i open vs studio 2005. anyway, if worst comes to worst and i cant figure this one out would you happen to know how i would configure the database connection for a pocket pc through the use of vs.net 2003?<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />|||
Hmm...it looks like SQL Mobile Developer tools were not installed. Server Tools are not needed for you, they are meant for synchronization of SQL Mobile database with SQL Server. If you dont have Developer Tools, try installing the SQL Mobile Developer tools from the following location of your VS 2005 Disk: wcu\SQLCE\sqlmobile30devtoolsxxx.msi
The xxx in the MSI name stand for the locale, for english it would be 'enu'.
Let me know installing that worked for you.
Thanks
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Dear Laxmi....I also have the same problem and I tried to install the SQL Mobile Developer tools from VS 2005, but the location is not avalaible in the CD. I used VS PRo 2005.
Please help me because I have a project to write program for My Mobile Application that's need to connect to database.
Microsoft SQL Server Mobile Edition data source
i just installed a copy of microsoft sql server 2005 in my pc and decided to follow the newbie's guide:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true
anyway, on the 5th exercise wherein i'm supposed to add a new data
connection through the server explorer.. when i chose the option of
adding a new data source and was prompted to select a data source i
couldnt find the Microsoft SQL Server Mobile Edition data source..
is there anything else that i need to download in order to have that data source?
1) Did your installation include "SQL Server 2005 Mobile Edition". Can you see SQL Mobile traces in "Add/Remove Programs" directory. Some thing like "SQL Server Mobile Developer Tools".
2) If yes, then can you please follow the thread@. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=260599&SiteID=1, and see if it helps you out.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||uh yeah. but mine says "Microsoft SQL Server Mobile 2005 Mobile [ENU]Server Tools" i got the sql server 2005 mobile edition by accessing the
sqlce30setupen.msi file. also i already tried to add the
following lines to the machine.config file but i still couldnt find it
when i open vs studio 2005. anyway, if worst comes to worst and i
cant figure this one out would you happen to know how i would configure
the database connection for a pocket pc through the use of vs.net 2003?
<add
name="SQL Server CE Data Provider"
invariant="Microsoft.SqlServerCe.Client" description=".NET Framework
Data Provider for Microsoft SQL Server 2005 Mobile Edition"
type="Microsoft.SqlServerCe.Client.SqlCeClientFactory,
Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91" />|||
Hmm...it looks like SQL Mobile Developer tools were not installed. Server Tools are not needed for you, they are meant for synchronization of SQL Mobile database with SQL Server. If you dont have Developer Tools, try installing the SQL Mobile Developer tools from the following location of your VS 2005 Disk: wcu\SQLCE\sqlmobile30devtoolsxxx.msi
The xxx in the MSI name stand for the locale, for english it would be 'enu'.
Let me know installing that worked for you.
Thanks
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
|||Dear Laxmi....I also have the same problem and I tried to install the SQL Mobile Developer tools from VS 2005, but the location is not avalaible in the CD. I used VS PRo 2005.
Please help me because I have a project to write program for My Mobile Application that's need to connect to database.
Subscribe to:
Posts (Atom)