|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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 You can create a SQL script and then schedule this to run every Sunday.> 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. > > 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 Steen,
Do you have any idea where can I find samples of those scripts - disconnect user and restore ? Thanks Show quote "Steen Persson (DK)" <s**@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 - Below is an example (SQL 2000). See the Books Online for syntax details.> disconnect user and restore ? 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 -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Robert" <Rob***@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)" <s**@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. Show quote "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" <Rob***@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)" <s**@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 Did you also include the 'WITH ROLLBACK IMMEDIATE' option? That should kill > fails. Is it possible to disconnect users connected to the Testing > Database ? 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 You can include the delete command (DEL > like to include it in Step 3 ? "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. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Robert" <Rob***@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" <Rob***@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)" <s**@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. Show quote "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" <Rob***@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" <Rob***@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)" <s**@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 >>>> >>>> >>> >>> >> >> > > 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 Show quote "Robert" <Rob***@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. >  |
|||||||||||||||||||||||
Other interesting topics