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

Schedule Stored Procedure: Changing one Field based on another

Hi all,

I want to do something that I can't get my head around. Here is the info..

I have a table, Tips. This is a table holding playing tips for a department of music web site. The tips are broken down into areas (brass, woodwind, etc...). Each area has its own .aspx page which has a user control that will display a "tip of the week." That tip will be taken from the table. Columns are:

ID, int, pk
AreaID, int
CategoryID, int
Active, bit
Selected, bit
Author, nvarchar
TipText, text

For what I want to do the only fields that are in play are the ID, AreaID and Selected. The table will have multiple rows for each AreaID, and at any one given time only one of the multiple rows with the same AreaID will be selected (Selected = 1). The others will be 0.

I'm trying to figure out how to write a stored procedure that, scheduled to run weekly, will select one random tip for each disctinct area id (setting selected = 1) that is not currently selected and then deselecting the previously selected items (setting selected = 0).

Doing this for a single record in the table would be easy. The tricky part for me is doing this for each of the distinct AreaIDs.

Ideas?

RichardWow. No replies.

Did I not ask the question right? I'm still trying to figure this out. If it's a stupid question I'd appreciate a heads up about where I should look to find this info.

Thanks!

Richard|||Have you had any luck? This is actually trickier than it sounds and I am not coming up with anything clever. I think you are going to have to resort to using a cursor -- the cursor will contain a unique set of AreaIDs, and you will loop through it.

What I was able to come up with was this (note I didn't put the cursor creation and looping code in there because I don't know the syntax off of the top of my head -- BOL is our friend):

-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
 tips
SET
 Selected = 2
WHERE
 Selected = 1
-- create a cursor consisting of the unique AreaIDs
-- then loop through cursor, updating Selected for each AreaID
UPDATE
 tips
SET
 Selected = 1
FROM
 tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.cursor_AreaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
 Selected <> 2 AND
 AreaID = @.cursor_AreaID
-- when done looping, make the originally selected IDs unselected
UPDATE
 tips
SET
 Selected = 0
WHERE
 Selected = 2

I am hopeful this could be done without a cursor but it's not coming to me...

Terri|||Terri,

Clever! I didn't think about the setting the 0's to 2 part. I don't know what a cursor is, but I'll poke around and figure it out - give me a day or so. :) The rest make perfect sense, and should help me get it done. Thanks for your help!

Richard|||Since I now know you are still working on this, I still stand by my suggestion, but I can give you code to use instead of using a cursor (which is resource intensive). Same approach, but you would put all of your AreaIDs into a #temp table instead and loop though that:

DECLARE @.areaID int
-- temporarily make the currently selected items = 2 to keep them out of consideration
UPDATE
 tips
SET
 Selected = 2
WHERE
 Selected = 1
-- create a temp table consisting of the unique AreaIDs
CREATE TABLE #Temp (areaID int)
INSERT INTO #Temp SELECT DISTINCT areaID FROM tips
-- get first areaID to process
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
-- loop
WHILE @.areaID IS NOT NULL
BEGIN
-- update Selected for each AreaID
UPDATE
 tips
SET
 Selected = 1
FROM
 tips
INNER JOIN
(SELECT TOP 1 ID FROM tips WHERE areaID = @.areaID AND Selected <> 2 ORDER BY NEWID()) AS SubSelect ON Tips.ID = SubSelect.ID
WHERE
 Selected <> 2 AND
 AreaID = @.AreaID
-- get next AreaID to process
DELETE FROM #temp WHERE areaID = @.areaID
SELECT @.areaID = NULL
SELECT TOP 1 @.areaID = areaID FROM #temp ORDER BY areaID
END
-- when done looping, make the originally selected IDs unselected
UPDATE
 tips
SET
 Selected = 0
WHERE
 Selected = 2
-- drop temporary table
DROP TABLE #TEMP

Still wishing this could be done in one statement...

Terri|||Terri,

Worked perfectly! All I had to do is change the column type from bit to int (couldn't update all the 1's to 2 in a bit field). Thank you!!

RH|||That's good news; thanks for updating us! (And yet another reason why I don't like bit fields ;-)) You should be able to use a tinyint here and save some storage space.

Terro