copy objects and data from server to server

I want to copy all objects and data from other SQL 2000 server std. to my
local server(SQL 2000 personal) to test something.
when I do copy wizard I got a message
"SQL Server service is running under localsystem account. To get the
privilege to copy through the network you should change SQL Service
account."(FYI,this may not what you see in Eng version because I translated
it.)
Why?
This time I used "copy wizard", is it the best solution?If you load EM, & select the server in question. Right click & select
properties. Goto the Security tab. At the bottom, you have the option to
start & run SQL server in either the local system account, or another
account...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||HI,
To perform this operation start the SQL server service using a Domain OS
user, which has got access in both servers.
Thanks
Hari
MCDBA
"James Goodman" <j a m e s@.norton-associates.co.u k> wrote in message
news:bvd77v$o53$1@.sparta.btinternet.com...
> If you load EM, & select the server in question. Right click & select
> properties. Goto the Security tab. At the bottom, you have the option to
> start & run SQL server in either the local system account, or another
> account...
>
>
> --
> Cheers,
> James Goodman MCSE, MCDBA
> http://www.angelfire.com/sports/f1pictures
>

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

copy DTS from a user to another

Hi,
I have some local packages that are owned by the old sqlserver dba.I'm not
able to see them, nor to save them with my user (i have granted the system
administrator role).
I want to see these packages and move them to a new user.
How can I do this?
Thanks in advance,If you are trying to change the owner of these packages, then you can use
the undocumented procedure, sp_reassign_dtspackageowner.
For details refer to: http://www.sqldts.com/?212
http://support.microsoft.com/?kbid=247052
Anith|||Hi,
I saved the DTS package as my user but I'm still not able to edit them.
Is it possible that there's a password?
Thanks,
Tarek
"Anith Sen" wrote:

> If you are trying to change the owner of these packages, then you can use
> the undocumented procedure, sp_reassign_dtspackageowner.
> For details refer to: http://www.sqldts.com/?212
> http://support.microsoft.com/?kbid=247052
> --
> Anith
>
>sqlsql

Copy Database Wizard Error- SQL Server Agent cannot execute the SSIS package

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Where did you run your package?|||The Copy Database Wizard ran the SSIS package automatically, at the end of the wizard.|||The Copy Database Wizard ran the SSIS package automatically.|||Oops. I thought you were using transfer database task in SSIS. I think you are at the wrong forum. I assume you initiated copy database wizard from sql management studio.Try posting your issue at the right forum:
SQL Management studio: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597172&SiteID=1
SMO: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

Also, before doing that you may want to check if both the source and destination servers support remote connections.|||Thanks for pointing me to the fourm. Yes I am using Management Studio for the Copy Database Wizard. Could you please tell me how I can check to see if the source and the destination servers allow remote connections? Is that a Windows Server2003 setting or a SQL Server2005 setting?
Thanks!|||1. Open "SQL Server 2005 Surface Area Configuration" (in Programs-> Microsoft SQL Server 2005 -> Configuration Tools)
2. Click on "Surface Area Configuraiton for Services and Connections"
3. Select "Remote Connections" option for the instance you are interested in.
4. Select "Local and Remove connections"

In SQL Server Management studio, right click on the instance, select properties, go to connections page and select "Allow remote connections tot his server".

I do not know what the default settings are. So, check both.|||When I click on Remote Connections, I get a message saying"You cannot configure surface area of clustered services by connecting to a computer name. Connect to the virtual server to configure clustered services"

Do you know how/where I can do that?
Thanks again!

Copy Database Wizard Error

I am trying to

copy a database from our company's external SQL

Server(production) to our local SQL

Server(development). The Copy Database wizard fails on the step

"Execute SQL Server Agent Job".
Following is the error in the log file.. Please advise

InnerException-->An

error has occurred while establishing a connection to the server. When

connecting to SQL Server 2005, this failure may be caused by the fact

that under the default settings SQL Server does not allow remote

connections. (provider: Named Pipes Provider, error: 40 - Could not

open a connection to SQL Server)

