copy from C drive of sql server to another sever!

help!
I'm using a SQL server 2000 box and I'm a member of symins on it and I
have a remote drive mounted on the server that I can copy files to with
Explorer and the command line.
I've created a job that has the following line of SQL in it
xpcmdShell 'copy "C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\test.txt" "\\madupserver\madeupshare$\testzzzzz.txt"'
and whilst I can make this work in the command line, it tells that my access
is denied...
what can I do to run xpCmdShell with the same priviliges as I have when I'm
running the copy command via the UI of the server
Thanks in advance and regards
CharlesAthis could running as some other user
though i've never encountered this, you could check out who this is
running under using enterprize manager.|||"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:BCFB6F48-079E-4FFE-A966-22F3B4731A4A@.microsoft.com...
> help!
> I'm using a SQL server 2000 box and I'm a member of symins on it and I
> have a remote drive mounted on the server that I can copy files to with
> Explorer and the command line.
> I've created a job that has the following line of SQL in it
> xpcmdShell 'copy "C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\test.txt" "\\madupserver\madeupshare$\testzzzzz.txt"'
> and whilst I can make this work in the command line, it tells that my
> access
> is denied...
> what can I do to run xpCmdShell with the same priviliges as I have when
> I'm
> running the copy command via the UI of the server
> Thanks in advance and regards
> CharlesA
>
If the SQL Server login running the account is a member of the symins
group, then xp_cmdshell will use the Windows Security account associated
with the MSSQLServer service. If the SQL Server login is not a member of
symins, then xp_cmdshell will run in the context of the SQL Server Agent
Proxy account.
Given your scenario, you need to ensure that the Windows account has the
appropriate privileges on the share.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Rick,
I'm pretty sure we've had an email dialog before because I remember using
your magnificent book 'TY SQL server 2000 in 21 days' (From SAMS) which got
me into the right thinking mode about SQL server (after years of Access dev
work) I can heartily recommend this work to anyone wishing to understand how
to be a first-time DBA
I hope you're updating it for 2005!
Thanks for you helpful post,
Regards
CharlesAsqlsql

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 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 table minimizing log space

Yes, I know this is a silly situation.
I have an application that uploads several tables to a remote SQL Server
that is part of a web site. The records are uploaded into a dedicated
upload table, then the table they belong in is truncated and the records
copied using
INSERT INTO ... (SELECT ...)
My problem is that the web hosting company is very stingy with log space
and this approach will place the entire table into the log during the
copy. This is failing for the larger tables.
Is there are way for a remote app to copy data from one table to
another, minimizing the use of log space. I have no access to the file
system, so bulk copy is not an option.
Thanks,
++PLSHi
How big are your tables?
What is RECOVERY mode of the database?
Can you divide you inserting into a small transaction ?
INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
OR
SET ROWCOUNT 1000
INSERT INTO Table SELECT * FROM OtherTable
SET ROWCOUNT 0
"pls" <elth-rs1p@.spamex.com> wrote in message
news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
> Yes, I know this is a silly situation.
> I have an application that uploads several tables to a remote SQL Server
> that is part of a web site. The records are uploaded into a dedicated
> upload table, then the table they belong in is truncated and the records
> copied using
> INSERT INTO ... (SELECT ...)
> My problem is that the web hosting company is very stingy with log space
> and this approach will place the entire table into the log during the
> copy. This is failing for the larger tables.
> Is there are way for a remote app to copy data from one table to another,
> minimizing the use of log space. I have no access to the file system, so
> bulk copy is not an option.
> Thanks,
> ++PLS|||RECOVERY mode is simple.
I can't think of any logic that could be in a WHERE clause to divide the
table anything like evenly. I like your second approach (although I
think you meant ...SELECT TOP 1000). But then how do you get to the
second 1000?
++PLS
Uri Dimant wrote:
> Hi
> How big are your tables?
> What is RECOVERY mode of the database?
> Can you divide you inserting into a small transaction ?
> INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
> OR
> SET ROWCOUNT 1000
> INSERT INTO Table SELECT * FROM OtherTable
> SET ROWCOUNT 0
> "pls" <elth-rs1p@.spamex.com> wrote in message
> news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
>
>
>|||pls
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
No, it is exactly SET ROWCOUNT (See in the BOL)
CREATE TABLE #Test
(
col INT NOT NULL PRIMARY KEY
)
--Insert 10 rows
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
INSERT INTO #Test VALUES (3)
INSERT INTO #Test VALUES (4)
INSERT INTO #Test VALUES (5)
INSERT INTO #Test VALUES (6)
INSERT INTO #Test VALUES (7)
INSERT INTO #Test VALUES (8)
INSERT INTO #Test VALUES (9)
INSERT INTO #Test VALUES (10)
SELECT * FROM #Test
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The first five rows were deleted
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The table is empty now
"pls" <elth-rs1p@.spamex.com> wrote in message
news:eTetGT9JGHA.2336@.TK2MSFTNGP12.phx.gbl...
> RECOVERY mode is simple.
> I can't think of any logic that could be in a WHERE clause to divide the
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
> ++PLS
> Uri Dimant wrote:

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

Copies over links

Hi if I have a backup file in a remote 'US' location of
3gb and I use getright or wget to copy this to a location
in london I want to now add another location Ireland to
copy this file to, will this extend the time of the copy
to london if I want to copy this data to both locations at
the same time. Both locations have separate links to the US
Thanks for any advice
Deanny
Deanny,
as you have separate links, there isn't bandwidth contention. However, there
will be disk useage contention unless you're writing to separate disks which
have separate disk controllers.
HTH,
Paul Ibison