SQL Server Performance Forum – Threads Archive
Fastest way to restore 46 databases?
I am working on a DR plan for a client and one of the tasks is to restore the SQL 2000 server which has 46 databases. My initial plan was to use a script to resotre the backups – which works fine. However there is the matter of the transaction logs that also need restoring. Bearing in mind that the server is not the same hardware and log-shipping is out of the question (for now) I am wondering how best to achieve this mammoth task… Of course hopefully I can re-attach the databases from the tape backup but I still need to do diff and trans log restores on all these databases…. Any idea?
You could create a custom backup procedure that use a timestamp in the name and a standard naming scheme for full, diff and transaction log backups. Store these timestamps (and other related info like dbname, path, date etc) in a database or textfile. Each time a backup is run store this data and copy it to another server as well. In case of disaster import the text file in a database and build dynamic restore scripts by querying this data. For example a script that generates a restore command for the latest full backup of a database, the latest diff backup and the transaction log backups that follow.
We have everything stored in a backup setup table. The backups then write to a backup history table. To restore, we just need to look at the backup history table and run them in order. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I use a couple of fairly simple procs which can
– restore the latest backup file in a directory
– restore all logs since that latest backup file Then on our production servers I simply set up db and/or log backups to a directory structure, which the stored procs restore to a ‘standby’ server Cheers
Twan
And try to deploy log shipping as soon as possible for easy maintenance of warm standby on these databases and till then plan as per Argyle’s note. 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.
]]>