Have you verified that both servers have the remote connections allowed. Have a look at this KB Article. Remember that Remote connections is turned off by default on new installes of sql server 2005.

|||Do you know how I can set SQL Server2005 to allow remote connections on a cluster?

Copy Database Wizard

Hello, I am trying to copy my database tables and stored procedures to my local machine. I keep gettin gthis error
CDW Error Message

X "Your SQL Server Service is running under the local system account. You need to change you SQL Server Sevice account to have the rights to copy files over the network."

What do I do to correct this?

Thanks,
EricAre you working in a controlled environment ??
Active Directory and so ??
If that is so, you need to be system admin in the network to be able to copy those files ...|||The local System account only has permissions to run proggys on the local machine, to do any networkable actions the SQL Server needs to be run under an account that is able to have permissions added to it.

What You should do when createing or installing a SQL Server is to create another account for it to run under. If you are working in a domain You would create a domain account that is a member of the domain users group, and has the add permissions to log on as a service. Then increase the permissions as needed. Now if you want to use SQL Mail you need to do the above so that you can add a mail profile to the server so that the user can send emails (SQLServer mail uses the MAPI system same as Outlook).

If you are in a workgroup, create a local user and add the log on as service permissions. then on the other machines that it needs to talk to you would add the user.|||Is it at all possible to get instructions on how this can happen. Even if I have to uninstall Sql server and re-install it. I need to copy the Database to my local machine.|||Are you just trying to copy it once, or do you want this to be a regular job.

Copy Database to another server

Hello there
I have database on my local server
I have also database on internet server
at 12:00pm i would like to update the internet server with all the changes
that made on the local server
how can i do this?
any help would be useful
If every table on your database has a primary key you should be using
transactional replication for this.
"Oded Kovach" <roygoldh@.hotmail.com> wrote in message
news:u8joTU$kEHA.1936@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I have database on my local server
> I have also database on internet server
> at 12:00pm i would like to update the internet server with all the changes
> that made on the local server
> how can i do this?
> any help would be useful
>
|||Thankes kilary
I have many databases on the server
and some of the tables in some databases realy haven't primary key
These databases shouldn't be raplicated at all
How can i remove them from the list of the replication?
hope that you can help me
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eDrqXlAlEHA.2500@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> If every table on your database has a primary key you should be using
> transactional replication for this.
>
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:u8joTU$kEHA.1936@.TK2MSFTNGP12.phx.gbl...
changes
>
|||Thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eDrqXlAlEHA.2500@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> If every table on your database has a primary key you should be using
> transactional replication for this.
>
> "Oded Kovach" <roygoldh@.hotmail.com> wrote in message
> news:u8joTU$kEHA.1936@.TK2MSFTNGP12.phx.gbl...
changes
>

copy database from web host to local?

Hi,
I also posted this question in sqlserver.DTS because I'm not sure where it
should go.
I'm trying to restore my local db from the one in my web host. I asked the
web host for help, and the only help they can offer is that: "Use SQL Server
Integrated Services (SSIS), which is integrated with the commercial version
of SQL Server Management Studio Express." I don't know what that means.
I also tried the DTSWizard.exe, but it only copies the data. It leaves out
my relationships, the keys, the SPs, etc...
What's the best way to copy a database from one sever to the other so that
everything is included?
Both are sql server 2005, and I want to copy from my web host to my local
server.
Thanks.
VMI (VMI@.discussions.microsoft.com) writes:
> I also posted this question in sqlserver.DTS because I'm not sure where it
> should go.
> I'm trying to restore my local db from the one in my web host. I asked
> the web host for help, and the only help they can offer is that: "Use
> SQL Server Integrated Services (SSIS), which is integrated with the
> commercial version of SQL Server Management Studio Express." I don't
> know what that means.
Well, Integration Services is not included in Express as far as I know.
Probably what they have in mind is the Copy Database Wizard, but you
would need to cough up around 50 USD for Developer Edition to get it.
Problem, though is that you would have to use the SMO method, which is
not very reliable.

