Converting problem: databases

Showing posts with label databases. Show all posts

Thursday, March 29, 2012

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

Tuesday, March 27, 2012

Copy job from one server to another

How do you copy a job from one server to another. Identical databases different servers with different names
Hi,
There are 3 options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
"Transfer Jobs" Task in DTS, That will transfer the jobs to new server.
3.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:B1336E38-C8C8-4F1B-BB8D-87E92ADD338F@.microsoft.com...
> How do you copy a job from one server to another. Identical databases
different servers with different names

copy from sqlsrv2000 to sqlsrv 7

Hi
Unfortunality have one of my clients sqlsrv 7 and whant to
remain to that version, how do I do to convert my
sqlsrv2000 databases to 7? Any good documentation/link
about that?
thanks!!
Staffan
Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan
|||Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan

copy from sqlsrv2000 to sqlsrv 7

Hi
Unfortunality have one of my clients sqlsrv 7 and whant to
remain to that version, how do I do to convert my
sqlsrv2000 databases to 7? Any good documentation/link
about that?
thanks!!
StaffanHi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx
.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan|||Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx
.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan

copy from sqlsrv2000 to sqlsrv 7

Hi
Unfortunality have one of my clients sqlsrv 7 and whant to
remain to that version, how do I do to convert my
sqlsrv2000 databases to 7? Any good documentation/link
about that?
thanks!!
StaffanHi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan|||Hi,
Due to architectural changes backup compatibility from SQL 2000 down to SQL
7 is not possible.
Only option is:-
1. Craete a database in SQL 7
2. Script out all the objects using Generate scripts in SQL 2000
3. Run and create the objects in SQL 7 database. ( Some script will fail if
you used new features of SQL 2000 like table data type...)
4. USE DTS to transfer the data from SQL 2000 to SQL 7 ( You can also use
BCP OUT from 2000 and BCP IN in sql 7)
Thanks
Hari
MCDBA
"Staffan" <anonymous@.discussions.microsoft.com> wrote in message
news:128f901c442d5$c58f8100$a101280a@.phx.gbl...
> Hi
> Unfortunality have one of my clients sqlsrv 7 and whant to
> remain to that version, how do I do to convert my
> sqlsrv2000 databases to 7? Any good documentation/link
> about that?
> thanks!!
> Staffan

Sunday, March 25, 2012

Copy DB,Packages etc to another computer ..

If I need to move my SQL server to another bigger
computer.
How can I copy the all the databases and all my
Transformation packages over properly ? I don't want to
recreate everything again.
Can I use the detach and copy the database command and
recopy to the new server ?
How about the Transformation packages ?
What else do I need to copy over ?
Any advice is greatly welcomeHi,
I will suggest you to do these steps;
1. Install SQL server in the new server with same folder structure as old
server
2. Apply the Service packs identical to old server
3. Stop SQL server and SQL Agent in old server and new server
4. Copy ALL MDF and LDF files from old server to new server ( In the same
folder) - Include system databases as well
5. Start the SQLserver in New server
6. If you need to change the SQl server name in new server then
sp_dropserver <oldservername>
go
sp_addserver <newserver>,local
7. Restart SQL server and SQl agent services
This will ensure that all the database in old system is up in new server,
DTS Packages, Jobs, Alerts , Logins, Users into the new server, etc...
(Same as old server).
Note:
But few of DBAs wont agree the above concept, Microsoft has got a good
article on moving the database, Have a look into that.
http://support.microsoft.com/defaul...kb;en-us;314546
Thanks
Hari
MCDBA
"jen" <anonymous@.discussions.microsoft.com> wrote in message
news:11b7801c410b4$64819c70$a301280a@.phx
.gbl...
> If I need to move my SQL server to another bigger
> computer.
> How can I copy the all the databases and all my
> Transformation packages over properly ? I don't want to
> recreate everything again.
>
> Can I use the detach and copy the database command and
> recopy to the new server ?
> How about the Transformation packages ?
> What else do I need to copy over ?
> Any advice is greatly welcome|||Hi Jen
Check out http://support.microsoft.com/defaul...en-us;Q314546#2
regarding different ways to move databases. The easiest methods (IMO) are
using sp_detach_db/sp_attach_db or backup/restore.
John

Copy databses from production server

