SQL Server Performance Forum – Threads Archive

automate the restore

How do I automate the backup of 200 databases in a server and automate the restore all of them on destination server running on differnt machine.

Well, it depends on how complicated you want to get with this. Tara Duggan has some good scripts athttp://weblogs.sqlteam.com/tarad . You could just schedule them on both sides, using a table to record backup success so you know when to run the restores on the other server. You could also use log shipping if you have Enterprise Edition. Is the number of databases highly variable; and are you trying to account for that fact? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Well my problem state ment is like this:i need a scrpt or some job which will take back up of all the databases and transaction logs one by onre from the source server and dump it to a suitable location.After that on the destination machine a sp/script/job will be scheduled which will restore the databases one by one on the destination server.Can any one pls help me with the sp/script/job which can do this..i ll be really gr8ful forever

You can take advantage of SQL Server 2000 log shipping in this case where the Transaction log are restored from the primary server to the secondary server depending upon the scheduled intervals. Take help ofhttp://www.sql-server-performance.com/sql_server_log_shipping.asp link about the log shipping. If not you can search under this website for tips and tricks of maintaining the backup/restore operations and also books online will help you to get about the syntax. And alsohttp://vyaskn.tripod.com/maintaining_standby_sql_server.htm about the standby servers information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>