> I also tried the DTSWizard.exe, but it only copies the data. It leaves out
> my relationships, the keys, the SPs, etc...
You can use the scripting capabilities in SSMS Express to script the
database, and then copy the data separately.
What you really should have done is do have all your database definition
under version control.

> What's the best way to copy a database from one sever to the other so that
> everything is included?
BACKUP/RESTORE, but that option is not likely to be available in
your situation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Copy Database from SQL 2000 to SQL 2005

Howdy,
I'm trying to use the Copy Database wizard in SQL Server Management Studio
to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
After running the wizard it errors out with the following. I can connect to
the 2000 server fine using the management studio as well. Any ideas?
StackTrace: at
Microsoft.SqlServer.Management.Common.ConnectionMa nager.Connect()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.OpenConnection(Server&
server, ServerProperty serverProp)
InnerException-->An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)
StackTrace: at
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception,
Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.Att emptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Log inNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at
Microsoft.SqlServer.Management.Common.ConnectionMa nager.InternalConnect(WindowsIdentity
impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionMa nager.Connect()
On Mar 6, 12:21 pm, "David Lozzi" <dlo...@.nospam.nospam> wrote:
> Howdy,
> I'm trying to use the Copy Database wizard in SQL Server Management Studio
> to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
> After running the wizard it errors out with the following. I can connect to
> the 2000 server fine using the management studio as well. Any ideas?
> StackTrace: at
> Microsoft.SqlServer.Management.Common.ConnectionMa nager.Connect()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask. TransferObjectsTask.OpenConnection(Server&
> server, ServerProperty serverProp)
> InnerException-->An error has occurred while establishing a connection to
> the server. When connecting to SQL Server 2005, this failure may be caused
> by the fact that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)
> StackTrace: at
> System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
> SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
> integratedSecurity, SqlConnection owningObject)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.Att emptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject)
> at System.Data.SqlClient.SqlInternalConnectionTds.Log inNoFailover(String
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
> identity, SqlConnectionString connectionOptions, Object providerInfo, String
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
> owningConnection, DbConnectionPool pool, DbConnectionOptions options)
> at System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
> owningObject)
> at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
> at System.Data.SqlClient.SqlConnection.Open()
> at
> Microsoft.SqlServer.Management.Common.ConnectionMa nager.InternalConnect(WindowsIdentity
> impersonatedIdentity)
> at Microsoft.SqlServer.Management.Common.ConnectionMa nager.Connect()
Complaints about the "Copy Database" wizard are common in this group,
and the response is always the same - don't use it. It's much faster
and easier to do a simple backup/restore of the database. The copy
wizard is flaky and throws up a variety of bizarre issues, depending
on what day of the week it is...
|||Not for nothing but its not easier to backup/restore when i'm sucking it
down from a web server... can i get this to work?
Thanks,
David Lozzi
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173206717.382194.282980@.t69g2000cwt.googlegr oups.com...
> On Mar 6, 12:21 pm, "David Lozzi" <dlo...@.nospam.nospam> wrote:
> Complaints about the "Copy Database" wizard are common in this group,
> and the response is always the same - don't use it. It's much faster
> and easier to do a simple backup/restore of the database. The copy
> wizard is flaky and throws up a variety of bizarre issues, depending
> on what day of the week it is...
>

Copy Database from SQL 2000 to SQL 2005