Hi all:
I need to copy 2 relatively small databases from a SQL server, and then
bring those databases over to a test SQL server. I tried to do a restore on
the test serer from a backup copy of the production databases, but I think
the DBs have to exist on the test server before the restore can happen.
Can someone tell me the easiest way to copy the databases? I do not have a
direct connection to the test server so I need to make full copies of the DBs
and then copy them to the test server and then get them into SQL on the test
machine.
Can I select Tasks->Copy Databases and copy the databases to a local
directory? Can I do this while SQL Server is running without causing a
problem?
Thanks for any and all help.
John.
Do a backup and restore.
Backup the database.
Copy the database backup to the new server.
Create a database which will be the shell you will restore the database
backup into.
There is an option when you do the restore to replace existing database. Use
this option.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.discussions.microsoft.com> wrote in message
news:68305293-61F5-4C18-94E7-664148BB361D@.microsoft.com...
>
> Hi all:
> I need to copy 2 relatively small databases from a SQL server, and then
> bring those databases over to a test SQL server. I tried to do a restore
> on
> the test serer from a backup copy of the production databases, but I think
> the DBs have to exist on the test server before the restore can happen.
> Can someone tell me the easiest way to copy the databases? I do not have a
> direct connection to the test server so I need to make full copies of the
> DBs
> and then copy them to the test server and then get them into SQL on the
> test
> machine.
> Can I select Tasks->Copy Databases and copy the databases to a local
> directory? Can I do this while SQL Server is running without causing a
> problem?
> Thanks for any and all help.
> John.
>
|||Hi Hilary:
Thank you for your message. That seemed to work.
John.
"Hilary Cotter" wrote:

> Do a backup and restore.
> Backup the database.
> Copy the database backup to the new server.
> Create a database which will be the shell you will restore the database
> backup into.
> There is an option when you do the restore to replace existing database. Use
> this option.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <John@.discussions.microsoft.com> wrote in message
> news:68305293-61F5-4C18-94E7-664148BB361D@.microsoft.com...
>
>

Copy databases from SQL 2005 server to SQL Express

Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.

Is it possible to copy databases from SQL 2005 to SQL Express?

Thanks.Thanks (Thanks@.work.com) writes:

Quote:

Originally Posted by

Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.
>
Is it possible to copy databases from SQL 2005 to SQL Express?

Yes, but I don't think you can use the Copy Database Wizard, but you will
have to do it by hand. Which is not very difficult anyway.

On source machine backup database, and make note of the logical
filenames. (You see these with sp_helpdb).

On target machine to do:

RESTORE db FROM DISK = 'pathgoeshere'
WITH MOVE 'logicalfilename1'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf',
WITH MOVE 'logicalfilename2' TO
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf',
REPLACE

I've used MSSQL.2 for the target, but you use the number for your
Express instance. You find out which is which by looking around in
the directories.

This does not migrate logins, and mappings user and logins are likely
to be out of whack. You can use sp_change_users_login to fix.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks.

Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express

Is it possible to connect the SQL Express in B from the A using the SQL
2005?

I tried it, but failed.

If I can do this, that means I can use Import function in SQL 2005 to import
Access databases into SQL Express.|||Am_I_right (Am_I_right@.world.com) writes:

Quote:

Originally Posted by

Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express
>
Is it possible to connect the SQL Express in B from the A using the SQL
2005?
>
I tried it, but failed.

How did you try? How did it fail?

You can always set up a linked server, but it's unclear to me if that
is what you tried.

Quote:

Originally Posted by

If I can do this, that means I can use Import function in SQL 2005 to
import Access databases into SQL Express.

No, that is what you can do with a linked server. You could however
import you Access database into SQL 2005 non-Express, and then
copy the database with backup/restore as I described in my previous
post.

Quote:

Originally Posted by

Am_I_right (Am_I_right@.world.com) writes:

Quote:

Originally Posted by

>Same system setup as before.
>Computer A with SQL 2005
>Computer B with SQL Express
>>
>Is it possible to connect the SQL Express in B from the A using the SQL
>2005?
>>
>I tried it, but failed.

>
How did you try? How did it fail?
>

Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.
Thanks.

Quote:

Originally Posted by

You can always set up a linked server, but it's unclear to me if that
is what you tried.
>

Quote:

Originally Posted by

>If I can do this, that means I can use Import function in SQL 2005 to
>import Access databases into SQL Express.

