none
SSMS Copy database task RRS feed

  • Question

  • Hi,

    In SQL 2008 r2 I just used the Copy Database task to move a DB to another server.

    I chose to run the task on a scheduled job. I now cant find the created job anywhere. I really need to delete it.

    Can anybody help,

    Regards,

    Ben


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, April 22, 2015 10:10 AM

Answers

  • Did you give any name for schedule? If not I doubt it created a job. Also run this on source and target

    USE msdb
    GO
    EXEC dbo.sp_help_job

    This will list all jobs on the server

    You can also list all the package by running below command to be sure no package was created by wizard:

    select s.name,l.name from msdb..sysssispackages s left join master.sys.syslogins l on s.ownersid = l.sid

    By default it create package name starting with CDW_

    Then you can compare if job exists or not.


    • Edited by dave_gona Wednesday, April 22, 2015 10:50 AM
    • Marked as answer by Mr Shaw Wednesday, April 22, 2015 11:13 AM
    Wednesday, April 22, 2015 10:49 AM
  • First question, did you schedule the task or did you carry out the import/export straight away?  If you carried it out straight away then SQL Server will not have saved the task as an SSIS Package and it will not have created a SQL Server Agent Job.

    Did you Import or export the database?  If you imported look on your destination Server, if you exported look on your source Server.

    It is also possible that the Job is there but you might not recognise the name; if you know the date when you created the package then you can interrogate sysjobs in msdb: -

    select

    * from sysjobs where date_created=[your created date]


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Wednesday, April 22, 2015 11:06 AM
    • Marked as answer by Mr Shaw Wednesday, April 22, 2015 11:15 AM
    Wednesday, April 22, 2015 11:01 AM

All replies

  • Just expand SQL Server Agent -> jobs and it will list you available jobs (I hope you've access to msdb database for your login)

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 22, 2015 10:30 AM
  • When I expand the SQL Server Agent on the source server it does not list my job

    I can manually add and delete Agent jobs...so i am guessing i have the correct permissions.

    Maybe copy database task never created a job?


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, April 22, 2015 10:40 AM
  • When I expand the SQL Server Agent on the source server it does not list my job

    I can manually add and delete Agent jobs...so i am guessing i have the correct permissions.

    Maybe copy database task never created a job?


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Yes

    Or the target server was different?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 22, 2015 10:42 AM
  • Did you give any name for schedule? If not I doubt it created a job. Also run this on source and target

    USE msdb
    GO
    EXEC dbo.sp_help_job

    This will list all jobs on the server

    You can also list all the package by running below command to be sure no package was created by wizard:

    select s.name,l.name from msdb..sysssispackages s left join master.sys.syslogins l on s.ownersid = l.sid

    By default it create package name starting with CDW_

    Then you can compare if job exists or not.


    • Edited by dave_gona Wednesday, April 22, 2015 10:50 AM
    • Marked as answer by Mr Shaw Wednesday, April 22, 2015 11:13 AM
    Wednesday, April 22, 2015 10:49 AM
  • First question, did you schedule the task or did you carry out the import/export straight away?  If you carried it out straight away then SQL Server will not have saved the task as an SSIS Package and it will not have created a SQL Server Agent Job.

    Did you Import or export the database?  If you imported look on your destination Server, if you exported look on your source Server.

    It is also possible that the Job is there but you might not recognise the name; if you know the date when you created the package then you can interrogate sysjobs in msdb: -

    select

    * from sysjobs where date_created=[your created date]


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Wednesday, April 22, 2015 11:06 AM
    • Marked as answer by Mr Shaw Wednesday, April 22, 2015 11:15 AM
    Wednesday, April 22, 2015 11:01 AM
  • Thanks dave_gona !

    I have found the jobs on my destinatin server....

    They have default names begining with CDW_


    Mr Shaw... One day I might know a thing or two about SQL Server!

    Wednesday, April 22, 2015 11:14 AM