On what basis is the sql job's step linked to a database?



  • I have couple of SQL jobs on source sql server, and on the job step a particular database is selected.

    I want to move the databases and jobs on a new sql server, so I have scripted the jobs.

    In the script I can see the database name. So is it correct to conclude that the sql job is related to the database based on the database name and not something else like db id?



  • It is not possible to be 100% certain that a database is used by looking at the job step level in the msdb system tables for jobs. Some examples:

    • A job can include SQL code that does USE dbname.
    • A job can SELECT from a view, which in turn reads data from your database.
    • A job can execute a stored procedure that has code that references tables in your database.
    • A job can execute an SSIS package that does stuff against your database.

    This is why it is so important that we educate those that creates jobs to comment each job!




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2