>
No, that is what you can do with a linked server. You could however
import you Access database into SQL 2005 non-Express, and then
copy the database with backup/restore as I described in my previous
post.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(Thanks@.work.com) writes:

Quote:

Originally Posted by

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9874F08E810F0Yazorman@.127.0.0.1...

Quote:

Originally Posted by

>Am_I_right (Am_I_right@.world.com) writes:

Quote:

Originally Posted by

>>Same system setup as before.
>>Computer A with SQL 2005
>>Computer B with SQL Express
>>>
>>Is it possible to connect the SQL Express in B from the A using the SQL
>>2005?
>>>
>>I tried it, but failed.

>>
>How did you try? How did it fail?

>
Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.

Just because you connect the computers with a network cables does not
mean that the two SQL Servers are connected. You must also set up a
linked server. Although it depends on what you want to do. Linked servers
are needed only if you want to access tables on server from the other.

Set up SQL sever is ok, import Access to SQL is also oK for me.
but couldn't figure out how to import Accees into SQL Express.
either directly or through another SQL 2005 server.

Next I am going to try import Access into MySQL by using Premiumsoft
Navicat.
If that work, I will decide to use MySQL, because it is open source and
lower cost.

Copy databases

Hello all,
I have a unique situation on my hands, and I need a bit of advice. I
currently use transaction-log based backups on my SQL server through
Symantec BackupExec for data protection / disaster recovery purposes.
However, I need a copy of my SQL data available for my development and
support people.
What I need is a way to copy the data from my production server that
does NOT truncate the transaction log, but does not require that my
support people restore from a full backup file, and then apply log
backup files to get a "current" database. As of now, I have written
a .Net app that allows them to restore files created with the "BACKUP
DATABASE" command, but clearly that will not work going forward.
Is there a good way to copy databases from one server to another
server in this situation? I don't necessarily care about syncing the
user IDs on my test machine, because end users have no visibility into
the testing area. If this process works, I would also like to use it
when updating training databases (copies of prod databases kept on the
same SQL instance).
Any help here is GREATLY appreciated!
Tom:
Have you considered some form of replication: either transactional or
snapshot?
HTH
Paul
"tom@.drdabbles.us" wrote:

> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
>
|||Replication would not be appropriate, since we want a point-in-time
copy of the data and not a constantly refreshed copy.
|||Tom wrote:
> Replication would not be appropriate, since we want a point-in-time
> copy of the data and not a constantly refreshed copy.
Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
<database> WITH COPY_ONLY.
|||> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log
BACKUP DATABASE. The only type of backup that truncates the log is BACKUP LOG.
I agree with Jeffrey that you check out COPY_ONLY. This isn't needed in your situation, but it makes
the intent of that backup a bit more clear. (COPY_ONLY for BACKUP DATABASE will not affect
differential backups - has no effect on log backups).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<tom@.drdabbles.us> wrote in message
news:fd8f3a92-c082-4e21-b366-0e5079265554@.b1g2000hsg.googlegroups.com...
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
|||Isnt there a copy db task in SSIS?
MC
"Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Tom wrote:
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
|||Watch out so Erland don't see this! ;-)
I haven't used it, but I do know that among others, Erland have issues with this. It can run in two
modes where one does detach and attach (the only mode available for 2000).
The other mode does some object scripting and then transfer the data. I believe this is where most
of the problems are.
SSIS also have some options to transfer server level objects, like logins, jobs etc. This can be
fine, but unfortunately for logins the SID isn't the same on dest server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
> Isnt there a copy db task in SSIS?
>
> MC
>
> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>
|||Ahhh okay. It actually worked for me, but it seems i was lucky. Anyway,
Erland DONT read this ;)
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uED7OdtmIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Watch out so Erland don't see this! ;-)
> I haven't used it, but I do know that among others, Erland have issues
> with this. It can run in two modes where one does detach and attach (the
> only mode available for 2000).
> The other mode does some object scripting and then transfer the data. I
> believe this is where most of the problems are.
> SSIS also have some options to transfer server level objects, like logins,
> jobs etc. This can be fine, but unfortunately for logins the SID isn't the
> same on dest server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
>
|||On Apr 10, 1:08Xam, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> Are you on SQL Server 2005? XIf so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!
|||<<While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out.>>
What a weird design. BE cripples the product and usability, for no gain. Glad that COPY_ONLY solves
this for you, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom" <tom@.drdabbles.us> wrote in message
news:d8bee064-e931-49f5-a3d8-e329f96e6c04@.u69g2000hse.googlegroups.com...
On Apr 10, 1:08 am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!

