Converting problem: 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>
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 

</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>
> 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 
> 
> </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>
>> 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 
>> 
>> </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>
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 

</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>
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 

</body>
</html>
--050402070308080300020405--

Thursday, March 29, 2012

Copy MDF and LDF files Over Existing Database

No comments:

Post a Comment

Converting problem

Blog Archive

About Me