Creating a test environment

I just got my first job doing some DBA work (junior level) in a small company using SQL Server 2000.

I would like to make a copy of the production DB and use it for development of SP's, DTS packages and views.

What is the safest way for me to copy the entire production DB, to a new DB on the same server?

Should I create a new DB, and do a restore of the production backup?

Any help would be appreciated,
Thank you.restoring from a backup is probably the most common way.|||restoring from a backup is probably the most common way.

Should I restore to a 'new database' I create, or restore and just supply the name of 'development', for example...

Thanks for the reply|||Should I restore to a 'new database' I create, or restore and just supply the name of 'development', for example...

Thanks for the reply

you can try restoring the backup via Query Analyzer. Try consulting BOL for the syntax. Based on my experience it creates the database along with the devices. You can also specify that it move it's devices to a specific path.

Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1

Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1

Creating a job with many steps including DTS

Hi,
(using Windows 2000 Server/SQL2K Ent Ed SP3a)
I am looking to create a job with a number of steps inside it. I need
the job to run on a sunday afternoon when our OLTP system is at our
quietest in terms of user connections and throughput. The job purpose
is to copy data (24,000,000 rows) from one table to another on the same
database, via a DTS package. I then need to rename the old table,
removing the triggers and views on it, then rename the new table, and
add the relevent objects (including indexs and constraints) then add
the permissions on it.
These are the steps:
1) Set restricted user mode on database
2) Run the DTS package
3) Remove triggers and views on old table
4) Rename old table
5) Rename new table
6) Add indexs and constraints to new table
7) Add triggers/views to new table
8) Set permissions to new table
9) Set multi user mode on database
My question would be does the above seem logical, or would there be a
better way of performing this process? Do I need the DTS package, would
a INSERT INTO be better, or could I use a DTS package for the entire
process?
Thanks and rgds,
qhHi
INSERT..SELECT may be a better option.
You may want to try something like the following to avoid the rename steps:
Create Archive Table
Insert Into Archive Table
Delete Archived Data from Live Table
ReIndex Live Table
You may also be able to do this in smaller increments each night instead of
once a w.
John
"Scott" wrote:

> Hi,
> (using Windows 2000 Server/SQL2K Ent Ed SP3a)
> I am looking to create a job with a number of steps inside it. I need
> the job to run on a sunday afternoon when our OLTP system is at our
> quietest in terms of user connections and throughput. The job purpose
> is to copy data (24,000,000 rows) from one table to another on the same
> database, via a DTS package. I then need to rename the old table,
> removing the triggers and views on it, then rename the new table, and
> add the relevent objects (including indexs and constraints) then add
> the permissions on it.
> These are the steps:
> 1) Set restricted user mode on database
> 2) Run the DTS package
> 3) Remove triggers and views on old table
> 4) Rename old table
> 5) Rename new table
> 6) Add indexs and constraints to new table
> 7) Add triggers/views to new table
> 8) Set permissions to new table
> 9) Set multi user mode on database
>
> My question would be does the above seem logical, or would there be a
> better way of performing this process? Do I need the DTS package, would
> a INSERT INTO be better, or could I use a DTS package for the entire
> process?
> Thanks and rgds,
> qh
>

Creating a job from a trigger

Hello, All
I have a trigger on a table, which when called, creates a job to run in n
minutes. The trigger runs fine and the job is created, however with a small
issue.
The username that is used as the Job Owner, ends up to be the name of the
user that triggered the trigger eventhough the code that creates the job
specifies a different name.
What am I doing wrong? How can I resolve this?
Here is the SP that is run in the trigger:
exec msdb..sp_add_job @.job_name = 'TEST',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 0,
@.delete_level = 1
The code above says user SA but the job ends up running as TESTUSER and it
makes the job fail because a that user is not a SA nor able to run a CMD
job.
Please help!
Thank you.slamm wrote:
> Hello, All
> I have a trigger on a table, which when called, creates a job to run
> in n minutes. The trigger runs fine and the job is created, however
> with a small issue.
> The username that is used as the Job Owner, ends up to be the name of
> the user that triggered the trigger eventhough the code that creates
> the job specifies a different name.
> What am I doing wrong? How can I resolve this?
> Here is the SP that is run in the trigger:
> exec msdb..sp_add_job @.job_name = 'TEST',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 0,
> @.delete_level = 1
> The code above says user SA but the job ends up running as TESTUSER
> and it makes the job fail because a that user is not a SA nor able to
> run a CMD job.
> Please help!
> Thank you.
I don't think that's going to work. If it did, then any user who had job
creation rights could alias a job as the system administrator, giving
them more rights in the process. You can grant the user rights to do
what needs to run or you can do the following:
Instead of creating a job directly from the trigger, insert the
necessary job criteria into a custom table of application jobs (to be
created). Create a recurring job on the server that monitors this table
at specified intervals and creates the jobs itself. Then you don't even
have to grant users job creation rights. They only need rights to your
application job table.
David Gugick
Imceda Software
www.imceda.com

Creating a job for SQL 2000 using SQLDMO in c#

I have a quick question on how one can create a backup job using SQLDMO in C# I have not really been able to find any information on how to do this. I can create backups using SQLDMO and I can create Jobs but I do not know how to bind the two together. So if anyone has any information on how to do this it would be great.I can write a little sample for you if needed. But before I do, can you let me know whether doing this with SMO is an option for you? This is the object model that replaces DMO for SQL Server 2000 and allows you to work with SQL Server 2000 as well.|||sure, I would love to see the example with SMO|||

Sir,

Could you please send me the sample code that you mentioned in this post. I need to write an application than monitors the jobs running on SQL server.

Thanks alot

Ruchi

|||

Two weeks ago I posted a 3-part entry in my blog on building alert-based transaction log backups using SMO. The parts involve building a stored procedure to perform the backup, creating a job to run the stored procedure, and creating a performance-based alert to run the job.

The first entry is here: http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/08/29/23049.aspx

Hopefully that'll help.

|||

I'm writing a similar app using SQLDMO. Could you please give me sample code of how did you combine the two?

Thanks in advance.

mct.

Creating a job for SQL 2000 using SQLDMO in c#

I have a quick question on how one can create a backup job using SQLDMO in C# I have not really been able to find any information on how to do this. I can create backups using SQLDMO and I can create Jobs but I do not know how to bind the two together. So if anyone has any information on how to do this it would be great.I can write a little sample for you if needed. But before I do, can you let me know whether doing this with SMO is an option for you? This is the object model that replaces DMO for SQL Server 2000 and allows you to work with SQL Server 2000 as well.|||sure, I would love to see the example with SMO|||

Sir,

Could you please send me the sample code that you mentioned in this post. I need to write an application than monitors the jobs running on SQL server.

Thanks alot

Ruchi

|||

Two weeks ago I posted a 3-part entry in my blog on building alert-based transaction log backups using SMO. The parts involve building a stored procedure to perform the backup, creating a job to run the stored procedure, and creating a performance-based alert to run the job.

The first entry is here: http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/08/29/23049.aspx

Hopefully that'll help.

|||

I'm writing a similar app using SQLDMO. Could you please give me sample code of how did you combine the two?

Thanks in advance.

mct.

Creating a job for SQL 2000 using SQLDMO in c#

I have a quick question on how one can create a backup job using SQLDMO in C# I have not really been able to find any information on how to do this. I can create backups using SQLDMO and I can create Jobs but I do not know how to bind the two together. So if anyone has any information on how to do this it would be great.I can write a little sample for you if needed. But before I do, can you let me know whether doing this with SMO is an option for you? This is the object model that replaces DMO for SQL Server 2000 and allows you to work with SQL Server 2000 as well.|||sure, I would love to see the example with SMO|||

Sir,

Could you please send me the sample code that you mentioned in this post. I need to write an application than monitors the jobs running on SQL server.

Thanks alot

Ruchi

|||

Two weeks ago I posted a 3-part entry in my blog on building alert-based transaction log backups using SMO. The parts involve building a stored procedure to perform the backup, creating a job to run the stored procedure, and creating a performance-based alert to run the job.

The first entry is here: http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/08/29/23049.aspx

Hopefully that'll help.

|||

I'm writing a similar app using SQLDMO. Could you please give me sample code of how did you combine the two?

Thanks in advance.

mct.

Creating a Job

I am new to SQL Server and have a few questions:

1) We have given our clients the option to create a scheduled job for a future date. When this occurs, the jobID, the new action for the job, and the future date is inserted into a table aptly called 'Scheduling'. What we are hoping SQL Server can do is the following:

Query the 'Scheduling' table based on the current date to see if there are any jobs that need their actions updated. If ( 1 ) returns a recordset, update the job's action in the 'Jobs' table with the new action Delete the rows that were queried and updated from the 'Scheduling' table

I am assuming I can do this using the schedule Jobs (excuse the irony) in SQL Server Management. Is this true?

2) I having been playing with TSQL to do the previous mentioned. My other question is how can I query the database using the current date? For example, the date in the database is entered as "mm/dd/yyyy" and I can query it using the following: SELECT * FROM Scheduling WHERE Date='6/30/2006', which will return the recordsets that I desire. If I can schedule SQL Server to do this, then how will I query it based on today's date? SELECT * FROM Scheduling WHERE Date="today's date". I tried the function GETDATE(), but that didn't seem to work. Any ideas?

Thanks

I have been trying to answer my second question on my own, but so far have been unable. Like I said earlier, I have a field in my table "Scheduling" called "Date". This is a timestamp of when my clients want their schedule for their job updated. I have been trying to query the database for today's date, but I don't know how. Here is how the table looks right now:

JobID Date DC1235 2006-05-31 00:00:00.0

I can query it by the following and it works fine:

SELECT * FROM Scheduling WHERE Date='May 31, 2006'

SELECT * FROM Scheduling WHERE Date='5/31/2006'

SELECT * FROM Scheduling WHERE Date='2006-05-31'

How can I query it using today's timestamp? The following doesn't work:

SELECT * FROM Scheduling WHERE Date=getdate() //This returns nothing

Thanks,

Scott

|||

If you use datetime, you are not using the timestamp data type which is completly different to datetime and has nothing to do with date/time. It is used for row versioning (thats different to the ANSI standard).

Using datetime means that you are have always the time stored within the data, so comparing this to GETDATE() (which returns a datetime which on its own holds a time part) will return false (except if you query at midnight :-) ). You can either convert the date to a non-timecontaining format or use the datediff function to query thise records:

SELECT * FROM Scheduling WHERE DATEDIFF(dd,Date,GETDATE()) = 0

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi again,

1) is possible, but would that make sense, updating the row in the table and afterwards right deleting it ?

But in common, you can do schedule recurring jobs in SQL Server Agent, thats for sure true.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens! I also tried this:

SELECT * FROM Scheduling WHERE Date=convert(varchar,getdate(),101)

which seemed to work as well. Is this what you where talking about when you said, "...convert the date to a non-time containing format..."? Do you prefer one way over the other?

Also, is it possible to do what I want using a SQL Server Agent Job to perform what I was talking about in my first question on my first post?

Thanks!

|||

I guess we must have posted at the same time :)

As far as question 1 is concerned, 2 tables are affected. When a client schedules a new 'job' action for a future date, it is inserted into the 'Scheduling' table. When that date rolls around, the 'Job' table is updated with the new action and it gets deleted from the 'Scheduling' table as it is no longer needed. Hopefully I explained it better.

Thanks

|||

Also... Is there any good documentation or tutorials on how to get started with Transact-SQL or creating SQL Server Agent Jobs?

Thanks

|||

Hi,

"SELECT * FROM Scheduling WHERE Date=convert(varchar,getdate(),101) which seemed to work as well."

Sure you should always provide a length within VARCHAR otherwise it will be truncated to the length 1. I would rather prefer using 112 which is the ISO format.

"Do you prefer one way over the other?" I would prefer DATEDIFF, because it can take use of indexes.

HTH, jens Suessmeyer.

http://www.sqlserver2005-de

|||

Hi,

sorry I don′t know any good ressource, beside the e-learning classes of microsoft for adminstration, most of them are free and you can use a virtual sql server to test and train you knowledge. Beside this, as of my opinion it is always good to have a pocket book for administration of you are right starting with the sql things like this one here:

SQL 2000
OR
SQL2005

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for all your help Jens! I will look into purchasing the pocket book for SQL 2000. I am sure it can probably answer a lot of questions!

Thanks again,

Scott

CreateSubscription - Specify the Job Name or Get the GUID Job Name

We're using the CreateSubscription method to schedule a report for automatic delivery, using SQL Server Agent. Everything is working great. But, we had a question (or two).

Is there anyway to specify the name of the SQL Server Agent job that gets created when the CreateSubscription method is called? If so, how?
If not, is there anyway to get the GUID job name back after calling CreateSubscription?

TIA

There is no way to set the name of the SQL Agent job via the CreateSubscription method.

You can use the ListSubscriptions to get the guid and GetSubscriptionProperties to get further information.

ReportingService2005 rs = new ReportingService2005();
Subscription[] subscription = rs.ListSubscriptions(ReportAndPath, UserName);

rs.GetSubscriptionProperties(

subscription[0].SubscriptionID,

out actualExtensionSettings,

out actualDescription,

out actualActive,

out actualStatus,

out actualEventType,

out actualMatchData,

out actualParameters);

|||

Brad,

Thanks for the info. However, does using the ListSubscriptions and GetSubscriptionProperties give me the GUID name of the SQL Server Agent job?

Since there is no way to set the SQL Server Agent job to something more meaningful to end-users, the next best option for us is to provide the GUID name of the SQL Server Agent job to the end-user after is has been created. Thus, our ASP.NET app. will display to the end-user something like, "Your job has been created. The job name is xxxx." (where xxxx is the GUID job name as shown in the SQL Server list of jobs). In our situation, our end-users are 'knowledgeable' enough about our product to open SQL Server, navigate to SQL Server Agent, and find the job they just tried to create. So, we need to be able to give them some help with which job name is theirs.

Thanks.

|||

No. The guid for the SQL Agent job is not exposed through the SOAP Api's.

Why not just give them the subscription information? Instead of trying to tell them the SQL Agent Job, tell them the subscription name. "Your subscription has been created. The subscription is on "ReportX" for user "Foo" and is scheduled to send at "Time Selected". In Management Studio or Report Manager, more information is stored for the Subcription in RS then for the Job in the Agent. They can get parameter values, security info, and other information.

Just a thought.