Though the master database can be restored without restoring the service master key, some encrypted information will be unavailable and will need to be recreated. The service master key backup file and its password is needed to restore certain items in the master database like linked server information. Output from the system view sys.master_files is helpful so that you can recreate the volumes.
The volume letters and paths of SQL Server data and log files would be helpful too. Storing the output of the global variable is helpful.
The exact SQL Server version of each instance to recover, so that you can restore system databases and settings. In the case of SQL Servers needing a rebuild from nothing but SQL Server backups, some of the key pieces of information from this checklist will be helpful:ġ. This is a conversation for another blog post but a typical pattern is weekly full backups, nightly differential backups, and in the case of databases not in SIMPLE recovery model, 15 minute transaction log backups. Regardless, in all cases, SQL Server backups of each database should be taken regularly. A whole VM snapshot for example that is reliant on VSS could incur an unacceptable long IO stun duration when it occurs.
You should consider complimentary backup solutions that backup/snapshot the entire server (or VM) for SQL Server, but sometimes these technologies are limited or have too much of an impact on the server. Not hardware failure but ransomware (crypto-locking file) attacks have been the cause.
Recently, by far the most frequent and common disaster recovery scenario for my clients has been the need for a complete, bare-metal rebuild or restore of the master database. Not many disaster recovery or SQL migration/upgrade scenarios require the SQL Server instance service master key to be restored.
0 kommentar(er)
