Scheduling SSIS Package

Hi

I am trying to scheduling package using "Executing Sql Server Agent Task" before that I create new job,But there is error in STEP "Command Line Parameter are Invalid"

Anybody help me for configure jobs for Scheduling

I am new to SQL Server

Thanks in Advance

Now I want to save package in MSDB database but its gives en error as follows

"Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. "

Can anybody helpout for this error

Note : Whats modification in SQL Server Setting so did not get this error

Actually here i am trying to save a package in MSDB database for run package through Sql Server job

thnaks in advance for your help

|||

Hi,

I am new to SSIS but have worked on other ETL tools. I was trying to schedule and run a SSIS package using SQL Server Agent. I created the job but when i tried to make a step of the Job, it asks for a type of step in a drop down. I cant see the step type required for running the SSIS packages as mentioned in the help files.

Please help me on this.

Regards,

Samaksh

|||

Doesn't matter. Execute your packages from the cmdexec subsystem using dtexec. You will get more useful error messages as explained here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Don't you see the type "SQL Server Integration Services Package" as one option in the dropdown? If you choose that, you can select where your package is.|||

Yes, the help file says to look at the "SQL Server Integration Services Package" as the option in the drop down, but i am unable to see it as an option. I tried various ways to check if the i can get to it, but each time "SQL Server Integration Services Package" is not there as an option.

Can this be the issue with the licence of the product or something similar. Because this is bizarre that the option is not coming in the list.

Regards,

Samaksh

Scheduling SSIS Package

Hi

I am trying to scheduling package using "Executing Sql Server Agent Task" before that I create new job,But there is error in STEP "Command Line Parameter are Invalid"

Anybody help me for configure jobs for Scheduling

I am new to SQL Server

Thanks in Advance

Now I want to save package in MSDB database but its gives en error as follows

"Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. "

Can anybody helpout for this error

Note : Whats modification in SQL Server Setting so did not get this error

Actually here i am trying to save a package in MSDB database for run package through Sql Server job

thnaks in advance for your help

|||

Hi,

I am new to SSIS but have worked on other ETL tools. I was trying to schedule and run a SSIS package using SQL Server Agent. I created the job but when i tried to make a step of the Job, it asks for a type of step in a drop down. I cant see the step type required for running the SSIS packages as mentioned in the help files.

Please help me on this.

Regards,

Samaksh

|||

Doesn't matter. Execute your packages from the cmdexec subsystem using dtexec. You will get more useful error messages as explained here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Don't you see the type "SQL Server Integration Services Package" as one option in the dropdown? If you choose that, you can select where your package is.|||

Yes, the help file says to look at the "SQL Server Integration Services Package" as the option in the drop down, but i am unable to see it as an option. I tried various ways to check if the i can get to it, but each time "SQL Server Integration Services Package" is not there as an option.

Can this be the issue with the licence of the product or something similar. Because this is bizarre that the option is not coming in the list.

Regards,

Samaksh

Scheduling simple cdosys email task does not work

I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?

'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")

with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update

.Subject = "Notification"
.From = "donotreply@.test.com"
.TextBody = "TEST"
.Bcc = "someone@.test.com"
.Send
end with
'-

Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

Any help would be appreciated.

Almost certainly permissions. Here's the KB article on this subject:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

Is your SMTP server configured to allow anonymous sending, or to allow the account being used by Agent?

-Doug

|||

Thanks for the article.
We did try method 1 (SQL Agent proxy account). We also set method 2 for serverstorage protection level. We don't have any sensitive information so we skipped method 3 (Encrypt Sensitive with password). As for method 4, we do have package configuration files that have been set as well.

Also, our SMTP server allows anonymous connections.

However, since we are trying to execute activex script tasks using CDOSYS, we're assuming perhaps there is some security permissions issue we overlooked with "component services" perhaps? We also tried adding our SQL agent account (as well as proxy account) into our local admins group, but without any luck. It still runs only manually, but fails for CDOSYS when scheduled.

Scheduling simple cdosys email task does not work

I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?

'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")

with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update

.Subject = "Notification"
.From = "donotreply@.test.com"
.TextBody = "TEST"
.Bcc = "someone@.test.com"
.Send
end with
'-

Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.

Any help would be appreciated.

Almost certainly permissions. Here's the KB article on this subject:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;918760

Is your SMTP server configured to allow anonymous sending, or to allow the account being used by Agent?

-Doug

|||

