Differences

This shows you the differences between two versions of the page.

--- public:microsoft_sql_database [2019/08/19 14:56]
ronnie.chan [Considerations for System Databases]
+++ public:microsoft_sql_database [2019/08/19 15:11]
ronnie.chan
@@ Line 173: / Line 173: @@
   - Launch SQL Server Management Studio in Windows.
-  - Select the SQL server you would like to connect to, and the corresponding authentication method, then click Connect to proceed. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_13.png?800}}
+  - Select the SQL server you would like to connect to, and the corresponding authentication method, then click Connect to proceed. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_13.png?640}}
-  - Expand the menu tree and select the desired database you would like to back up. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_14.png?800}}
+  - Expand the menu tree and select the desired database you would like to back up. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_14.png?640}}
-  - Right click the database name, then go to Tasks > Back Up. The Back Up Database dialog box shows. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_15.png?800}}
+  - Right click the database name, then go to Tasks > Back Up. The Back Up Database dialog box shows. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_15.png?640}}
-  - In the Source section, confirm the database name, then select Transaction Log in the Backup type drop-down menu. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_16.png?800}}
+  - In the Source section, confirm the database name, then select Transaction Log in the Backup type drop-down menu. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_16.png?640}}
-  - Select Disk or URL as the destination of the backup, then click Add to select a destination path. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_17.png?800}}
+  - Select Disk or URL as the destination of the backup, then click Add to select a destination path. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_17.png?640}}
-  - After selecting the destination path, click OK twice to proceed. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_18.png?800}}
+  - After selecting the destination path, click OK twice to proceed. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_18.png?640}}
-  - Go to the Backup Options, then in the Backup set section, name the backup set and enter a description of the backup set if needed. \\ \\ Configure the Backup set to expire after a specified number of day or on a specified date. Set to 0 day if you do not want the backup set to expire \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_19.png?800}}
+  - Go to the Backup Options, then in the Backup set section, name the backup set and enter a description of the backup set if needed. \\ \\ Configure the Backup set to expire after a specified number of day or on a specified date. Set to 0 day if you do not want the backup set to expire \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_19.png?640}}
-  - Click OK to start the transaction log backup when you are done with all the necessary settings in the Back Up Database dialog box. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_20.png?800}}
+  - Click OK to start the transaction log backup when you are done with all the necessary settings in the Back Up Database dialog box. \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_20.png?640}}
 </WRAP>
 </WRAP>
@@ Line 269: / Line 269: @@
 ^ System      ^ Description       ^ Backup          ^ Suggestion          ^