copy databases

Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W.See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Per W." <fungererikke@.ikkegyldig.no.invalid> wrote in message
news:e6udnk$95i$1@.troll.powertech.no...
Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W.|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i melding
news:uRVRAIVkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
>
No, sorry. I have 2 SBS servers, one 2000 that i will move the database over
to the new 2003, and since both servers are up and running i cant do this,
and i need something to copy all databases since the databases are on
different folders on the harddisk then ther is a lot of job copying the
files manualy. I cant understand why MS have made restrictions on the copy
options in the enterprise manager since this is a situation where it will be
realy good to use.
/Per W.

Copy databases

Hello all,
I have a unique situation on my hands, and I need a bit of advice. I
currently use transaction-log based backups on my SQL server through
Symantec BackupExec for data protection / disaster recovery purposes.
However, I need a copy of my SQL data available for my development and
support people.
What I need is a way to copy the data from my production server that
does NOT truncate the transaction log, but does not require that my
support people restore from a full backup file, and then apply log
backup files to get a "current" database. As of now, I have written
a .Net app that allows them to restore files created with the "BACKUP
DATABASE" command, but clearly that will not work going forward.
Is there a good way to copy databases from one server to another
server in this situation? I don't necessarily care about syncing the
user IDs on my test machine, because end users have no visibility into
the testing area. If this process works, I would also like to use it
when updating training databases (copies of prod databases kept on the
same SQL instance).
Any help here is GREATLY appreciated!Tom:
Have you considered some form of replication: either transactional or
snapshot?
HTH
Paul
"tom@.drdabbles.us" wrote:
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!
>|||Replication would not be appropriate, since we want a point-in-time
copy of the data and not a constantly refreshed copy.|||Tom wrote:
> Replication would not be appropriate, since we want a point-in-time
> copy of the data and not a constantly refreshed copy.
Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
<database> WITH COPY_ONLY.|||> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log
BACKUP DATABASE. The only type of backup that truncates the log is BACKUP LOG.
I agree with Jeffrey that you check out COPY_ONLY. This isn't needed in your situation, but it makes
the intent of that backup a bit more clear. (COPY_ONLY for BACKUP DATABASE will not affect
differential backups - has no effect on log backups).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<tom@.drdabbles.us> wrote in message
news:fd8f3a92-c082-4e21-b366-0e5079265554@.b1g2000hsg.googlegroups.com...
> Hello all,
> I have a unique situation on my hands, and I need a bit of advice. I
> currently use transaction-log based backups on my SQL server through
> Symantec BackupExec for data protection / disaster recovery purposes.
> However, I need a copy of my SQL data available for my development and
> support people.
> What I need is a way to copy the data from my production server that
> does NOT truncate the transaction log, but does not require that my
> support people restore from a full backup file, and then apply log
> backup files to get a "current" database. As of now, I have written
> a .Net app that allows them to restore files created with the "BACKUP
> DATABASE" command, but clearly that will not work going forward.
> Is there a good way to copy databases from one server to another
> server in this situation? I don't necessarily care about syncing the
> user IDs on my test machine, because end users have no visibility into
> the testing area. If this process works, I would also like to use it
> when updating training databases (copies of prod databases kept on the
> same SQL instance).
> Any help here is GREATLY appreciated!|||Isnt there a copy db task in SSIS?
MC
"Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Tom wrote:
>> Replication would not be appropriate, since we want a point-in-time
>> copy of the data and not a constantly refreshed copy.
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.|||Watch out so Erland don't see this! ;-)
I haven't used it, but I do know that among others, Erland have issues with this. It can run in two
modes where one does detach and attach (the only mode available for 2000).
The other mode does some object scripting and then transfer the data. I believe this is where most
of the problems are.
SSIS also have some options to transfer server level objects, like logins, jobs etc. This can be
fine, but unfortunately for logins the SID isn't the same on dest server...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
> Isnt there a copy db task in SSIS?
>
> MC
>
> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Tom wrote:
>> Replication would not be appropriate, since we want a point-in-time
>> copy of the data and not a constantly refreshed copy.
>> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE <database> WITH COPY_ONLY.
>|||Ahhh okay. It actually worked for me, but it seems i was lucky. Anyway,
Erland DONT read this ;)
MC
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uED7OdtmIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Watch out so Erland don't see this! ;-)
> I haven't used it, but I do know that among others, Erland have issues
> with this. It can run in two modes where one does detach and attach (the
> only mode available for 2000).
> The other mode does some object scripting and then transfer the data. I
> believe this is where most of the problems are.
> SSIS also have some options to transfer server level objects, like logins,
> jobs etc. This can be fine, but unfortunately for logins the SID isn't the
> same on dest server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:8900F35F-E714-4D73-902B-2104E8F950A8@.microsoft.com...
>> Isnt there a copy db task in SSIS?
>>
>> MC
>>
>> "Jeffrey Williams" <jeff.williams3188@.verizon.ent> wrote in message
>> news:ekxOOksmIHA.5280@.TK2MSFTNGP02.phx.gbl...
>> Tom wrote:
>> Replication would not be appropriate, since we want a point-in-time
>> copy of the data and not a constantly refreshed copy.
>> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
>> <database> WITH COPY_ONLY.
>|||On Apr 10, 1:08=A0am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> > Replication would not be appropriate, since we want a point-in-time
> > copy of the data and not a constantly refreshed copy.
> Are you on SQL Server 2005? =A0If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!|||<<While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out.>>
What a weird design. BE cripples the product and usability, for no gain. Glad that COPY_ONLY solves
this for you, though!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom" <tom@.drdabbles.us> wrote in message
news:d8bee064-e931-49f5-a3d8-e329f96e6c04@.u69g2000hse.googlegroups.com...
On Apr 10, 1:08 am, Jeffrey Williams <jeff.williams3...@.verizon.ent>
wrote:
> Tom wrote:
> > Replication would not be appropriate, since we want a point-in-time
> > copy of the data and not a constantly refreshed copy.
> Are you on SQL Server 2005? If so, take a look at BACKUP DATABASE
> <database> WITH COPY_ONLY.
Jeffrey,
This was EXACTLY what I needed. I somehow missed the section in the
docs on MSDN that gave the description of this option. As it turns
out, this actually is important. While the "full" backup SQL does
normally may not truncate the logs, it does mark BackupExec as not
owning the last full backup that was done. No matter what method you
use for log-based backups, if that specific method does not own the
last full backup, the product will freak out. This is also true of log-
based backups using BACKUP from within T-SQL code.
Thanks again!!