Howdy,
I'm trying to use the Copy Database wizard in SQL Server Management Studio
to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
After running the wizard it errors out with the following. I can connect to
the 2000 server fine using the management studio as well. Any ideas'
StackTrace: at
Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server&
server, ServerProperty serverProp)
InnerException-->An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)
StackTrace: at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at
Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity
impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()On Mar 6, 12:21 pm, "David Lozzi" <dlo...@.nospam.nospam> wrote:
> Howdy,
> I'm trying to use the Copy Database wizard in SQL Server Management Studio
> to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
> After running the wizard it errors out with the following. I can connect to
> the 2000 server fine using the management studio as well. Any ideas'
> StackTrace: at
> Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server&
> server, ServerProperty serverProp)
> InnerException-->An error has occurred while establishing a connection to
> the server. When connecting to SQL Server 2005, this failure may be caused
> by the fact that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)
> StackTrace: at
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
> SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
> integratedSecurity, SqlConnection owningObject)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject)
> at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> identity, SqlConnectionString connectionOptions, Object providerInfo, String
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
> owningConnection, DbConnectionPool pool, DbConnectionOptions options)
> at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
> owningObject)
> at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
> at System.Data.SqlClient.SqlConnection.Open()
> at
> Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity
> impersonatedIdentity)
> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
Complaints about the "Copy Database" wizard are common in this group,
and the response is always the same - don't use it. It's much faster
and easier to do a simple backup/restore of the database. The copy
wizard is flaky and throws up a variety of bizarre issues, depending
on what day of the week it is...|||Not for nothing but its not easier to backup/restore when i'm sucking it
down from a web server... can i get this to work'
Thanks,
David Lozzi
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173206717.382194.282980@.t69g2000cwt.googlegroups.com...
> On Mar 6, 12:21 pm, "David Lozzi" <dlo...@.nospam.nospam> wrote:
>> Howdy,
>> I'm trying to use the Copy Database wizard in SQL Server Management
>> Studio
>> to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
>> After running the wizard it errors out with the following. I can connect
>> to
>> the 2000 server fine using the management studio as well. Any ideas'
>> StackTrace: at
>> Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
>> at
>> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server&
>> server, ServerProperty serverProp)
>> InnerException-->An error has occurred while establishing a connection to
>> the server. When connecting to SQL Server 2005, this failure may be
>> caused
>> by the fact that under the default settings SQL Server does not allow
>> remote
>> connections. (provider: Named Pipes Provider, error: 40 - Could not open
>> a
>> connection to SQL Server)
>> StackTrace: at
>> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
>> exception,
>> Boolean breakConnection)
>> at
>> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
>> stateObj)
>> at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
>> SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
>> timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
>> integratedSecurity, SqlConnection owningObject)
>> at
>> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
>> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
>> timerExpire, SqlConnection owningObject)
>> at
>> System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
>> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
>> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
>> at
>> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
>> owningObject, SqlConnectionString connectionOptions, String newPassword,
>> Boolean redirectedUserInstance)
>> at
>> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
>> identity, SqlConnectionString connectionOptions, Object providerInfo,
>> String
>> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
>> at
>> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
>> options, Object poolGroupProviderInfo, DbConnectionPool pool,
>> DbConnection
>> owningConnection)
>> at
>> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
>> owningConnection, DbConnectionPool pool, DbConnectionOptions options)
>> at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
>> owningObject)
>> at
>> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
>> owningObject)
>> at
>> System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
>> owningObject)
>> at
>> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
>> owningConnection)
>> at
>> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
>> outerConnection, DbConnectionFactory connectionFactory)
>> at System.Data.SqlClient.SqlConnection.Open()
>> at
>> Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity
>> impersonatedIdentity)
>> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
> Complaints about the "Copy Database" wizard are common in this group,
> and the response is always the same - don't use it. It's much faster
> and easier to do a simple backup/restore of the database. The copy
> wizard is flaky and throws up a variety of bizarre issues, depending
> on what day of the week it is...
>

Copy Database from SQL 2000 to SQL 2005