-| master    | The database that records all of the system level information of a SQL server system.     | Yes        | To back up any database, the instance of SQL server must be running.
+^ master    | The database that records all of the system level information of a SQL server system.     | Yes        | To back up any database, the instance of SQL server must be running. \\ \\ Startup of an instance of SQL server requires that the master database is accessible and at least partly usable. \\ \\ Back up the master database as often as necessary to protect the data sufficiently for your business needs.  \\ \\ Microsoft recommends a regular backup schedule, which you can supplement with manual backup after any substantial update.  |
-Startup of an instance of SQL server requires that the master database is accessible and at least partly usable.
+^ model    | The template for all databases that are created on the instance of SQL server.     | Yes        | Backup the model database only when necessary, for example, after customizing its database options.  \\ \\ Microsoft recommends that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.        |
+^ msdb    | The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. It also contains history tables (e.g. backup / restore history table).      | Yes        | Back up the msdb whenever it is updated.        |
+^ tempdb    | A workspace for holding temporary or intermediate result sets. This database is recreated every time an instance of SQL server is started.      | No        | The tempdb system database cannot be backed up.        |
+^ distribution    | The distribution database exists only if the server is configured as a replication distributor.  \\ \\ It stores metadata and history data for all types of replication, and transactions for transactional replication.      | Yes        | Replicated databases and their associated system databases should be backed up regularly.        |
-Back up the master database as often as necessary to protect the
+==== Restore ====
-data sufficiently for your business needs.
+^ Heading 1      ^ Heading 2       ^
+^ master    | To restore any database, the instance of SQL server must be running. Startup of an instance of SQL server requires that the master database is accessible and at least partly usable.  \\ \\ Restore or rebuild the master database completely if master becomes unusable.     |
+^ model    | Restore the model database if: \\  * The master database has been rebuilt. \\  * The model database has been damaged, for example due to media failure. \\  * The model database has been modified, in this case, it is necessary to restore model from a backup when you rebuild master, because the Rebuild Master utility deletes and recreates model.     |
+^ msdb    | Restore the msdb database if the master database has been rebuilt.     |
+^ distribution     | For restore strategies of distribution database, please refer to the following online document from Microsoft for more details: [[http://msdn.microsoft.com/enus/library/ms152560.aspx]]    |
-Microsoft recommends a regular backup schedule, which you can
+===== Best Practice and Recommendation =====
-supplement with manual backup after any substantial update.        |
+The following are some best practice and recommendation we strongly recommend you to follow before you start any MS SQL Server backup and restore.
-| model    | The template for all databases that are created on the instance of SQL server.     | Yes        | Backup the model database only when necessary, for example, after customizing its database options.
+  - For VSS backup mode, it is suggested to set the backup schedule to a time when system activity is low to achieve the best possible performance. \\ \\
+  - It is recommended to use ODBC backup mode for backup of database with a high volume of transaction, since such setup may require frequent backups. Transaction log backup (which is only supported by ODBC backup mode) can be performed periodically, and is less resource intensive than VSS based backup. \\ \\
+  - For maximum data protection and restore options, it is recommended to configure:
+    - At least one offsite or cloud destination
+    - At least one local destination for fast recovery \\ \\
+  - Perform test restores periodically to ensure your backup is set up and performed properly. Performing recovery test can also help identify potential issues or gaps in your recovery plan. It is important that you do not try to make the test easier, as the objective of a successful test is not to demonstrate that everything is flawless. There might be flaws identified in the plan throughout the test and it is important to identify those flaws. \\ \\
+  - The Restore Raw File option is for advanced MS SQL Server administrator and should only be used if you have in-depth knowledge and understanding of your MS SQL Server, otherwise, it is not recommended to use this option as there are additional MS SQL techniques required to perform the manual restore.
-Microsoft recommends that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.        |
+----
-| msdb    | The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and for recording operators. It also contains history tables (e.g. backup / restore history table).      | Yes        | Back up the msdb whenever it is updated.        |
-| tempdb    | A workspace for holding temporary or intermediate result sets. This database is recreated every time an instance of SQL server is started.      | No        | The tempdb system database cannot be backed up.        |
-| distribution    | The distribution database exists only if the server is configured as a replication distributor.
-It stores metadata and history data for all types of replication, and transactions for transactional replication.      | Yes        | Replicated databases and their associated system databases should be backed up regularly.        |
+===== Limitations =====
-==== Restore ====
+==== Standalone Environment Only ====
-^ Heading 1      ^ Heading 2       ^
+AhsayOBM does not support backup of MS SQL server in cluster environment, only standalone environment is supported.
-| master    | To restore any database, the instance of SQL server must be running. Startup of an instance of SQL server requires that the master database is accessible and at least partly usable.
+==== VSS Backup Mode ====
+  - Only support backup of database on local drive.  Database on network drive is not supported. \\ \\
+  - VSS backup mode does not support transaction log backup, therefore, transaction log backup will have to be done manually. Or you can choose ODBC backup mode for transaction log backup. \\ \\
+  - In order to truncate transaction logs, you have to perform a manual log truncation, which could be time consuming.
+==== File System for Database Snapshot ====
+You cannot create database snapshots on FAT32 file system or RAW partitions. The sparse files used by database snapshots are provided by the NTFS file system.
+==== SQL Server Version ====
+<WRAP indent>
+=== Automated Restore Option ====
+If you have chosen the automated restoration to the Original SQL server or Alternate SQL server of your selection, the restoration can only be done in a SQL server version that is the same as the one used for performing the backup.
+=== Manual Raw-file Restore Option ===
+If you have chosen to restore the raw file, the raw database file(s) can be manually restored to the same or newer SQL server version that you used to perform the backup.
+</WRAP>
+==== Restoration to Other SQL Server ====
+  - If you would like to restore database to an alternate SQL server, you can only choose to restore one database to restore at a time.\\ \\
+  - If you would like to restore database to an alternate SQL server, make sure you choose to restore raw file by enabling the checkbox Restore raw file.
+----
+===== Backup Modes =====
+You can choose from one of the two backup modes when creating a backup set for MS SQL server. The information below provides you with more details on each backup mode.
+<code>
+NOTE
+For MS SQL server backup sets which are upgraded from v6, the default backup mode will be ODBC.
+</code>
+==== VSS Mode ====
+VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.
-Restore or rebuild the master database completely if master becomes unusable.     |
+{{public:ahsay_wiki_module_microsoft_sql_requirement_8.png?800}}
-| model    | Restore the model database if:
-  * The master database has been rebuilt.
-  * The model database has been damaged, for example due to media failure.
-  * The model database has been modified, in this case, it is necessary to restore model from a backup when you rebuild master, because the Rebuild Master utility deletes and recreates model.     |
-| msdb    | Restore the msdb database if the master database has been rebuilt.     |
-| distribution     | For restore strategies of distribution database, please refer to the following online document from Microsoft for more
-details: [[http://msdn.microsoft.com/enus/library/ms152560.aspx]]    |
 ===== Documentation =====

User Tools

Site Tools

Differences

Page Tools