April 17, 2008 at 1:51 pm
I have 2 database on same server called
Sales and
Sales_readonly.
I am doing daily backup for Sales.
Now I need to Restore this Sales backup to Sales_readonly on daily baisis. Do anyone has scripts to do this via Job.
Thank
Nita
April 17, 2008 at 3:33 pm
Assuming Sales database has one mdf with name is sales_data and one ldf with name sales_log
use following command pattern to accomplish your requirement:
restore database Sales_Readonly from disk='specify bak file name with location'
with move 'sales_data' to 'type location\sales_data.mdf',
move 'sales_log' to 'type location\sales_log.ldf'
Once you have customized above query put it into job. You also need to write kill process to ensure no user is accessing Sales_Readonly when you are restoring it.
hope you have kill process command.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 18, 2008 at 6:23 am
Kill Process how do I know which login ID I need to KILL before resotoring automatically through job.
April 18, 2008 at 6:29 am
Use sp_who2. It's an undocumented stored procedure similar to sp_who, but it provides more information.
John
April 18, 2008 at 6:35 am
How should I use in my code...if I am doing automated Restoration.
Like Kill the processes used by Restoration DB and then restore it
April 18, 2008 at 6:51 am
Personally I'm not a friend of the various Kill process scripts you can find on the web.
I prefer to set the database in single user mode before starting the restore. Any existing connections can be rolled back.
ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE MyDB FROM ....
[font="Verdana"]Markus Bohse[/font]
April 18, 2008 at 6:53 am
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply