Copy of a database with new filenames

I have database A on files A.mdf and A.ldf
Using sp_detach_db and sp_attach_db I can get
database B on files A.mdf and A.ldf
Is there a way to get database B on B.mdf and B.ldf
not going through the copying of individual objects.
Thanks in advance
SergeiHi,
1. Backup the database A (backup database A to disk='c:\a_db.bak' with init
2. Restore database B from disk='C:\a_db.bak' with move 'logfilename' to
'path\b.mdf',
move 'loglogname' to 'path\b.ldf'
Replace path with your actual path name.
Thanks
Hari
MCDBA
"Sergei" <sergei@.kbotd.ru> wrote in message
news:#y#LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...
> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei|||Check books on line for the backup with MOVE to statement, it's straight
forward and useful
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...
> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei|||To add to the other responses, you can also rename the file to the desired
names before the attach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...
> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

Copy of a database with new filenames

I have database A on files A.mdf and A.ldf
Using sp_detach_db and sp_attach_db I can get
database B on files A.mdf and A.ldf
Is there a way to get database B on B.mdf and B.ldf
not going through the copying of individual objects.
Thanks in advance
SergeiHi,
1. Backup the database A (backup database A to disk='c:\a_db.bak' with init
2. Restore database B from disk='C:\a_db.bak' with move 'logfilename' to
'path\b.mdf',
move 'loglogname' to 'path\b.ldf'
Replace path with your actual path name.
Thanks
Hari
MCDBA
"Sergei" <sergei@.kbotd.ru> wrote in message
news:#y#LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

---

|||Check books on line for the backup with MOVE to statement, it's straight
forward and useful
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

---

|||To add to the other responses, you can also rename the file to the desired
names before the attach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sergei" <sergei@.kbotd.ru> wrote in message
news:%23y%23LzBA3DHA.560@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I have database A on files A.mdf and A.ldf
> Using sp_detach_db and sp_attach_db I can get
> database B on files A.mdf and A.ldf
> Is there a way to get database B on B.mdf and B.ldf
> not going through the copying of individual objects.
> Thanks in advance
> Sergei

---

Copy MDF and LDF files Over Existing Database

I am not a DBA and it will be obvious to you soon. I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
BenBACKUP and RESTORE are fast operations on small databases, you might want to
try that method.
You could restore
FROM FILE = 'x:\train.bak'
or
from the "device" train_devo. You have to set up this device first, so it
might just be easier to to restore from file.
You will want to (need to) use REPLACE to replace the files if they already
exist.
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153259948.748817.240020@.m73g2000cwd.googlegroups.com...
>I am not a DBA and it will be obvious to you soon. I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>|||bcumminsr@.yahoo.com wrote:
> I am not a DBA and it will be obvious to you soon. I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>
Hi
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again.
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||underprocessable|||Steen Persson (DK) wrote:
> bcumminsr@.yahoo.com wrote:
> Hi
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again.
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
> --030204050507050003020602
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 2025
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=
Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:bcumminsr@.yahoo.com"=
>bcumminsr@.yahoo.com</a> wrote:
> <blockquote
> cite=3D"mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">I am not a DBA and it will be obvious to you soon. I=
have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
> </pre>
> </blockquote>
> <font size=3D"-1"><font face=3D"Arial">Hi<br>
> <br>
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again. <br>
> <br>
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details.<br>
> <br>
> <br>
> -- <br>
> Regards<br>
> Steen Schlüter Persson<br>
> Databaseadministrator / Systemadministrator<br>
> </font></font>
> </body>
> </html>
> --030204050507050003020602--
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||Why doesn't it work? What errors do you receive?
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153327247.825744.46880@.75g2000cwc.googlegroups.com...
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||bcumminsr@.yahoo.com wrote:
> Steen Persson (DK) wrote:
>
> Thanks for all of your help. I need to do this using T-SQL and I can't
> get the RESTORE command to work. Is it possible to copy the MDF and LDF
> using T-SQL to a different location? Thanks
>
You'll have to give us some more than just "it doesn't work". If you
want to copy the files using T-sql, you can look up xp_cmdshell in Books
On Line.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

Copy MDF and LDF files Over Existing Database

I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
BenBACKUP and RESTORE are fast operations on small databases, you might want to
try that method.
You could restore
FROM FILE = 'x:\train.bak'
or
from the "device" train_devo. You have to set up this device first, so it
might just be easier to to restore from file.
You will want to (need to) use REPLACE to replace the files if they already
exist.
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153259948.748817.240020@.m73g2000cwd.googlegroups.com...
>I am not a DBA and it will be obvious to you soon. :) I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>|||This is a multi-part message in MIME format.
--030204050507050003020602
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
bcumminsr@.yahoo.com wrote:
> I am not a DBA and it will be obvious to you soon. :) I have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
>
Hi
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again.
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--030204050507050003020602
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
<blockquote
cite="mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
Ben
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again. <br>
<br>
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--030204050507050003020602--|||By the way: I mentioned that you should use backup and restore because of
this blurb related to sp_attach_db within the SQL Server 2005 Books Online:
Important:
This feature will be removed in a future version of Microsoft SQL
Server. Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. We recommend that you use
CREATE DATABASE database_name FOR ATTACH instead. For more information, see
CREATE DATABASE (Transact-SQL).
Keith Kratochvil
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OLkoterqGHA.4912@.TK2MSFTNGP05.phx.gbl...
> BACKUP and RESTORE are fast operations on small databases, you might want
> to
> try that method.
> You could restore
> FROM FILE = 'x:\train.bak'
> or
> from the "device" train_devo. You have to set up this device first, so it
> might just be easier to to restore from file.
>
> You will want to (need to) use REPLACE to replace the files if they
> already
> exist.
>
> --
> Keith Kratochvil
>
> <bcumminsr@.yahoo.com> wrote in message
> news:1153259948.748817.240020@.m73g2000cwd.googlegroups.com...
>>I am not a DBA and it will be obvious to you soon. :) I have two
>> databases on the same server. I want to basically restore a db from an
>> existing db on the same server. The catch is that I don't want to
>> restore from a backup file and I have to enable this process to run by
>> the class instructor through an ASP page calling a stored proc. Due to
>> time limitations between training classes I would just like to copy the
>> .MDF and .LDF from the Source db to the destination db and attach it.
>> I was trying the code below:
>> RESTORE DATABASE train
>> FROM train_devo
>> WITH RECOVERY,
>> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
>> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
>> GO
>> I am getting the error:
>> Server: Msg 3206, Level 16, State 1, Line 1
>> No entry in sysdevices for backup device 'train_devo'. Update
>> sysdevices and rerun statement.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> I know that I will need to kill active processes and that my record
>> doesn't exist in the sysdevices table but I don't have enough time
>> right now to study this completely.
>> Any help is greatly appreciated.
>> Thanks
>> Ben
>
begin 666 note.gif
M1TE&.#EA"@.`*`+,)`/\$`/]=6L;#QM;3UO_/`(2&A(R.C ```/__SO___P``
M`````````````````````"'Y! $```D`+ `````*``H```0HT,@.Y1 `&Z7T(
AWEIW)!E'C.16G$.2(L-1((5;%D-H@._H+HH>@.,.B*```[
`
end|||Steen Persson (DK) wrote:
> bcumminsr@.yahoo.com wrote:
> > I am not a DBA and it will be obvious to you soon. :) I have two
> > databases on the same server. I want to basically restore a db from an
> > existing db on the same server. The catch is that I don't want to
> > restore from a backup file and I have to enable this process to run by
> > the class instructor through an ASP page calling a stored proc. Due to
> > time limitations between training classes I would just like to copy the
> > .MDF and .LDF from the Source db to the destination db and attach it.
> >
> > I was trying the code below:
> >
> > RESTORE DATABASE train
> > FROM train_devo
> > WITH RECOVERY,
> > MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> > MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> > GO
> >
> > I am getting the error:
> > Server: Msg 3206, Level 16, State 1, Line 1
> > No entry in sysdevices for backup device 'train_devo'. Update
> > sysdevices and rerun statement.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > I know that I will need to kill active processes and that my record
> > doesn't exist in the sysdevices table but I don't have enough time
> > right now to study this completely.
> >
> > Any help is greatly appreciated.
> > Thanks
> > Ben
> >
> >
> Hi
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again.
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
> --030204050507050003020602
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 2025
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:bcumminsr@.yahoo.com"=>bcumminsr@.yahoo.com</a> wrote:
> <blockquote
> cite=3D"mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">I am not a DBA and it will be obvious to you soon. :) I= have two
> databases on the same server. I want to basically restore a db from an
> existing db on the same server. The catch is that I don't want to
> restore from a backup file and I have to enable this process to run by
> the class instructor through an ASP page calling a stored proc. Due to
> time limitations between training classes I would just like to copy the
> .MDF and .LDF from the Source db to the destination db and attach it.
> I was trying the code below:
> RESTORE DATABASE train
> FROM train_devo
> WITH RECOVERY,
> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
> GO
> I am getting the error:
> Server: Msg 3206, Level 16, State 1, Line 1
> No entry in sysdevices for backup device 'train_devo'. Update
> sysdevices and rerun statement.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I know that I will need to kill active processes and that my record
> doesn't exist in the sysdevices table but I don't have enough time
> right now to study this completely.
> Any help is greatly appreciated.
> Thanks
> Ben
> </pre>
> </blockquote>
> <font size=3D"-1"><font face=3D"Arial">Hi<br>
> <br>
> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
> existing database, copy the files, and then reattach the db's again. <br>
> <br>
> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
> for details.<br>
> <br>
> <br>
> -- <br>
> Regards<br>
> Steen Schlüter Persson<br>
> Databaseadministrator / Systemadministrator<br>
> </font></font>
> </body>
> </html>
> --030204050507050003020602--
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||Why doesn't it work? What errors do you receive?
--
Keith Kratochvil
<bcumminsr@.yahoo.com> wrote in message
news:1153327247.825744.46880@.75g2000cwc.googlegroups.com...
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks|||This is a multi-part message in MIME format.
--050402070308080300020405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
bcumminsr@.yahoo.com wrote:
> Steen Persson (DK) wrote:
>> bcumminsr@.yahoo.com wrote:
>> I am not a DBA and it will be obvious to you soon. :) I have two
>> databases on the same server. I want to basically restore a db from an
>> existing db on the same server. The catch is that I don't want to
>> restore from a backup file and I have to enable this process to run by
>> the class instructor through an ASP page calling a stored proc. Due to
>> time limitations between training classes I would just like to copy the
>> .MDF and .LDF from the Source db to the destination db and attach it.
>> I was trying the code below:
>> RESTORE DATABASE train
>> FROM train_devo
>> WITH RECOVERY,
>> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
>> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
>> GO
>> I am getting the error:
>> Server: Msg 3206, Level 16, State 1, Line 1
>> No entry in sysdevices for backup device 'train_devo'. Update
>> sysdevices and rerun statement.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> I know that I will need to kill active processes and that my record
>> doesn't exist in the sysdevices table but I don't have enough time
>> right now to study this completely.
>> Any help is greatly appreciated.
>> Thanks
>> Ben
>>
>> Hi
>> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
>> existing database, copy the files, and then reattach the db's again.
>> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
>> for details.
>>
>> --
>> Regards
>> Steen Schlüter Persson
>> Databaseadministrator / Systemadministrator
>> --030204050507050003020602
>> Content-Type: text/html; charset=ISO-8859-1
>> X-Google-AttachSize: 2025
>> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>> <html>
>> <head>
>> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
>> </head>
>> <body bgcolor="#ffffff" text="#000000">
>> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
>> <blockquote
>> cite="mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"
>> type="cite">
>> <pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
>> databases on the same server. I want to basically restore a db from an
>> existing db on the same server. The catch is that I don't want to
>> restore from a backup file and I have to enable this process to run by
>> the class instructor through an ASP page calling a stored proc. Due to
>> time limitations between training classes I would just like to copy the
>> .MDF and .LDF from the Source db to the destination db and attach it.
>> I was trying the code below:
>> RESTORE DATABASE train
>> FROM train_devo
>> WITH RECOVERY,
>> MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
>> MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
>> GO
>> I am getting the error:
>> Server: Msg 3206, Level 16, State 1, Line 1
>> No entry in sysdevices for backup device 'train_devo'. Update
>> sysdevices and rerun statement.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> I know that I will need to kill active processes and that my record
>> doesn't exist in the sysdevices table but I don't have enough time
>> right now to study this completely.
>> Any help is greatly appreciated.
>> Thanks
>> Ben
>> </pre>
>> </blockquote>
>> <font size="-1"><font face="Arial">Hi<br>
>> <br>
>> You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
>> existing database, copy the files, and then reattach the db's again. <br>
>> <br>
>> Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
>> for details.<br>
>> <br>
>> <br>
>> -- <br>
>> Regards<br>
>> Steen Schlüter Persson<br>
>> Databaseadministrator / Systemadministrator<br>
>> </font></font>
>> </body>
>> </html>
>> --030204050507050003020602--
> Thanks for all of your help. I need to do this using T-SQL and I can't
> get the RESTORE command to work. Is it possible to copy the MDF and LDF
> using T-SQL to a different location? Thanks
>
You'll have to give us some more than just "it doesn't work". If you
want to copy the files using T-sql, you can look up xp_cmdshell in Books
On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--050402070308080300020405
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
<blockquote
cite="mid1153327247.825744.46880@.75g2000cwc.googlegroups.com"
type="cite">
<pre wrap="">Steen Persson (DK) wrote:
</pre>
<blockquote type="cite">
<pre wrap=""><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
Ben
</pre>
</blockquote>
<pre wrap="">Hi
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again.
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--030204050507050003020602
Content-Type: text/html; charset=ISO-8859-1
X-Google-AttachSize: 2025
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">"mailto:bcumminsr@.yahoo.com"</a>><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:bcumminsr@.yahoo.com">bcumminsr@.yahoo.com</a></a> wrote:
<blockquote
cite=<a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com">"mid1153259948.748817.240020@.m73g2000cwd.googlegroups.com"</a>
type="cite">
<pre wrap="">I am not a DBA and it will be obvious to you soon. :) I have two
databases on the same server. I want to basically restore a db from an
existing db on the same server. The catch is that I don't want to
restore from a backup file and I have to enable this process to run by
the class instructor through an ASP page calling a stored proc. Due to
time limitations between training classes I would just like to copy the
.MDF and .LDF from the Source db to the destination db and attach it.
I was trying the code below:
RESTORE DATABASE train
FROM train_devo
WITH RECOVERY,
MOVE 'c:\train_devo_data.mdf' TO 'c:\train_data.mdf',
MOVE 'c:\train_devo_log.ldf' TO 'c:\train_log.ldf'
GO
I am getting the error:
Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'train_devo'. Update
sysdevices and rerun statement.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I know that I will need to kill active processes and that my record
doesn't exist in the sysdevices table but I don't have enough time
right now to study this completely.
Any help is greatly appreciated.
Thanks
Ben
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi<br>
<br>
You can use BACKUP/RESTORE as suggested by Keith, or you can detach the
existing database, copy the files, and then reattach the db's again. <br>
<br>
Try to look up RESTORE and sp_detach_db/sp_attach_db in Books On Line
for details.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schl&uuml;ter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--030204050507050003020602--
</pre>
</blockquote>
<pre wrap=""><!-->
Thanks for all of your help. I need to do this using T-SQL and I can't
get the RESTORE command to work. Is it possible to copy the MDF and LDF
using T-SQL to a different location? Thanks
</pre>
</blockquote>
<font size="-1"><font face="Arial">You'll have to give us some more
than just "it doesn't work". If you want to copy the files using T-sql,
you can look up xp_cmdshell in Books On Line.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--050402070308080300020405--

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 Folders and Files to different location

I have a situation from where I need to loop through different folders and files in these folders. After processing these files, I need to archive these folders to different location.

e.g., C:\MainFolder\Mar01 ==> Multiple files in Mar01 folder

C:\MainFolder\Mar02 ==> Multiple files in Mar02 folder

Does any one know the best way to do this in SSIS?

Thanks in advance.

BC

One way would be a For Each Loop container with Traverse subfolders enabled, with a File System task inside it to move the files once they've been processed.

Copy files from one server to another

hi,
I have a sql box under diff domain. I login as sqladmin mapped a network
drive pointed to the dir that I need to copy the files.
I created a job under command prompt--copy /Y z:\*'* \\server2\myfoleder\
If I run it under ms dos --OK, but running it through sql server job, got
this error:
The system cannot find the drive specified.
Any ideas
ThanksdOk, it is all a matter of security contexts:
WHO are you when you run from the command line?
WHO are you when you run a SQL Server Job:
If job runs as system administrator?
If job runs as a non-system administrator?
Does whatever account is actually running the job actually have rights to
the drive? Apparently not.
Also, FWIW, it is usually better not to use drive letters. Make a share and
use it with the standard non-dos notation.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23THGzfPTGHA.4752@.TK2MSFTNGP10.phx.gbl...
> hi,
> I have a sql box under diff domain. I login as sqladmin mapped a network
> drive pointed to the dir that I need to copy the files.
> I created a job under command prompt--copy /Y z:\*'* \\server2\myfoleder\
> If I run it under ms dos --OK, but running it through sql server job,
> got this error:
> The system cannot find the drive specified.
>
> Any ideas
> Thanksd
>

Copy files after backup in maintenance plan

Hello,

We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?

Thanks in advance.

Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........

else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.

Thanxx

Deepak

|||

Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.

Madhu

|||

Thank you both for the fast replys....

From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?

Thanks again in advance!

|||

Hi Marty,

Go to BIDS--> create a New project -- > Open the project.

In solution explorer you can see "SSIS packages" folder --> Right click on the folder

Select "Add existing package"

Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.

|||Excellent...thank you so much

Copy files after backup in maintenance plan

Hello,

We're new users of SQL Server 2005. I created two maintenance plans...one to backup the database at 2 AM daily and one to back up transaction logs every 30 minutes. These maintenance plans write to a local disk. What we want to do, within the maintenance plan, is copy the files as soon as they are written to a remote server. Is that possible?

Thanks in advance.

Create a shared folder in the remote server and specify the path in the maintenace plan so that the backup will directly fall in the remote server. You need to ensure that the sql startup account should have modify privileges to the shared folder in remote server for the maintenance jobs to succeed !..........

else you can also add a job step in maintenance jobs using xcopy command to copy the files to remote server.

Thanxx

Deepak

|||

Maintenance Plan is nothing but a SSIS (Integration Services) project. What u can do is open the MP in BIDS (business intellegence Development studio) ENV and add a *File System Task* and make the requried changes.

Madhu

|||

Thank you both for the fast replys....

From what I can tell, the maintenance plans are stored in the database. However, when I go into BIDS the file/open is only for OS files. What do I need to do to open the maintenance plan in BIDS?

Thanks again in advance!

|||

Hi Marty,

Go to BIDS--> create a New project -- > Open the project.

In solution explorer you can see "SSIS packages" folder --> Right click on the folder

Select "Add existing package"

Mention the servername & Authentication mode in the window then you can select the maintainence plan you have created.

|||Excellent...thank you so muchsqlsql

copy files

Hi,
I have two servers within the same domain. Using CMD Prompt, when I copy
files from one server to another, it works flawlessly, eg. COPY
D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
However, when I use the same command in a CMDEXEC step of a job, it gives me
an Access Denied error message.
Any help. Thanks in advance for all your help.Make sure that the account that the SQL Server Agent runs in has permissions
on the source/ destination. Thats the account that jobs run in.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:3FED8D10-A43F-431A-94EF-0F51271B5E82@.microsoft.com...
> Hi,
> I have two servers within the same domain. Using CMD Prompt, when I copy
> files from one server to another, it works flawlessly, eg. COPY
> D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
> However, when I use the same command in a CMDEXEC step of a job, it gives
me
> an Access Denied error message.
> Any help. Thanks in advance for all your help.

copy files

Hi,
I have two servers within the same domain. Using CMD Prompt, when I copy
files from one server to another, it works flawlessly, eg. COPY
D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
However, when I use the same command in a CMDEXEC step of a job, it gives me
an Access Denied error message.
Any help. Thanks in advance for all your help.Make sure that the account that the SQL Server Agent runs in has permissions
on the source/ destination. Thats the account that jobs run in.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:3FED8D10-A43F-431A-94EF-0F51271B5E82@.microsoft.com...
> Hi,
> I have two servers within the same domain. Using CMD Prompt, when I copy
> files from one server to another, it works flawlessly, eg. COPY
> D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
> However, when I use the same command in a CMDEXEC step of a job, it gives
me
> an Access Denied error message.
> Any help. Thanks in advance for all your help.

copy files

Hi,
I have two servers within the same domain. Using CMD Prompt, when I copy
files from one server to another, it works flawlessly, eg. COPY
D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
However, when I use the same command in a CMDEXEC step of a job, it gives me
an Access Denied error message.
Any help. Thanks in advance for all your help.
Make sure that the account that the SQL Server Agent runs in has permissions
on the source/ destination. Thats the account that jobs run in.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:3FED8D10-A43F-431A-94EF-0F51271B5E82@.microsoft.com...
> Hi,
> I have two servers within the same domain. Using CMD Prompt, when I copy
> files from one server to another, it works flawlessly, eg. COPY
> D:\SQL8Bkup4DRP\*.BAK \\<otherServerName>\D$\SQL8Bkup4DRP
> However, when I use the same command in a CMDEXEC step of a job, it gives
me
> an Access Denied error message.
> Any help. Thanks in advance for all your help.

copy file automatically

I need to copy files from one machine to another machine. It should be done automatically. Could you please give me a suggestion. I have no idea about this. It should write a script or program. But I have no idea yet,

Thanksxcopy utility would help..|||Can you give me more detail about xcopy utility? Where is it? how can i learn it? Thanks in advance.|||open a dos window.

at the prompt type: help xcopy|||i try this in cmd command:
C:\>xcopy c:YTEST \\Server25702\C:\YTEST1

i want to copy the YTEST file to another machine (called Server25702). but it gave me a error message:
Invalid drive specification
0 File(s) copied

What wrong with it, also i change Server25702 to IP address, it also gave me a same message. Can you help me again? Thanks.|||You have to map a drive to \\server25702\c:\ytest1 before you can use xcopy.

So if you use the map command to map drive letter D - your syntax would be:

xcopy c:\ytest d:\ytest

i try this in cmd command:
C:\>xcopy c:YTEST \\Server25702\C:\YTEST1

i want to copy the YTEST file to another machine (called Server25702). but it gave me a error message:
Invalid drive specification
0 File(s) copied

What wrong with it, also i change Server25702 to IP address, it also gave me a same message. Can you help me again? Thanks.|||i try to use map drive, i type \\Server25702, it give me message, the network drive can't be found. Why? Thanks.|||you will need to share the drive on server25702|||Thanks, i got it.
But after i copied, i found all the folder in the source file didn't copy to destination, Can this copy anything in the folder i selected to the destination?

I am going to save it as .bat file, How can i use window to schedule it to run from Monday to Friday. Thanks.

copy file

hi guys,

anybody know the syntax in SQL to copy file to other server.

we want to copy our db backup files automaticaly to other server.

thanks

-wei

Hi

It wud be helpful if u can be more specific, like if u wanna back up ur entire DB or just copy data from one table to another table in another DB

|||

hi,

I want to copy file to other server using SQL.

I found out that we can use xp_cmdshell but I can make it work.

my syntax is " exec xp_cmdshell 'copy c:\folder\test.xxx

\\server1\folder1, no_ouput ;

on the result pane it said succesful, but when I look to the destination folder there is no file that I copied.

|||

hi dedy,

hi try mapping "\\server1\folder1" to qa drive letter

to map

1. open windows explorer.

2. click tools.

3. clcik on map network drive

4. enter necessary permission

you can assign a specific user (using different username option) to your mappings

to make sure you wont run on a permission problem with NTFS

run

exec xp_cmdshell 'copy c:\folder\test.xxx x:' <--where x: is the mapped network drive

regards,

joey

|||

hi joey,

I have tries your suggestion.

but there is an error that said "The system cannot find the drive specified"

I'm using SQL 2005.

I already map the folder using administrator account.

please advice.

Copy external files to different locations

Hi,

I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.

I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.

Many thanks in advanceMark,

this may be a situation where you want to take a step backwards in the requirements definition process and look at the problem from a different context: what is it that your users need? While there are ways to copy data/files from point A to point B using SQL Server, there may be an entirely different approach that would be more in keeping with best practices.

As a first step, I might suggest investigating DTS (Data Transformation Services). I would also look at SQL Reporting Services (though you need a bit of experience setting this up; I wouldn't recommend it for a newbie).

You may also want to consider Access Data Projects (ADP); I don't recommend them for development, but they do provide serviceable reporting tools.

Regardless, focus on the user requirements (not the way they've always dones things, but rather what they really use the information for and how they use it). All that being said, of course you want to meet whatever need in the shortest amount of time possible. Be prepared to take an incremental approach and "lead" your users to a better solution down the road.

Regards,

hmscott|||Thanks for getting back to me.

What I have to do is take data extracts suplied to me by our IT dept and manipulate them, then supply various extracts out of this back to various teams in the company. Because of the way our place works I'd export to another database, zip it, and then move it to a shared (or sometimes protected) location each team has access to.

I used to do this via Access, but now want to up my skills and improve the way I do things.
My theory was:
1. DTS import the data (18 files)
2. Managet the data
3. Export the data
4. Zip the files and move to locations

To be honest I was just hoping it was as simple as a FileCopy but I was just missing it in the ActiveX.

If it's just not feasible yet for me at my skill level I can appreciate that, just thought I'd ask the question.

Many thanks|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'|||I think a light bulb has just come on.
Is it not just the File Transfer Protocol Task.
Just tested it and seems to work fine.|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'

You can use this method across the network if:
1. You use UNC naming conventions (\\SERVER\Share\folder\file.zip)
2. You SQL Server is running under a service account (not LOCALSYSTEM) and this account has write permissions on the target server.

Still, you might want to consider some other (more scalable/reliable) solutions such as:
1. ADP (already mentioned)
2. Replication to an Access database (never tried it myself, but it's supposed to work)
3. Use DTS to generate the data to the target file (DTS has export connections for Excel, MS Access and other target file types).

Regards,

hmscott

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of about
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
Chad
Hello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad
|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of about
> 12GB. I connected each server -- separate boxes -- to my internal network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> the CDW is still processing and I can see that the largest of the ldf files
> has copied over to the 2005 server, but basically I'm wondering, what's the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.
|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:

> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of about
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
ChadHello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of about
> 12GB. I connected each server -- separate boxes -- to my internal network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> the CDW is still processing and I can see that the largest of the ldf files
> has copied over to the 2005 server, but basically I'm wondering, what's the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:
> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> > Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> > original is composed of 2 or 3 data files and 1 translog file, total of about
> > 12GB. I connected each server -- separate boxes -- to my internal network,
> > the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> > the CDW is still processing and I can see that the largest of the ldf files
> > has copied over to the 2005 server, but basically I'm wondering, what's the
> > metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> > seen it work faster? Just seems a bit slow to me . . .
> >
> > Thanks,
> >
> > Chad
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of abou
t
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours
,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
ChadHello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of ab
out
> 12GB. I connected each server -- separate boxes -- to my internal network
,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hou
rs,
> the CDW is still processing and I can see that the largest of the ldf file
s
> has copied over to the 2005 server, but basically I'm wondering, what's th
e
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CD
W
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so
I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:

> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,