Copy databases

Hi,
Maybe a stupid question, but I'm trying to copy some databases from one server to another. The copy databases wizard says the job is successful and I can see that the job has been done on the remote server. But the copied databases are not there. What do I do?
Anyone?|||Make sure you connected (during the copy) to the right (the same as you have inspected later)instance of SQL Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||How do you know if the databases are not there? do select * from sys.databases and see if they are there.|||Aren't they supposed to show up in the database tree in Object explorer? I can't see them...only the ones that I exported, but copying is supposed to be a better method.
|||

they are but you need to refresh the tree, by running the query I asked you to I was asking to check if they were really there.

Are the databases you are looking for in the resultset from the query I asked you to run?

|||I did and no they are not there.
|||Hmmm very strange, my only suggestion is to try again and look for errors.|||'Execute SQL Server Agent job' generates an error and the log says 'Login failed for user'.
|||So I think some Agent debugging might be in order then to make sure it is working ok, Management Tools Group folks can help, then come back.sql sql

copy databases

See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Per W." <fungererikke@.ikkegyldig.no.invalid> wrote in message
news:e6udnk$95i$1@.troll.powertech.no...
Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W."Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i melding
news:uRVRAIVkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
>
No, sorry. I have 2 SBS servers, one 2000 that i will move the database over
to the new 2003, and since both servers are up and running i cant do this,
and i need something to copy all databases since the databases are on
different folders on the harddisk then ther is a lot of job copying the
files manualy. I cant understand why MS have made restrictions on the copy
options in the enterprise manager since this is a situation where it will be
realy good to use.
/Per W.|||Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W.|||See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Per W." <fungererikke@.ikkegyldig.no.invalid> wrote in message
news:e6udnk$95i$1@.troll.powertech.no...
Hi, how can i copy databases from one server in one domain to another
server. Since the servers isnt in the same domain its not possible to do
with the copy in enterprise manager (even with same username and password on
Administrator). Is there som 3de party program to use for this? the customer
has nearly 100 databases and to manualy detach, copy and attach then is a
hell of a job.
/Per W.|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> skrev i melding
news:uRVRAIVkGHA.5036@.TK2MSFTNGP04.phx.gbl...
> See if this helps: http://vyaskn.tripod.com/moving_sql_server.htm
>
No, sorry. I have 2 SBS servers, one 2000 that i will move the database over
to the new 2003, and since both servers are up and running i cant do this,
and i need something to copy all databases since the databases are on
different folders on the harddisk then ther is a lot of job copying the
files manualy. I cant understand why MS have made restrictions on the copy
options in the enterprise manager since this is a situation where it will be
realy good to use.
/Per W.