Thanks for the article.
We did try method 1 (SQL Agent proxy account). We also set method 2 for serverstorage protection level. We don't have any sensitive information so we skipped method 3 (Encrypt Sensitive with password). As for method 4, we do have package configuration files that have been set as well.

Also, our SMTP server allows anonymous connections.

However, since we are trying to execute activex script tasks using CDOSYS, we're assuming perhaps there is some security permissions issue we overlooked with "component services" perhaps? We also tried adding our SQL agent account (as well as proxy account) into our local admins group, but without any luck. It still runs only manually, but fails for CDOSYS when scheduled.

Scheduling not working

I have an Active Task in a DTS which uses CDONTS to send an email. When I
execute the package manually it works. The schedule doesn't. It says in the
job list that it has been successful. The email does not, however, get sent.
The job, server agent and task scheduler are all enabled. I have even tried
setting up the job/dts package on the server in case there was some kind of
permissions issue. Any ideas? Regards, Chris.
The solution was specify a user name that belonged to sysadmin when creating
the package and schedule.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:enmaP4%23REHA.3444@.TK2MSFTNGP12.phx.gbl...
> I have an Active Task in a DTS which uses CDONTS to send an email. When I
> execute the package manually it works. The schedule doesn't. It says in
the
> job list that it has been successful. The email does not, however, get
sent.
> The job, server agent and task scheduler are all enabled. I have even
tried
> setting up the job/dts package on the server in case there was some kind
of
> permissions issue. Any ideas? Regards, Chris.
>

Scheduling not working

I have an Active Task in a DTS which uses CDONTS to send an email. When I
execute the package manually it works. The schedule doesn't. It says in the
job list that it has been successful. The email does not, however, get sent.
The job, server agent and task scheduler are all enabled. I have even tried
setting up the job/dts package on the server in case there was some kind of
permissions issue. Any ideas? Regards, Chris.The solution was specify a user name that belonged to sysadmin when creating
the package and schedule.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:enmaP4%23REHA.3444@.TK2MSFTNGP12.phx.gbl...
> I have an Active Task in a DTS which uses CDONTS to send an email. When I
> execute the package manually it works. The schedule doesn't. It says in
the
> job list that it has been successful. The email does not, however, get
sent.
> The job, server agent and task scheduler are all enabled. I have even
tried
> setting up the job/dts package on the server in case there was some kind
of
> permissions issue. Any ideas? Regards, Chris.
>

Scheduling not working

I have an Active Task in a DTS which uses CDONTS to send an email. When I
execute the package manually it works. The schedule doesn't. It says in the
job list that it has been successful. The email does not, however, get sent.
The job, server agent and task scheduler are all enabled. I have even tried
setting up the job/dts package on the server in case there was some kind of
permissions issue. Any ideas? Regards, Chris.The solution was specify a user name that belonged to sysadmin when creating
the package and schedule.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:enmaP4%23REHA.3444@.TK2MSFTNGP12.phx.gbl...
> I have an Active Task in a DTS which uses CDONTS to send an email. When I
> execute the package manually it works. The schedule doesn't. It says in
the
> job list that it has been successful. The email does not, however, get
sent.
> The job, server agent and task scheduler are all enabled. I have even
tried
> setting up the job/dts package on the server in case there was some kind
of
> permissions issue. Any ideas? Regards, Chris.
>

Scheduling DBCC DBReindex

