Scheduling outside SRS

Hello All!
I have a requirement to set a schedule to run a report on the Tuesday
following the last Saturday of the month. It appears that this is not
possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
and to make it more complex, if the Tuesday in in the new month, quarter, or
year then the parameter should select the previous month, quarter, or year
not the default of current month, current quarter, or current year. I think
the last part can easily be handled by getting the parameter values from the
date of the last saturday of the month, which would always give you the
correct month for month end reporting no matter if the next Tues is in the
next month or not.
what I need is the syntax for defining the parameters and rendering the
report. I would also like to specify a network drive where it can be
archived, email a link to an audience, and save a snapshot in history.
Can someone please help me with this? If not all then parts would be
appreciated!!!
AnthonyI am in the same situation. Were you able to resolve this?
--
---
Yes, I searched first :)
"anthonysjo" wrote:
> Hello All!
> I have a requirement to set a schedule to run a report on the Tuesday
> following the last Saturday of the month. It appears that this is not
> possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> and to make it more complex, if the Tuesday in in the new month, quarter, or
> year then the parameter should select the previous month, quarter, or year
> not the default of current month, current quarter, or current year. I think
> the last part can easily be handled by getting the parameter values from the
> date of the last saturday of the month, which would always give you the
> correct month for month end reporting no matter if the next Tues is in the
> next month or not.
> what I need is the syntax for defining the parameters and rendering the
> report. I would also like to specify a network drive where it can be
> archived, email a link to an audience, and save a snapshot in history.
> Can someone please help me with this? If not all then parts would be
> appreciated!!!
> Anthony
>|||Yes. Here is what you do. Create a subscription or a shared schedule but
set it to run only once and make it in the past. This will create a Schedule
ID in the database that you can reference later. Then open the Report Server
Database and return all rows for either the Schedules or Subcriptions table
depending on what you created. Copy the Schdule ID out of the table and make
note of the Event Type.
Now that you have this you can use the following code to fire the report
manually:
exec ReportServer.dbo.AddEvent
@.EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@.EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
I put this code inside the following stored procedure that is executed every
tuesday by a SQL job. If the @.myoutput date = Getdate then it executes the
code above otherwise it does nothing.
CREATE PROC dbo.MONTH_END_SCHEDULE as
--Declare variables
declare @.myoutputdate datetime
declare @.mydate datetime
declare @.minus int
declare @.TuesdayFound char(1)
declare @.subject varchar (255)
--set variables
set @.TuesdayFound='N'
--seed the date with the last day of the month
set
@.mydate=dateadd(dd,-1,convert(datetime,convert(varchar(2),datepart(mm,dateadd(mm,1,getdate())))+'/1/'+
convert(varchar(4),datepart(yy,dateadd(mm,1,getdate())))))
--a variable to backwards through the days of the month
set @.minus=0
WHILE @.TuesdayFound = 'N'
BEGIN
if datepart(dw,dateadd(dd,(@.minus*-1),@.mydate))=7 -- Find Last Saturday
BEGIN
set @.myoutputdate=dateadd(dd, 3,(dateadd(dd,(@.minus*-1),@.mydate))) --This
will add 3 days to the last Saturday
set @.TuesdayFound = 'Y'
END
set @.minus=@.minus+1
END
print @.myoutputdate
if datepart(dy,(getdate()))=datepart(dy,(@.myoutputdate))
BEGIN
exec ReportServer.dbo.AddEvent
@.EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@.EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
PRINT datename(mm, @.mydate)+ ' ' + datename(yyyy, @.mydate)+ ' Reports Fired '
SET @.subject = datename(mm, @.mydate)+ ' ' + datename(yyyy, @.mydate)+ ' Month
End reports are ready for viewing '
Exec master..xp_sendmail
@.recipients = 'someone@.somewhere.com',
@.copy_recipients = 'someone@.somewhere.com',
@.subject = @.subject,
@.message = 'Month End reports are now available via reporting services.
You can click on the link below and you will be taken directly to the
Month-End reports folder where you may choose to view the most recient
reports or view the history for archived reports.
http://localhost/Reports
If you have any questions please send an email to
someone@.somewhere.com'
END
GO
"Kmistic" wrote:
> I am in the same situation. Were you able to resolve this?
> --
> ---
> Yes, I searched first :)
>
> "anthonysjo" wrote:
> > Hello All!
> >
> > I have a requirement to set a schedule to run a report on the Tuesday
> > following the last Saturday of the month. It appears that this is not
> > possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> > and to make it more complex, if the Tuesday in in the new month, quarter, or
> > year then the parameter should select the previous month, quarter, or year
> > not the default of current month, current quarter, or current year. I think
> > the last part can easily be handled by getting the parameter values from the
> > date of the last saturday of the month, which would always give you the
> > correct month for month end reporting no matter if the next Tues is in the
> > next month or not.
> >
> > what I need is the syntax for defining the parameters and rendering the
> > report. I would also like to specify a network drive where it can be
> > archived, email a link to an audience, and save a snapshot in history.
> >
> > Can someone please help me with this? If not all then parts would be
> > appreciated!!!
> >
> > Anthony
> >

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

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

Schedule query results to file...

Hi there.
I have many needs. One of these happens to be a requirement to set up a
scheduled task which calls a simple stored procedure and then sent the
resulting record set to a file - preferably in .csv format.
Anybody any ideas how to go about this? My stored proc is very basic (select
* from...) so there's nothing fancy with the resulting records it returns...
.
Any help would greatly appreciated!
All the best,
LenCreate a DTS package that execute the sp and schedule it.
AMB
"len" wrote:

> Hi there.
> I have many needs. One of these happens to be a requirement to set up a
> scheduled task which calls a simple stored procedure and then sent the
> resulting record set to a file - preferably in .csv format.
> Anybody any ideas how to go about this? My stored proc is very basic (sele
ct
> * from...) so there's nothing fancy with the resulting records it returns.
...
> Any help would greatly appreciated!
> All the best,
> Len|||use osql with output file
"len" <len@.discussions.microsoft.com> wrote in message
news:F20E1C59-A4C7-433A-822F-9153B74B0E5C@.microsoft.com...
> Hi there.
> I have many needs. One of these happens to be a requirement to set up a
> scheduled task which calls a simple stored procedure and then sent the
> resulting record set to a file - preferably in .csv format.
> Anybody any ideas how to go about this? My stored proc is very basic
> (select
> * from...) so there's nothing fancy with the resulting records it
> returns....
> Any help would greatly appreciated!
> All the best,
> Len