Thursday, March 22, 2012

Copy Database Wizard never finishes

I am trying to use the Copy Database Wizard to copy about 240 databases on
Server1 with With SQL Server 2000 to Server2 with SQL Server 2005 64 bit.
The Copy Database Wizard starts and is successfull with the first 4 steps
but the last 5th step never finishes. I have tried moving all of the
databases at the same time. Or Just moving one small database and the Wizard
never finishes with no errors.
Any ideas, am I going about it the wrong way?
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgHi
It may be easier to backup/restore?
John
"Marco Napoli" <marco@.avantitecnospam.com> wrote in message
news:OKcxeclCGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I am trying to use the Copy Database Wizard to copy about 240 databases on
>Server1 with With SQL Server 2000 to Server2 with SQL Server 2005 64 bit.
> The Copy Database Wizard starts and is successfull with the first 4 steps
> but the last 5th step never finishes. I have tried moving all of the
> databases at the same time. Or Just moving one small database and the
> Wizard never finishes with no errors.
> Any ideas, am I going about it the wrong way?
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>|||Don't have that environment to test it or give you specific advice on.
But, you can also move databases from 2000 to 2005 by backup/restore and
detach/attach. BTW, once in 2005, there is no going back to 2000 with a
database as 2005 will automatically upgrade the system tables, etc.
Just a thought,
Joe
"Marco Napoli" wrote:

> I am trying to use the Copy Database Wizard to copy about 240 databases on
> Server1 with With SQL Server 2000 to Server2 with SQL Server 2005 64 bit.
> The Copy Database Wizard starts and is successfull with the first 4 steps
> but the last 5th step never finishes. I have tried moving all of the
> databases at the same time. Or Just moving one small database and the Wiza
rd
> never finishes with no errors.
> Any ideas, am I going about it the wrong way?
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>
>|||Marco Napoli (marco@.avantitecnospam.com) writes:
> I am trying to use the Copy Database Wizard to copy about 240 databases on
> Server1 with With SQL Server 2000 to Server2 with SQL Server 2005 64 bit.
> The Copy Database Wizard starts and is successfull with the first 4
> steps but the last 5th step never finishes. I have tried moving all of
> the databases at the same time. Or Just moving one small database and
> the Wizard never finishes with no errors.
Are you using the CDW in SQL 2005? In such case, are you using the
attach/detach method or the SMO method? (I would recommend staying away
from the latter. I filed many bugs for it during the beta programme,
and not all have been resolved.)
As the others have suggested backup/restore may an easier way to go,
rather than relying a on a wizard that you don't know exactly what it's
doing. The one thing you will have to do manually, if you use backup/restore
is to clean up the user-login mapping on the new server.
sp_change_users_login is good for that. (Whether the CDW handles this,
I don't know, but I would expect it to.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have tried using the CDW using the attache/detach and the SMO method. Both
gave me the same results, the wizard keeps going.
I am not opposed to do a backup/restore but I am trying to figure out how to
do all of the 240 database in one night. The server can only be down from
10:00 pm until morning. I have not figured out an automated way to restore
all db's from backup and recreate all logins for each db.
Thanks.
Marco
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9739481E99ADYazorman@.127.0.0.1...
> Marco Napoli (marco@.avantitecnospam.com) writes:
> Are you using the CDW in SQL 2005? In such case, are you using the
> attach/detach method or the SMO method? (I would recommend staying away
> from the latter. I filed many bugs for it during the beta programme,
> and not all have been resolved.)
> As the others have suggested backup/restore may an easier way to go,
> rather than relying a on a wizard that you don't know exactly what it's
> doing. The one thing you will have to do manually, if you use
> backup/restore
> is to clean up the user-login mapping on the new server.
> sp_change_users_login is good for that. (Whether the CDW handles this,
> I don't know, but I would expect it to.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Marco
This sounds like you may be burning your bridges a bit! Do you need to move
all databases at once? Is your time window applicable at w

ends? Can
existing backup jobs be utilised?
A trial run should give you an idea of timings and will also help you plan
the migration.
Have you checked that the code for your stored procedures/views/functions
are still compatible?
For logins check out:
http://support.microsoft.com/kb/246...122120121120120
John
"Marco Napoli" <marco@.avantitecnospam.com> wrote in message
news:uFlqmVnCGHA.900@.TK2MSFTNGP10.phx.gbl...
>I have tried using the CDW using the attache/detach and the SMO method.
>Both gave me the same results, the wizard keeps going.
> I am not opposed to do a backup/restore but I am trying to figure out how
> to do all of the 240 database in one night. The server can only be down
> from 10:00 pm until morning. I have not figured out an automated way to
> restore all db's from backup and recreate all logins for each db.
>
> Thanks.
> Marco
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9739481E99ADYazorman@.127.0.0.1...
>|||Marco Napoli wrote:
> The Copy Database Wizard starts and is successfull with the first 4 steps
> but the last 5th step never finishes. I have tried moving all of the
> databases at the same time. Or Just moving one small database and the Wiza
rd
> never finishes with no errors.
How long have you waited for it to finish?
Austin|||Marco Napoli (marco@.avantitecnospam.com) writes:
> I have tried using the CDW using the attache/detach and the SMO method.
> Both gave me the same results, the wizard keeps going.
> I am not opposed to do a backup/restore but I am trying to figure out
> how to do all of the 240 database in one night. The server can only be
> down from 10:00 pm until morning. I have not figured out an automated
> way to restore all db's from backup and recreate all logins for each
> db.
Are you doing this for a final migration or for testing only?
As long as you are testing, there is no need to take the server down
for backup/restore.
Are the user SQL logins, or are they only Windows logins? I made a quick
test, and it seems that for Windows logins the SID is the same (at least
if the servers are in the same domain), in which case there will not be
any mapping problem.
For SQL logins it's more complicated, but make a test with a database or
two, and see where sp_change_users_login takes you.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I have waited about 4 hours to move one database. Then I tried with another
real small database and it still never finished. Its weird...
Marco
"AustinMN" <tacooper260@.hotmail.com> wrote in message
news:1135706528.891088.247840@.f14g2000cwb.googlegroups.com...
> Marco Napoli wrote:
> How long have you waited for it to finish?
> Austin
>|||In the past when we have moved from one SQL Server 2000 to another box, we
had to do it in the same night after 10:00pm because of the users in the
West Coast. We spoke about giving a notice to our users that we will have
the site down for the w

end, so it is an option.
Currently I am trying to do a trial run with just copying one database at a
time but the Copy Database Wizard is not finishing. I wonder if anyone else
has better success. I accept all default options except for trying both ways
detach/attach and leaving db online.
Yes we have tested and the SQL 2000 code is working in SQL Server 2005.
Thanks
Marco
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ecqvtetCGHA.2840@.TK2MSFTNGP12.phx.gbl...
> Hi Marco
> This sounds like you may be burning your bridges a bit! Do you need to
> move all databases at once? Is your time window applicable at w

ends?
> Can existing backup jobs be utilised?
> A trial run should give you an idea of timings and will also help you plan
> the migration.
> Have you checked that the code for your stored procedures/views/functions
> are still compatible?
> For logins check out:
> http://support.microsoft.com/kb/246...122120121120120
> John
> "Marco Napoli" <marco@.avantitecnospam.com> wrote in message
> news:uFlqmVnCGHA.900@.TK2MSFTNGP10.phx.gbl...
>

Copy Database Wizard needs what?

I am trying to launch Copy database wizard from SS2005 SP2 Developer Ed. (on Windows XP SP2) to get databases from SS2005 SP1 Enterprise Ed. (on Windows 2003 Server Enterprise Ed.)

The same collation, default language, SQL Server Agent running on both

I use Windows Authentication with the same sysadmin, part of Administrators on both machines, with the same password. It is in workgroup

I am getting various errors from various attempts.

Ошибка DCOM "Указанная служба не может быть запущена, поскольку она отключена или все связанные с ней устройства отключены. " при попытке запуска службы MsDtsServer с аргументами "" для запуска сервера:

{ABF05265-635E-44B0-A28F-AEA45247ACA0}
(i.e. service MsDtsServer with arguments is switched off)

Таймаут (30000 мс) ожидания для ответа при транзакции от службы clr_optimization_v2.0.50727_32.

Copy Database Wizard shows "The wizard will create a Integration Services package with the properties yo specify below". And it is not enabled on remote SS2005 SP1

So, my questions are what exactly should I have and where in order to run CopyDatabase Wizard to get things from remote server:

1)
Is it possible to have Integration Services running only at one of the SS2005? from which I run the wizard

Should CLR integration be enabled? only at one of the SS2005 (from which I ran wizard)?

3)MsDts Services? only at one of the SS2005 (from which I run wizard)?

