Copy datafile on server to server?

Hi all,
Is there a command to copy a file on a sql server directory to another
directory on the same server?
I want to take the database off-line, then copy the datafile from one
directory to another. I am remote to the server and want a speedy copy made
by the server not my client.
After, I would bring the database back on-line and have the copy available
to download locally.
Thanks,
John.You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
and then sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is there a command to copy a file on a sql server directory to another
> directory on the same server?
> I want to take the database off-line, then copy the datafile from one
> directory to another. I am remote to the server and want a speedy copy
made
> by the server not my client.
> After, I would bring the database back on-line and have the copy available
> to download locally.
> Thanks,
> John.
>|||Hi,
To Add on to Tibers post, you can create a batch file in SQL Server and
schedule as as Job.
1. Perform the back of database (Use Restore Database)
2. Map the drive of remote server ( Net use command)
3. Copy the file to remote server and remove the mapping (Use net use drive
letter /d)
4. Load this backup file in remote server
This dont require your server to go offline.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is there a command to copy a file on a sql server directory to another
> directory on the same server?
> I want to take the database off-line, then copy the datafile from one
> directory to another. I am remote to the server and want a speedy copy
made
> by the server not my client.
> After, I would bring the database back on-line and have the copy available
> to download locally.
> Thanks,
> John.
>|||Is this command run from Query Analyzer? If not where is it to be ran from?
John.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23ifWUrlwDHA.2528@.TK2MSFTNGP10.phx.gbl...
> You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
> and then sp_attach_db.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > Is there a command to copy a file on a sql server directory to another
> > directory on the same server?
> > I want to take the database off-line, then copy the datafile from one
> > directory to another. I am remote to the server and want a speedy copy
> made
> > by the server not my client.
> >
> > After, I would bring the database back on-line and have the copy
available
> > to download locally.
> >
> > Thanks,
> > John.
> >
> >
>|||This is great idea; one that I keep forgetting about because my backup file
is over a gig and is too big for me to copy. What do you mean by "(Use
Restore Database)? Are you just saying that I would want to use the Restore
Database functions once I have the backup local?
John.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> Hi,
> To Add on to Tibers post, you can create a batch file in SQL Server and
> schedule as as Job.
> 1. Perform the back of database (Use Restore Database)
> 2. Map the drive of remote server ( Net use command)
> 3. Copy the file to remote server and remove the mapping (Use net use
drive
> letter /d)
> 4. Load this backup file in remote server
> This dont require your server to go offline.
> Thanks
> Hari
> MCDBA
>
>
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > Is there a command to copy a file on a sql server directory to another
> > directory on the same server?
> > I want to take the database off-line, then copy the datafile from one
> > directory to another. I am remote to the server and want a speedy copy
> made
> > by the server not my client.
> >
> > After, I would bring the database back on-line and have the copy
available
> > to download locally.
> >
> > Thanks,
> > John.
> >
> >
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Rugo" <jrugo@.patmedia.net> wrote in message news:%232JAATwwDHA.560@.TK2MSFTNGP11.phx.gbl...
> Is this command run from Query Analyzer? If not where is it to be ran from?
> John.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23ifWUrlwDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > You can use sp_detach_db, then xp_cmdshell to execute a "DOS" copy command
> > and then sp_attach_db.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>|||John
Another appoach
After backuping your database you can compres it by using WINRAR or WINZIP
and then copy it to another server.
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:us4SIUwwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> This is great idea; one that I keep forgetting about because my backup
file
> is over a gig and is too big for me to copy. What do you mean by "(Use
> Restore Database)? Are you just saying that I would want to use the
Restore
> Database functions once I have the backup local?
> John.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > To Add on to Tibers post, you can create a batch file in SQL Server and
> > schedule as as Job.
> >
> > 1. Perform the back of database (Use Restore Database)
> > 2. Map the drive of remote server ( Net use command)
> > 3. Copy the file to remote server and remove the mapping (Use net use
> drive
> > letter /d)
> > 4. Load this backup file in remote server
> >
> > This dont require your server to go offline.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to
another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy
copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>|||Hi,
Yes John, you are correct.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:us4SIUwwDHA.2340@.TK2MSFTNGP12.phx.gbl...
> This is great idea; one that I keep forgetting about because my backup
file
> is over a gig and is too big for me to copy. What do you mean by "(Use
> Restore Database)? Are you just saying that I would want to use the
Restore
> Database functions once I have the backup local?
> John.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eZw9JrmwDHA.1364@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > To Add on to Tibers post, you can create a batch file in SQL Server and
> > schedule as as Job.
> >
> > 1. Perform the back of database (Use Restore Database)
> > 2. Map the drive of remote server ( Net use command)
> > 3. Copy the file to remote server and remove the mapping (Use net use
> drive
> > letter /d)
> > 4. Load this backup file in remote server
> >
> > This dont require your server to go offline.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> > "John Rugo" <jrugo@.patmedia.net> wrote in message
> > news:egBIOplwDHA.3468@.TK2MSFTNGP11.phx.gbl...
> > > Hi all,
> > > Is there a command to copy a file on a sql server directory to
another
> > > directory on the same server?
> > > I want to take the database off-line, then copy the datafile from one
> > > directory to another. I am remote to the server and want a speedy
copy
> > made
> > > by the server not my client.
> > >
> > > After, I would bring the database back on-line and have the copy
> available
> > > to download locally.
> > >
> > > Thanks,
> > > John.
> > >
> > >
> >
> >
>

Copy Database Wizard

Using SQL 2000 SP3a on Windows 2000 Server, Active Directory domain. I want
to copy a database from a development server to a production server. But
when I run the Copy Database Wizard, it says the SQL server Service can't
run under the local system account. I've searched MSDN and couldn't find
anything on this subject. Do I need to enable the Interact with the Desktop
option? Or do I need to create a Domain User for both servers to run under?
Or local users on each machine with some extra priveleges?Have a look at the "Permissions to Copy the Files" section in
INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274463
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ron Hinds" <__NoSpam@.__NoSpamramac.com> wrote in message
news:e8hQmUurDHA.3748@.TK2MSFTNGP11.phx.gbl...
Using SQL 2000 SP3a on Windows 2000 Server, Active Directory domain. I want
to copy a database from a development server to a production server. But
when I run the Copy Database Wizard, it says the SQL server Service can't
run under the local system account. I've searched MSDN and couldn't find
anything on this subject. Do I need to enable the Interact with the Desktop
option? Or do I need to create a Domain User for both servers to run under?
Or local users on each machine with some extra priveleges?