SQL Server disaster recovery from 31 days to 4th days: Backup

Note:Disaster recovery SeriesOfArticleYesby Robert Davis Written in sqlsoldier,I personally think it is quite good, so according to my own understanding, testing and sorting are not directly translated. If there is any inaccuracy, please correct me.

This article is the fourth article on Database disaster recovery. It mainly discusses the importance of backup and the absence of backup, which is easy to ignore.

System Database
Most people know that the user database needs to be backed up. In fact, the system data also needs to be backed up. They are master, model, MSDB, distribution, and resource databases. Copy data files (mssqlsystemresource) are used for backup. MDF and mssqlsystemresource. LDF) method, copy to your directory where the backup is stored.

Certificate and asymmetric key
If you create a certificate for an asymmetric key, authorization, or security object, you may need to consider using it again. For example, if you need to move an encrypted database to a new server, you may need to decrypt or re-encrypt it. You will need the previous certificate. The backup method is to use the backup certificafe command to export it to a file. If it is a key, you can use the backup maserkey command. In addition, the backup files must be deleted from the server and transferred to a safe place.

SSRs key
If you have lost an SSRs instance, you need to connect to a new database. You need to import the previous key to read sensitive data. If the key is invalid, you can also create a new key, but this will clear all existing sensitive data. A customer once had over 600 subscriptions on the Report Server. They lost subscription-related user information and finally had to find the user information and manually repair the subscription, they also lost the data source connection password, which is better than restoring the subscription.

Therefore, make sure that the exported key is not stored on the source server but in other security areas, such as the source proxy server.

SSAs Database
Using xmla to back up the SSAS database, you can create an SQL job to regularly connect to SSAs to execute xmlaCode.
Example:

 <  Backup Xmlns  = "Http://schemas.microsoft.com/analysisservices/2003/engine"  >      <  Object  >          <  Databaseid  > Adventure works dwx 2012 </  Databaseid  >      </  Object  >      < File  > C: \ Bak \ adventure works DW 2012.abf </  File  >  </  Backup  > 

 <  Restore  Xmlns  = "Http://schemas.microsoft.com/analysisservices/2003/engine"  >      <  File  > C: \ Bak \ adventure works DW 2012.abf </  File  >      <  Databasename  > Adventure works dwx 2012 </  Databasename  >      <  Allowoverwrite  > True </  Allowoverwrite  >     <  Security  > Ignoresecurity </  Security  >      <  Dbstoragelocation  Xmlns  = "Http://schemas.microsoft.com/analysisservices/2008/engine/100/100"  > C: \ Program Files \ Microsoft SQL Server \ msas11.mssqlserver \ OLAP \ data \ </  Dbstoragelocation  > </  Restore  > 

 

Summary:
This article focuses on backing up the user database, as well as the system database, certificate, master key, SSRs key, and SSAs database. These are potential problems of data loss, therefore, backup is the most common problem that is caused by power failure. If such backup is available, recovery will become easier.

SQL Server disaster recovery: 31-5th days: deal with damaged non-clustered Indexes