What else? firewall, etc.

You need the Integration Services Service to be running on the destination machine and also SQL Agent if you are scheduling the package to run.

You do not need SQLCLR.

The firewall needs to to support the SQL Server and Integration Services ports (you need to look up IS but SQL Server defaults to 1433)

|||

Are you trying port the SSIS package on any of the client machine.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm and ensure all the XML configuration files are copied across teh server & client.

Also http://aspalliance.com/articleviewer.aspx?aId=947a good run on DTS - SSIS understanding.

|||

No, I do not have SSIS packages on source machine and I do not intend to port any.

As I understood, the Copy Database Wizard always ends up with
"The wizard will create a Integration Services package with the properties you specify below
Package name:
"

And why do I get MSDtsServer error?
Can it be connected with the mirroring on source server?

|||Have you attempted Service pack 2 for SQL SErver?|||

Now I have both with SP2 (90.3054) with postSP2 hotfix
http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
http://support.microsoft.com/?kbid=934458

(BTW it is very confusing that 3159, as I understood, is the same as 3054?)

I manage to run it between different instances on the same machine but not from remote one.

Both machines is in workgroup, I use Windows Auth with the same login fro
I use SQL Management Object method
I mark one db to copy to a new one at destination. It is just to pass forward (CDW doesn’t advance with no databases marked) to screen offering me to copy:
- Stored procedures from master database
- SQL Server Agent jobs
- User-defined error messages
- Endpoints
- SSIS packages
- Logins

I choose to copy only Logins(stored procedures from master db. I tried other options too, but this one is the most innocuous from all)

Integration Services Proxy account: SQL Service Account . Run immediately

I cannot change it or have any other choice

Performing operation…
- Add log for package Success
- Add task for transferring database objects Success
- Create package Success
- Start SQL Server Agent Job Error

TITLE: Copy Database Wizard

The job failed. Check the event log on the destination server for details.

Event log shows:

Package "CDW_<source_server>_<target_server>_<target_instance_name>_6" failed.

Дополнительные сведения можно найти в центре справки и поддержки, в http://go.microsoft.com/fwlink/events.asp.

Eveent Log shows various erros:
1)

Package "target_server>_<target_instance_name>_6" failed.

Дополнительные сведения можно найти в центре справки и поддержки, в http://go.microsoft.com/fwlink/events.asp.
2)

Event Name: OnWarning

Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_SERVER310_CELLO_C310_4

Source ID: {037E2C87-3F49-432C-8D67-04485FCD0C7D}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: -2147381246

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

Event Name: OnTaskFailed

Message:

Operator: NT AUTHORITY\SYSTEM

Source Name: SERVER310_CELLO_C310_Transfer Objects Task

Source ID: {DAF81427-B701-4329-BAB2-CFDB1B573E25}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: 0

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

4)
Event Name: OnError

Message: Failed to connect to server SERVER310.

StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)

InnerException-->Login failed for user 'NT AUTHORITY\АНОНИМНЫЙ ВХОД'.

StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_SERVER310_CELLO_C310_4

Source ID: {037E2C87-3F49-432C-8D67-04485FCD0C7D}

Execution ID: {FFDBBCE1-6BA4-46C3-9257-8F5DA91F44E4}

Start Time: 30.05.2007 12:14:54

End Time: 30.05.2007 12:14:54

Data Code: 0

Дополнительные сведения можно найти в центре справки и поддержки, в "http://go.microsoft.com/fwlink/events.asp".

5)
Event Name: OnError