How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
I've got backups scheduled nightly, but don't know how to schedule something
like this. I've got 7 tables that need to be reindexed and defragged every
Saturday night.
Tips/Help/etc appreciated.
Thanks!
YsandreJust create a TSQL job step which includes your DBCC DBREINDEX or DBCC INDEX
DEFRAG commands. The commands are
documented in Books Online. You also want to read below excellent article ab
ut defragmentation. :-)
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <ysandre@.hotmail.com> wrote in message news:317B96C9-15E6-46A7-972E-7DA32C7DEB21@.m
icrosoft.com...
> How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
> I've got backups scheduled nightly, but don't know how to schedule something like
this. I've got 7 tables
that need to be reindexed and defragged every Saturday night.
> Tips/Help/etc appreciated.
> Thanks!
> Ysandre|||Thanks for the article.
I know the commands for DBReindex and indexDefrag.
What I don't know is how to schedule it so it kicks off at, say, 9pm tonight
, without me having to be here (physically or remotely).
I'm fairly familiar with NT Scheduler, and I have scheduled backups of the S
QL database running nightly.
How do I create a job/task/process that kicks off the DBCC DBReindex at a sp
ecific time, either daily or weekly?
I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
Help appreciated!
Thanks . . .|||How do you schedule the backups? I hope you don't backup just the database f
iles... You should use the TSQL
BACKUP DATABASE and possibly the BACKUP LOG command to do SQL Server backups
.
Anyhow, you can use the NT Scheduler and use OSQL.EXE with the /i option to
specify a script file containing
the TSQL commands you want to execute.
Most dba's, however, use SQL Server's scheduler for these things: SQL Server
Agent. In Enterprise Manager,
Management, SQL Server Agent, Jobs, create a new job, add a TSQL job step wh
ich contains your TSQL commands.
To get fancy, I also suggest you specify an output file for the jobsteps, an
d then add a couple of finishing
job steps to send email on success/failure. Use xp_smtp_sendmail for the email (w.SQLDev.Net" target="_blank">ww
w.SQLDev.Net) and include
the output file in the email. See p." target="_blank">http://www.karaszi.com/sqlserver/in...pi.as
p.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <anonymous@.discussions.microsoft.com> wrote in message
news:9387A1F6-A6C3-49B3-A939-0E983AEC2256@.microsoft.com...
> Thanks for the article.
> I know the commands for DBReindex and indexDefrag.
> What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, with
out me having to be here
(physically or remotely).
> I'm fairly familiar with NT Scheduler, and I have scheduled backups of the
SQL database running nightly.
> How do I create a job/task/process that kicks off the DBCC DBReindex at a specific
time, either daily or
weekly?
> I'm not familiary with TSQL, and BOL is not giving me what I'm looking for
.
> Help appreciated!
> Thanks . . .|||yes this is exactly how I do it.
I just reindex every night in a SQL Agent Job.
the job has the following steps.
1. run indexDefrag on all indexes that I have decided need to be in my
maintenance list (Details would bore you)
2. After the index step has ran (either successfully or unsuccessfully) I
run the full backup
I want to make sure that the two steps above are not running at the same
time....
hope this, along with Tibors post, helps
Greg Jackson
PDX, Oregon|||Thank you! That (and Tibor's post) were exactly what I needed.

Scheduling DBCC DBReindex

How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
I've got backups scheduled nightly, but don't know how to schedule something like this. I've got 7 tables that need to be reindexed and defragged every Saturday night.
Tips/Help/etc appreciated.
Thanks!
Ysandre
Just create a TSQL job step which includes your DBCC DBREINDEX or DBCC INDEXDEFRAG commands. The commands are
documented in Books Online. You also want to read below excellent article abut defragmentation. :-)
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <ysandre@.hotmail.com> wrote in message news:317B96C9-15E6-46A7-972E-7DA32C7DEB21@.microsoft.com...
> How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
> I've got backups scheduled nightly, but don't know how to schedule something like this. I've got 7 tables
that need to be reindexed and defragged every Saturday night.
> Tips/Help/etc appreciated.
> Thanks!
> Ysandre
|||Thanks for the article.
I know the commands for DBReindex and indexDefrag.
What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, without me having to be here (physically or remotely).
I'm fairly familiar with NT Scheduler, and I have scheduled backups of the SQL database running nightly.
How do I create a job/task/process that kicks off the DBCC DBReindex at a specific time, either daily or weekly?
I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
Help appreciated!
Thanks . . .
|||How do you schedule the backups? I hope you don't backup just the database files... You should use the TSQL
BACKUP DATABASE and possibly the BACKUP LOG command to do SQL Server backups.
Anyhow, you can use the NT Scheduler and use OSQL.EXE with the /i option to specify a script file containing
the TSQL commands you want to execute.
Most dba's, however, use SQL Server's scheduler for these things: SQL Server Agent. In Enterprise Manager,
Management, SQL Server Agent, Jobs, create a new job, add a TSQL job step which contains your TSQL commands.
To get fancy, I also suggest you specify an output file for the jobsteps, and then add a couple of finishing
job steps to send email on success/failure. Use xp_smtp_sendmail for the email (www.SQLDev.Net) and include
the output file in the email. See http://www.karaszi.com/sqlserver/info_no_mapi.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <anonymous@.discussions.microsoft.com> wrote in message
news:9387A1F6-A6C3-49B3-A939-0E983AEC2256@.microsoft.com...
> Thanks for the article.
> I know the commands for DBReindex and indexDefrag.
> What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, without me having to be here
(physically or remotely).
> I'm fairly familiar with NT Scheduler, and I have scheduled backups of the SQL database running nightly.
> How do I create a job/task/process that kicks off the DBCC DBReindex at a specific time, either daily or
weekly?
> I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
> Help appreciated!
> Thanks . . .
|||yes this is exactly how I do it.
I just reindex every night in a SQL Agent Job.
the job has the following steps.
1. run indexDefrag on all indexes that I have decided need to be in my
maintenance list (Details would bore you)
2. After the index step has ran (either successfully or unsuccessfully) I
run the full backup
I want to make sure that the two steps above are not running at the same
time....
hope this, along with Tibors post, helps
Greg Jackson
PDX, Oregon
|||Thank you! That (and Tibor's post) were exactly what I needed.

Scheduling a text file to be inserted to a database table

Hi there
New to SQL 2000 and i have a question regarding scheduling a task. I have a
text file that is being downloaded to a web folder from an AS400 system
three times a day. I want to then grab this file and insert and replace the
exisiting records in a database table.
What be the best way to do this? Stored Procedure?
Can you schedule stored procedures to be run at time specific intervals?
Any help appreciatedBJ
DTS Package
"bj" <orders@.seton.net.au> wrote in message
news:OifAtnhUGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Hi there
> New to SQL 2000 and i have a question regarding scheduling a task. I have
> a text file that is being downloaded to a web folder from an AS400 system
> three times a day. I want to then grab this file and insert and replace
> the exisiting records in a database table.
> What be the best way to do this? Stored Procedure?
> Can you schedule stored procedures to be run at time specific intervals?
> Any help appreciated
>
>

Scheduling a task using DTS

Hey Guys!
I have a SQL Update Query that I want to run automatically everyday andI have read through the posts and I do not understand very clearly howto go about creating one.
The SQL query I want to run is this:
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0
Once I have made a DTS package. How do I declare it in my coding?
Thanks in advance!
If you have access to add a job in SQL Agent, you can create a job and just run the query. You don't need DTS at all. If you don't have access to create a new job, then you'll have to do something outside of SQL using a different scheduling mechanism. Either way, there's no need for DTS to run a query like this. DTS is more for importing and/or exporting data and objects. Even if you did it in DTS, it's the SQL Agent service you'd use to create and schedule a job to run the DTS package.|||I never knew that there was another way of scheduling a SQL task.
I have tried to create a SQL Agent Task by going to Management >> SQL Server Agent >> Jobs
But for some reason it is not working even through I have scheduled a job so that it will run the query automatically.
I tested the SQL Agent Job by right clicking over my job and selecting 'Start Job'. Then an error occurs:
error 22022SQLServerAgent is not currently running so it cannot be notified forthis action
So how do i go about running it?
|||You have to start the Agent service. Once that's running, it will run the job on the schedule you define. Refer to Books on Line for more details on the Agent service.|||It works now!
Thanks!
sql

scheduling a query

I need to scheduling a query to update some records.
I have to use a script in windows task manager or there is another way?
Many ThanksThe SQL Agent is a wonderful wat to schedule a query. Create a job and out
your query in a step.
"Simone" wrote:

> I need to scheduling a query to update some records.
> I have to use a script in windows task manager or there is another way?
> Many Thanks
>
>|||If you are using an edition of SQL Server which has an enterprise
manager and therefor a UI for SQL Server Agent, you can just create a
job with your query. Add a new job, add a new step (Transact SQL
Execution), paste your statement in there and add a schedule for it and
voil=E1, you=B4re done. iof you do=B4n=B4t have a GUI, you can use the
goold old fashioned AT to do your jobs. Just schedule your job by the
Windows Scheduler or AT, suing OSQL to run the query: OSQL -SServername
-UUsername -PPassword -Q"Here is my query".
HTH, jens Suessmeyer.|||you can create a job and schedule that
Jobs are under management-->Sql Server agent-->Jobs
make it a one step job paste your query or proc call in the step
command window and schedule the job
http://sqlservercode.blogspot.com/

scheduling a query

I need to scheduling a query to update some records.
I have to use a script in windows task manager or there is another way?
Many ThanksIf you are using an edition of SQL Server which has an enterprise
manager and therefor a UI for SQL Server Agent, you can just create a
job with your query. Add a new job, add a new step (Transact SQL
Execution), paste your statement in there and add a schedule for it and
voil=E1, you=B4re done. iof you do=B4n=B4t have a GUI, you can use the
goold old fashioned AT to do your jobs. Just schedule your job by the
Windows Scheduler or AT, suing OSQL to run the query: OSQL -SServername
-UUsername -PPassword -Q"Here is my query".
HTH, jens Suessmeyer.|||The SQL Agent is a wonderful wat to schedule a query. Create a job and out
your query in a step.
"Simone" wrote:
> I need to scheduling a query to update some records.
> I have to use a script in windows task manager or there is another way?
> Many Thanks
>
>|||you can create a job and schedule that
Jobs are under management-->Sql Server agent-->Jobs
make it a one step job paste your query or proc call in the step
command window and schedule the job
http://sqlservercode.blogspot.com/

scheduling a query

I need to scheduling a query to update some records.
I have to use a script in windows task manager or there is another way?
Many Thanks
The SQL Agent is a wonderful wat to schedule a query. Create a job and out
your query in a step.
"Simone" wrote:

> I need to scheduling a query to update some records.
> I have to use a script in windows task manager or there is another way?
> Many Thanks
>
>
|||If you are using an edition of SQL Server which has an enterprise
manager and therefor a UI for SQL Server Agent, you can just create a
job with your query. Add a new job, add a new step (Transact SQL
Execution), paste your statement in there and add a schedule for it and
voil=E1, you=B4re done. iof you do=B4n=B4t have a GUI, you can use the
goold old fashioned AT to do your jobs. Just schedule your job by the
Windows Scheduler or AT, suing OSQL to run the query: OSQL -SServername
-UUsername -PPassword -Q"Here is my query".
HTH, jens Suessmeyer.
|||you can create a job and schedule that
Jobs are under management-->Sql Server agent-->Jobs
make it a one step job paste your query or proc call in the step
command window and schedule the job
http://sqlservercode.blogspot.com/

Scheduled Tasks on Windows 2000/2003

I'm trying to use scheduled tasks to copy over transaction logs from one server to another.

Can I schedule a task to run hourly? I have it set to repeat the task every hour, but it's not working. It does it once and then the next run date is tomorrow.

Please let me know if I'm missing something.

Thanks

Susan

Figured it out..

I had the repeat task selected, but had the duration at an hour and a half.

Once I modified the duration to 24 hours it worked great!!

Thanks

Susan

Scheduled Tasks for SQL 7.0

Is there a way to schedule a task to shutdown SQL server, run a backup and
restart the service after the backup is finished?
can use the AT scheduler for this but why not backup the database and then
schedule the backup software using sqlserveragent to backup the backup file
as a subsequent step to the database backup?
"Margo" <margo@.discussions.microsoft.com> wrote in message
news:1D7E947F-0F07-4DAC-84E9-C94829930DCD@.microsoft.com...
> Is there a way to schedule a task to shutdown SQL server, run a backup and
> restart the service after the backup is finished?
|||Is there any documentation on this procedure that I could follow?
"Olu Adedeji" wrote:

> can use the AT scheduler for this but why not backup the database and then
> schedule the backup software using sqlserveragent to backup the backup file
> as a subsequent step to the database backup?
> "Margo" <margo@.discussions.microsoft.com> wrote in message
> news:1D7E947F-0F07-4DAC-84E9-C94829930DCD@.microsoft.com...
>
>

Scheduled Tasks for SQL 7.0

Is there a way to schedule a task to shutdown SQL server, run a backup and
restart the service after the backup is finished?can use the AT scheduler for this but why not backup the database and then
schedule the backup software using sqlserveragent to backup the backup file
as a subsequent step to the database backup?
"Margo" <margo@.discussions.microsoft.com> wrote in message
news:1D7E947F-0F07-4DAC-84E9-C94829930DCD@.microsoft.com...
> Is there a way to schedule a task to shutdown SQL server, run a backup and
> restart the service after the backup is finished?|||Is there any documentation on this procedure that I could follow?
"Olu Adedeji" wrote:

> can use the AT scheduler for this but why not backup the database and then
> schedule the backup software using sqlserveragent to backup the backup fil
e
> as a subsequent step to the database backup?
> "Margo" <margo@.discussions.microsoft.com> wrote in message
> news:1D7E947F-0F07-4DAC-84E9-C94829930DCD@.microsoft.com...
>
>

Scheduled Tasks for SQL 7.0

Is there a way to schedule a task to shutdown SQL server, run a backup and
restart the service after the backup is finished?can use the AT scheduler for this but why not backup the database and then
schedule the backup software using sqlserveragent to backup the backup file
as a subsequent step to the database backup?
"Margo" <margo@.discussions.microsoft.com> wrote in message
news:1D7E947F-0F07-4DAC-84E9-C94829930DCD@.microsoft.com...
> Is there a way to schedule a task to shutdown SQL server, run a backup and
> restart the service after the backup is finished?|||Is there any documentation on this procedure that I could follow?
"Olu Adedeji" wrote:
> can use the AT scheduler for this but why not backup the database and then
> schedule the backup software using sqlserveragent to backup the backup file
> as a subsequent step to the database backup?
> "Margo" <margo@.discussions.microsoft.com> wrote in message
> news:1D7E947F-0F07-4DAC-84E9-C94829930DCD@.microsoft.com...
> > Is there a way to schedule a task to shutdown SQL server, run a backup and
> > restart the service after the backup is finished?
>
>

Scheduled Task to restore Database ?

There is a requirement to restore the production database to testing
database regularly. It can be done manually with no big problem except
changing the logins.
However, someone suggests scheduling a task to restore the production
database to testing database on every Sunday. Is it possible to do so ?
Thanking you in anticipation.
Robert wrote:
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
You can create a SQL script and then schedule this to run every Sunday.
It might also be necessary to add a step in the script to disconnect any
user sessions before you start the restore. Otherwise the Restore will
fail if there're users connected.
Regards
Steen
|||Robert
Well , in our company we do it every month , so prior to RESTORE to
developing server we backup an existing database (on Developing Server) and
the drop it
Yes , you can create an job to perform it , however I do it by running
stored procedure that does restore operation from QA
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:%23dqYBjLIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
|||Dear Steen,
Do you have any idea where can I find samples of those scripts - disconnect
user and restore ?
Thanks
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Robert wrote:
> You can create a SQL script and then schedule this to run every Sunday.
> It might also be necessary to add a step in the script to disconnect any
> user sessions before you start the restore. Otherwise the Restore will
> fail if there're users connected.
>
> Regards
> Steen
|||> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
Below is an example (SQL 2000). See the Books Online for syntax details.
USE master
ALTER DATABASE MyTestDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyTestDatabase
FROM DISK='C:\Backups\MyProductionDatabase.bak'
WITH
MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
--login/user fixup here
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Dear Steen,
> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
> Thanks
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>
|||Dear Dan,
Thank you for your advice and it works properly.
I have set up a job to with 2 steps - The first one is to make the backup of
the Production DB and the second one is to restore to the Testing DB.
I would like to make 2 enhancement and would like to seek your advice.
1) When I "Set Single User", I find that if someone is connected, it fails.
Is it possible to disconnect users connected to the Testing Database ?
2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
like to include it in Step 3 ?
Thanks again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
> Below is an example (SQL 2000). See the Books Online for syntax details.
> USE master
> ALTER DATABASE MyTestDatabase
> SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyTestDatabase
> FROM DISK='C:\Backups\MyProductionDatabase.bak'
> WITH
> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
> MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
> --login/user fixup here
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>
|||> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill
all connections to that database except your own (you can issue the command
from master). However, it might take a little time for the killed
transaction(s) to rollback. In that case, you might try including the
following between the ALTER DATABASE and RESTORE:
--wait for all database locks to be released
WHILE EXISTS
(
SELECT *
FROM syslocks
WHERE dbid = DB_ID('MyDatabase')
)
BEGIN
WAITFOR DELAY '00:00:01'
END

> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
You can include the delete command (DEL
"C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
also delete the file from an ActiveX script or T-SQL xp_cmdshell command but
those methods are more complex than needed for this simple requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Dear Dan,
> Thank you for your advice and it works properly.
> I have set up a job to with 2 steps - The first one is to make the backup
> of the Production DB and the second one is to restore to the Testing DB.
> I would like to make 2 enhancement and would like to seek your advice.
> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
> Thanks again.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>
|||Dear Dan,
Thank you for your advice.
When I try to disconnect yesterday, maybe I have already kicked users out
but I am not aware. I still get the error message that it cannot be set to
single user - maybe because I am still connecting to it.
I will try your suggestion tomorrow.
Thanks for your advice again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%235jXFoaIGHA.516@.TK2MSFTNGP15.phx.gbl...
> Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should
> kill all connections to that database except your own (you can issue the
> command from master). However, it might take a little time for the killed
> transaction(s) to rollback. In that case, you might try including the
> following between the ALTER DATABASE and RESTORE:
> --wait for all database locks to be released
> WHILE EXISTS
> (
> SELECT *
> FROM syslocks
> WHERE dbid = DB_ID('MyDatabase')
> )
> BEGIN
> WAITFOR DELAY '00:00:01'
> END
>
> You can include the delete command (DEL
> "C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
> also delete the file from an ActiveX script or T-SQL xp_cmdshell command
> but those methods are more complex than needed for this simple
> requirement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>

Scheduled Task to restore Database ?

There is a requirement to restore the production database to testing
database regularly. It can be done manually with no big problem except
changing the logins.
However, someone suggests scheduling a task to restore the production
database to testing database on every Sunday. Is it possible to do so ?
Thanking you in anticipation.Robert wrote:
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>
You can create a SQL script and then schedule this to run every Sunday.
It might also be necessary to add a step in the script to disconnect any
user sessions before you start the restore. Otherwise the Restore will
fail if there're users connected.
Regards
Steen|||Robert
Well , in our company we do it every month , so prior to RESTORE to
developing server we backup an existing database (on Developing Server) and
the drop it
Yes , you can create an job to perform it , however I do it by running
stored procedure that does restore operation from QA
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:%23dqYBjLIGHA.3144@.TK2MSFTNGP11.phx.gbl...
> There is a requirement to restore the production database to testing
> database regularly. It can be done manually with no big problem except
> changing the logins.
> However, someone suggests scheduling a task to restore the production
> database to testing database on every Sunday. Is it possible to do so ?
> Thanking you in anticipation.
>|||Dear Steen,
Do you have any idea where can I find samples of those scripts - disconnect
user and restore ?
Thanks
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so ?
>> Thanking you in anticipation.
> You can create a SQL script and then schedule this to run every Sunday.
> It might also be necessary to add a step in the script to disconnect any
> user sessions before you start the restore. Otherwise the Restore will
> fail if there're users connected.
>
> Regards
> Steen|||> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
Below is an example (SQL 2000). See the Books Online for syntax details.
USE master
ALTER DATABASE MyTestDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
RESTORE DATABASE MyTestDatabase
FROM DISK='C:\Backups\MyProductionDatabase.bak'
WITH
MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
--login/user fixup here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Dear Steen,
> Do you have any idea where can I find samples of those scripts -
> disconnect user and restore ?
> Thanks
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect any
>> user sessions before you start the restore. Otherwise the Restore will
>> fail if there're users connected.
>>
>> Regards
>> Steen
>|||Dear Dan,
Thank you for your advice and it works properly.
I have set up a job to with 2 steps - The first one is to make the backup of
the Production DB and the second one is to restore to the Testing DB.
I would like to make 2 enhancement and would like to seek your advice.
1) When I "Set Single User", I find that if someone is connected, it fails.
Is it possible to disconnect users connected to the Testing Database ?
2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
like to include it in Step 3 ?
Thanks again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
> Below is an example (SQL 2000). See the Books Online for syntax details.
> USE master
> ALTER DATABASE MyTestDatabase
> SET SINGLE_USER
> WITH ROLLBACK IMMEDIATE
> RESTORE DATABASE MyTestDatabase
> FROM DISK='C:\Backups\MyProductionDatabase.bak'
> WITH
> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
> MOVE 'MyProductionDatabase_Log' TO 'F:\LogFiles\MyTestDatabase_Log.ldf'
> --login/user fixup here
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem except
>> changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so
>> ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect any
>> user sessions before you start the restore. Otherwise the Restore will
>> fail if there're users connected.
>>
>> Regards
>> Steen
>>
>|||> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill
all connections to that database except your own (you can issue the command
from master). However, it might take a little time for the killed
transaction(s) to rollback. In that case, you might try including the
following between the ALTER DATABASE and RESTORE:
--wait for all database locks to be released
WHILE EXISTS
(
SELECT *
FROM syslocks
WHERE dbid = DB_ID('MyDatabase')
)
BEGIN
WAITFOR DELAY '00:00:01'
END
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
You can include the delete command (DEL
"C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
also delete the file from an ActiveX script or T-SQL xp_cmdshell command but
those methods are more complex than needed for this simple requirement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Dear Dan,
> Thank you for your advice and it works properly.
> I have set up a job to with 2 steps - The first one is to make the backup
> of the Production DB and the second one is to restore to the Testing DB.
> I would like to make 2 enhancement and would like to seek your advice.
> 1) When I "Set Single User", I find that if someone is connected, it
> fails. Is it possible to disconnect users connected to the Testing
> Database ?
> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
> like to include it in Step 3 ?
> Thanks again.
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Below is an example (SQL 2000). See the Books Online for syntax details.
>> USE master
>> ALTER DATABASE MyTestDatabase
>> SET SINGLE_USER
>> WITH ROLLBACK IMMEDIATE
>> RESTORE DATABASE MyTestDatabase
>> FROM DISK='C:\Backups\MyProductionDatabase.bak'
>> WITH
>> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
>> MOVE 'MyProductionDatabase_Log' TO
>> 'F:\LogFiles\MyTestDatabase_Log.ldf'
>> --login/user fixup here
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem
>> except changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do so
>> ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every Sunday.
>> It might also be necessary to add a step in the script to disconnect
>> any user sessions before you start the restore. Otherwise the Restore
>> will fail if there're users connected.
>>
>> Regards
>> Steen
>>
>>
>|||Dear Dan,
Thank you for your advice.
When I try to disconnect yesterday, maybe I have already kicked users out
but I am not aware. I still get the error message that it cannot be set to
single user - maybe because I am still connecting to it.
I will try your suggestion tomorrow.
Thanks for your advice again.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%235jXFoaIGHA.516@.TK2MSFTNGP15.phx.gbl...
>> 1) When I "Set Single User", I find that if someone is connected, it
>> fails. Is it possible to disconnect users connected to the Testing
>> Database ?
> Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should
> kill all connections to that database except your own (you can issue the
> command from master). However, it might take a little time for the killed
> transaction(s) to rollback. In that case, you might try including the
> following between the ALTER DATABASE and RESTORE:
> --wait for all database locks to be released
> WHILE EXISTS
> (
> SELECT *
> FROM syslocks
> WHERE dbid = DB_ID('MyDatabase')
> )
> BEGIN
> WAITFOR DELAY '00:00:01'
> END
>> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
>> like to include it in Step 3 ?
> You can include the delete command (DEL
> "C:\Backups\MyProductionDatabase.bak") in a CmdExec job step. You could
> also delete the file from an ActiveX script or T-SQL xp_cmdshell command
> but those methods are more complex than needed for this simple
> requirement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:uB4al1YIGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Dear Dan,
>> Thank you for your advice and it works properly.
>> I have set up a job to with 2 steps - The first one is to make the backup
>> of the Production DB and the second one is to restore to the Testing DB.
>> I would like to make 2 enhancement and would like to seek your advice.
>> 1) When I "Set Single User", I find that if someone is connected, it
>> fails. Is it possible to disconnect users connected to the Testing
>> Database ?
>> 2) How can I delete the 'C:\Backups\MyProductionDatabase.bak' if I would
>> like to include it in Step 3 ?
>> Thanks again.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:eELyPDOIGHA.2472@.TK2MSFTNGP10.phx.gbl...
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Below is an example (SQL 2000). See the Books Online for syntax
>> details.
>> USE master
>> ALTER DATABASE MyTestDatabase
>> SET SINGLE_USER
>> WITH ROLLBACK IMMEDIATE
>> RESTORE DATABASE MyTestDatabase
>> FROM DISK='C:\Backups\MyProductionDatabase.bak'
>> WITH
>> MOVE 'MyProductionDatabase' TO 'E:\DataFiles\MyTestDatabase.mdf',
>> MOVE 'MyProductionDatabase_Log' TO
>> 'F:\LogFiles\MyTestDatabase_Log.ldf'
>> --login/user fixup here
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Okkt8YNIGHA.3700@.TK2MSFTNGP15.phx.gbl...
>> Dear Steen,
>> Do you have any idea where can I find samples of those scripts -
>> disconnect user and restore ?
>> Thanks
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:ugQw8oLIGHA.1424@.TK2MSFTNGP12.phx.gbl...
>> Robert wrote:
>> There is a requirement to restore the production database to testing
>> database regularly. It can be done manually with no big problem
>> except changing the logins.
>> However, someone suggests scheduling a task to restore the production
>> database to testing database on every Sunday. Is it possible to do
>> so ?
>> Thanking you in anticipation.
>> You can create a SQL script and then schedule this to run every
>> Sunday.
>> It might also be necessary to add a step in the script to disconnect
>> any user sessions before you start the restore. Otherwise the Restore
>> will fail if there're users connected.
>>
>> Regards
>> Steen
>>
>>
>>
>