Reduce DR restore time -- importance of msdb
colby
Posts: 30
Need you help
I am trying to make best use of Redgate SQL back v5 in a DR situation.
I sssume Redgate SQL Backup v5 uses msdb to know the history of each backup. There are 2 options Redgate uses to restore backups 1) review existing history in msdb or 2) the user can manually select backups from a backup folder. My goal is to develop a process to minimize restore time on a new replacement DR SQL server. We have fulls once a week, differential once a day and transactional once an hour. So worst case the restore will need 1 full, 1 differential, 23 transactionals. How does one keep track of each backup file? I my change this backup model depending on what I hear in this forum.
If one uses the review existing history option then all backup information Redgate needs is in msdb. So the most recent restore from the time of the last transactional log is needed so when you restore msdb the names of the transactional log backup are in your restored msdb. If not then you will need to restore via option 1 (some backups names are in msdb) and option 2 (some backup names are not in your restored msdb). This takes more time.
Plan A -- To reduce restore time should one backup msdb each hour and restore all of msdb and use the latest msdb in the DR restore of application database?
Plan B -- if Redgate can not use msdb in a DR, then I will need to restore backup files manually using option 2. 1) Are there Redgate backup file naming conventions that will reduce the number of files to 1 where the fulls, differentials and transactional are all in 1 file? Currently each backup creates a unique file name so at the end of the week then are some 145 backup files (1 full, 6 differentials and 138 transactional) for each database. 2) If there is a naming convention and all backups are in 1 file, can we restore to any point in time?
Any thoughts?
Thanks
Colby
I am trying to make best use of Redgate SQL back v5 in a DR situation.
I sssume Redgate SQL Backup v5 uses msdb to know the history of each backup. There are 2 options Redgate uses to restore backups 1) review existing history in msdb or 2) the user can manually select backups from a backup folder. My goal is to develop a process to minimize restore time on a new replacement DR SQL server. We have fulls once a week, differential once a day and transactional once an hour. So worst case the restore will need 1 full, 1 differential, 23 transactionals. How does one keep track of each backup file? I my change this backup model depending on what I hear in this forum.
If one uses the review existing history option then all backup information Redgate needs is in msdb. So the most recent restore from the time of the last transactional log is needed so when you restore msdb the names of the transactional log backup are in your restored msdb. If not then you will need to restore via option 1 (some backups names are in msdb) and option 2 (some backup names are not in your restored msdb). This takes more time.
Plan A -- To reduce restore time should one backup msdb each hour and restore all of msdb and use the latest msdb in the DR restore of application database?
Plan B -- if Redgate can not use msdb in a DR, then I will need to restore backup files manually using option 2. 1) Are there Redgate backup file naming conventions that will reduce the number of files to 1 where the fulls, differentials and transactional are all in 1 file? Currently each backup creates a unique file name so at the end of the week then are some 145 backup files (1 full, 6 differentials and 138 transactional) for each database. 2) If there is a naming convention and all backups are in 1 file, can we restore to any point in time?
Any thoughts?
Thanks
Colby
Comments
SQL Backup can potentially make your DR restored easier because it can work out all of the files it needs in order to fully recover a database, satrting with the latest full backup and including all diffs and trans.
This functionality is implemented through the UI however, and if you wanted to automate a DR script, you would need to query MSDB and work out which restores need to be done and in what order.
SQL Backup virtual devices are limited to one backup per file, so potentially you would need to do at least two restore operations. First, work out the latest full backup and restore that with no recovery. Then work out whether you need to apply the latest differential. Finally, see if you need to restore any log files. Once everything is restored, run RESTORE DATABASE [x] WITH RECOVERY.
I've been asked about recovering SQL Servers a multitude of times and the reason why I am hesitant to publish information about automatic recovery is that there are so many scenarios. Recovering the system databases and applying all of the rest of the databases is one thing. If any of the database files are missing, it's another. If master, model or msdb are corrupt, you may need to run rebuildm.exe, and when that doesn't work, SQL Server needs to be installed manually anyway.
You also need to think, as you have pointed out, that the backup history in MSDB is not 100% up-to-date. If that's the case, then it's impossible to say that your databases will get fully recovered automatically.
The most successful strategy I could come up with is to recover master,model, and msdb, then use msdb to locate the last full backup, diff backup, and log backup. Assuming you do full backups less frequently, the latest full is likely to be recorded in MSDB. I feel the same way about the differentials. But because log backups are normally mere minutes apart, I get the file location of the latest log backup of the database and use SQL Backup's wildcard support to restore all of the logs in that particular folder to minimize the chances of missing any. The only way I can see this going wrong is if the log backup folder changes at some point.
I hope that this give you some idea about how to plan your DR strategy.