Howdy,
I'm trying to use the Copy Database wizard in SQL Server Management Studio
to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
After running the wizard it errors out with the following. I can connect to
the 2000 server fine using the management studio as well. Any ideas'
StackTrace: at
Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at
Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenCo
nnection(Server&
server, ServerProperty serverProp)
InnerException-->An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)
StackTrace: at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentit
y
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOpti
ons
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnec
tion
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at
Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(Wind
owsIdentity
impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()On Mar 6, 12:21 pm, "David Lozzi" <dlo...@.nospam.nospam> wrote:
> Howdy,
> I'm trying to use the Copy Database wizard in SQL Server Management Studio
> to copy a database from a SQL 2000 Server to my local SQL 2005 Express.
> After running the wizard it errors out with the following. I can connect t
o
> the 2000 server fine using the management studio as well. Any ideas'
> StackTrace: at
> Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
> at
> Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.Open
Connection(Server&
> server, ServerProperty serverProp)
> InnerException-->An error has occurred while establishing a connection to
> the server. When connecting to SQL Server 2005, this failure may be cause
d
> by the fact that under the default settings SQL Server does not allow remo
te
> connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)
> StackTrace: at
> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
> SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
> integratedSecurity, SqlConnection owningObject)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject)
> at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Strin
g
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnecti
on
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdent
ity
> identity, SqlConnectionString connectionOptions, Object providerInfo, Stri
ng
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConn
ection
> owningConnection, DbConnectionPool pool, DbConnectionOptions options)
> at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
> owningObject)
> at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
> owningObject)
> at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
> at System.Data.SqlClient.SqlConnection.Open()
> at
> Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(Wi
ndowsIdentity
> impersonatedIdentity)
> at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
Complaints about the "Copy Database" wizard are common in this group,
and the response is always the same - don't use it. It's much faster
and easier to do a simple backup/restore of the database. The copy
wizard is flaky and throws up a variety of bizarre issues, depending
on what day of the week it is...|||Not for nothing but its not easier to backup/restore when i'm sucking it
down from a web server... can i get this to work'
Thanks,
David Lozzi
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1173206717.382194.282980@.t69g2000cwt.googlegroups.com...
> On Mar 6, 12:21 pm, "David Lozzi" <dlo...@.nospam.nospam> wrote:
> Complaints about the "Copy Database" wizard are common in this group,
> and the response is always the same - don't use it. It's much faster
> and easier to do a simple backup/restore of the database. The copy
> wizard is flaky and throws up a variety of bizarre issues, depending
> on what day of the week it is...
>

Copy database from remote server to local?

My domain host hosts my MS SQL 2000 database. I am able to connect to it
from my workstation using the MS SQL Server Management Studio Express
application. I want to copy from the remote server, modify it, and upload it
back to my domain host.
I don't see any method of copying the remote database to my local machine.
Help?Hi,
u can backup the database to the remote server or mirror the backup
to remote location.
u should have permission on the shared folder of the remote server.
for mirroring see help
HTH
from
Doller|||Hi,
u can backup the database to the remote server
u should have permission on the shared folder of the remote server.
HTH
from
Doller|||"doller" <sufianarif@.gmail.com> wrote in message
news:1145437778.697809.321740@.z34g2000cwc.googlegroups.com...
> Hi,
> u can backup the database to the remote server
> u should have permission on the shared folder of the remote server.
I am able to generate the backup on the remote server, but I have no access
to actually download the backup once generated.
I do have a local MS SQL 2005 server, but I can't see any method to backup
from the remote server to the local server.|||BACKUP is executed by SQL Server, so the backup file will be generated by SQ
L Server. Either direct
the file to the desired location when you do the backup (UNC path) or do it
locally and then grab
the file (COPY, FTP etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Noozer" <dont.spam@.me.here> wrote in message news:6SC1g.41524$P01.35165@.pd7tw3no...eagreen">
> "doller" <sufianarif@.gmail.com> wrote in message
> news:1145437778.697809.321740@.z34g2000cwc.googlegroups.com...
> I am able to generate the backup on the remote server, but I have no acces
s to actually download
> the backup once generated.
> I do have a local MS SQL 2005 server, but I can't see any method to backup
from the remote server
> to the local server.
>
>

Copy database from remote server to local?

My domain host hosts my MS SQL 2000 database. I am able to connect to it
from my workstation using the MS SQL Server Management Studio Express
application. I want to copy from the remote server, modify it, and upload it
back to my domain host.
I don't see any method of copying the remote database to my local machine.
Help?Hi,
u can backup the database to the remote server or mirror the backup
to remote location.
u should have permission on the shared folder of the remote server.
for mirroring see help
HTH
from
Doller|||Hi,
u can backup the database to the remote server
u should have permission on the shared folder of the remote server.
HTH
from
Doller|||"doller" <sufianarif@.gmail.com> wrote in message
news:1145437778.697809.321740@.z34g2000cwc.googlegroups.com...
> Hi,
> u can backup the database to the remote server
> u should have permission on the shared folder of the remote server.
I am able to generate the backup on the remote server, but I have no access
to actually download the backup once generated.
I do have a local MS SQL 2005 server, but I can't see any method to backup
from the remote server to the local server.|||BACKUP is executed by SQL Server, so the backup file will be generated by SQL Server. Either direct
the file to the desired location when you do the backup (UNC path) or do it locally and then grab
the file (COPY, FTP etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Noozer" <dont.spam@.me.here> wrote in message news:6SC1g.41524$P01.35165@.pd7tw3no...
> "doller" <sufianarif@.gmail.com> wrote in message
> news:1145437778.697809.321740@.z34g2000cwc.googlegroups.com...
>> Hi,
>> u can backup the database to the remote server
>> u should have permission on the shared folder of the remote server.
> I am able to generate the backup on the remote server, but I have no access to actually download
> the backup once generated.
> I do have a local MS SQL 2005 server, but I can't see any method to backup from the remote server
> to the local server.
>
>

Copy Data..

Hello, im having troubles copying data from our hosted remote server, to our
local server, we want to do this inorder to have a copy of our database
locally, incase the server ever goes down.. etc.
I have tried, grabbing the *.BAK file and it keeps giving me an error saying
the connection is lost.
I keep trying to use DTS and i get the same problem.
I need something that can copy this data locally fast, and not timeout or
drop a connection.
any idea'Hi
The problem seems to be your connectivity to the remote server rather than a
database problem!
You don't say how big your backup files are is or what method you use thr
retrieve it? If you can use FTP then you may wish to look a download manager
program that can resume from the point it was aborted (I can think of any
names at the moment).
Your outsourcer should be backing up the database regularly as part of your
disaster recover plan, maybe that could be extended to sending a copy to
yourselves? Having a copy of the database to restore may not be the only
thing that you need to do when the server goes down!!
John
Adam" <adoeler@.sharklogic.com> wrote in message
news:bvNtc.5672$9q1.5647@.news20.bellglobal.com...
> Hello, im having troubles copying data from our hosted remote server, to
our
> local server, we want to do this inorder to have a copy of our database
> locally, incase the server ever goes down.. etc.
> I have tried, grabbing the *.BAK file and it keeps giving me an error
saying
> the connection is lost.
> I keep trying to use DTS and i get the same problem.
> I need something that can copy this data locally fast, and not timeout or
> drop a connection.
> any idea'
>|||I assume you are copying the backup file. If that's the case, then you
don't need DTS, a NT job will do the work. In the job, do something like
copy \\your_remote_server_name\share_name\file_name whatever directory
you wanna put it.
First, try it under cmd prompt, if you get the same error, then there's
something wrong with your network connection, you need to fix that.
Eric Li
SQL DBA
MCDBA
Adam wrote:
> Hello, im having troubles copying data from our hosted remote server, to our
> local server, we want to do this inorder to have a copy of our database
> locally, incase the server ever goes down.. etc.
> I have tried, grabbing the *.BAK file and it keeps giving me an error saying
> the connection is lost.
> I keep trying to use DTS and i get the same problem.
> I need something that can copy this data locally fast, and not timeout or
> drop a connection.
> any idea'
>

Copy a Database from a remote server and using it on my local machine

Hi and thanks in advance for your help.

I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.

Dollarjunkie

Check out Red Gate software... Specifically the SQL Compare and Data Compare products. They are fantastic and do exactly what you want... http://redgate.com/ I've used them for years; they save SO much time when developing locally and keeping data/structure in sync.

-Damien