public:microsoft_sql_database
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
|
public:microsoft_sql_database [2019/08/19 15:23] ronnie.chan |
public:microsoft_sql_database [2019/08/19 15:33] ronnie.chan [VSS Mode] |
||
|---|---|---|---|
| Line 4: | Line 4: | ||
| {{public:ahsay_wiki_microsoft_sql_database_backup_set.png?640}} | {{public:ahsay_wiki_microsoft_sql_database_backup_set.png?640}} | ||
| + | |||
| + | ---- | ||
| ===== System Architecture ===== | ===== System Architecture ===== | ||
| Below is the system architecture diagram illustrating the major elements involved in the backup process among the MS SQL server, AhsayOBM and AhsayCBS. | Below is the system architecture diagram illustrating the major elements involved in the backup process among the MS SQL server, AhsayOBM and AhsayCBS. | ||
| + | |||
| + | {{public:ahsay_wiki_module_microsoft_sql_requirement_1.png?800}} | ||
| ---- | ---- | ||
| Line 135: | Line 139: | ||
| 1.SQL Server VSS Writer | 1.SQL Server VSS Writer | ||
| - | {{public:ahsay_wiki_module_microsoft_sql_requirement_1.png?800}} | + | {{public:ahsay_wiki_module_microsoft_sql_requirement_2.png?800}} |
| 2. SQL Server Services | 2. SQL Server Services | ||
| - | {{public:ahsay_wiki_module_microsoft_sql_requirement_2.png?800}} | + | {{public:ahsay_wiki_module_microsoft_sql_requirement_3.png?800}} |
| 3. Volume Shadow Copy | 3. Volume Shadow Copy | ||
| - | {{public:ahsay_wiki_module_microsoft_sql_requirement_3.png?800}} | + | {{public:ahsay_wiki_module_microsoft_sql_requirement_4.png?800}} |
| </WRAP> | </WRAP> | ||
| Line 150: | Line 154: | ||
| To access this path, type “regedit” in the command prompt to launch the Registry Editor. | To access this path, type “regedit” in the command prompt to launch the Registry Editor. | ||
| - | {{public:ahsay_wiki_module_microsoft_sql_requirement_4.png?800}} | + | {{public:ahsay_wiki_module_microsoft_sql_requirement_5.png?800}} |
| <code> | <code> | ||
| Line 162: | Line 166: | ||
| To prevent this from occurring, you can modify the recovery model of database selected for backup to Simple. | To prevent this from occurring, you can modify the recovery model of database selected for backup to Simple. | ||
| - | Alternatively, to truncate the transaction log files, you can perform a transaction log backup manually, or create an additional MS SQL database backup set in ODBC backup mode to perform a transaction log backup. | + | Alternatively, to [[#truncating_transaction_log|truncate the transaction log files]], you can perform a transaction log backup manually, or create an additional MS SQL database backup set in ODBC backup mode to perform a transaction log backup. |
| - | <WRAP indent> | ||
| - | == Truncating Transaction Log == | ||
| - | The instructions below only apply for database with full recovery model. | ||
| - | |||
| - | Since AhsayOBM v8 utilizes VSS-based backup, which does not support log backup ([[https://technet.microsoft.com/en-us/library/cc966520.aspx]]), transaction log of database in full / bulk-logging recovery model may eventually fill up all disk space available on the volume. | ||
| - | |||
| - | Below are steps to perform a log backup in the SQL Server Management Studio. For further details on this topic, refer to this URL: [[https://msdn.microsoft.com/en-us/library/ms179478.aspx]] | ||
| - | |||
| - | - 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?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?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?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?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?640}} | ||
| - | - 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?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?640}} | ||
| - | </WRAP> | ||
| </WRAP> | </WRAP> | ||
| Line 221: | Line 207: | ||
| Launch Services in Windows by clicking Start then typing “Services” in the search box. All MS SQL server related services should be started by default, in case if it is not, turn it on by right clicking the item then selecting Start. | Launch Services in Windows by clicking Start then typing “Services” in the search box. All MS SQL server related services should be started by default, in case if it is not, turn it on by right clicking the item then selecting Start. | ||
| - | 1. SQL Server Services \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_5.png?800}} | + | 1. SQL Server Services \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_6.png?800}} |
| - | 2. Volume Shadow Copy \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_6.png?800}} | + | 2. Volume Shadow Copy \\ {{public:ahsay_wiki_module_microsoft_sql_requirement_7.png?800}} |
| === MS SQL Server Registry === | === MS SQL Server Registry === | ||
| Line 231: | Line 217: | ||
| To access this path, type “regedit” in the command prompt to launch the Registry Editor. | To access this path, type “regedit” in the command prompt to launch the Registry Editor. | ||
| - | {{public:ahsay_wiki_module_microsoft_sql_requirement_7.png?800}} | + | {{public:ahsay_wiki_module_microsoft_sql_requirement_8.png?800}} |
| <WRAP prewrap> | <WRAP prewrap> | ||
| Line 281: | Line 267: | ||
| ^ msdb | Restore the msdb database if the master database has been rebuilt. | | ^ 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]] | | ^ 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]] | | ||
| + | |||
| + | ---- | ||
| ===== Best Practice and Recommendation ===== | ===== Best Practice and Recommendation ===== | ||
| Line 337: | Line 325: | ||
| <WRAP indent> | <WRAP indent> | ||
| + | |||
| + | === Process === | ||
| + | |||
| + | {{public:ahsay_wiki_module_microsoft_sql_requirement_11.png?640}} | ||
| + | |||
| === Temporary Folder Requirement === | === Temporary Folder Requirement === | ||
| <WRAP indent> | <WRAP indent> | ||
| Line 384: | Line 377: | ||
| </WRAP> | </WRAP> | ||
| - | For MS SQL Server setups where you cannot modify the recovery model of the database, please refer to here for details on how to truncate transaction log (e.g. perform a transaction log backup manually). | + | For MS SQL Server setups where you cannot modify the recovery model of the database, please refer to [[#truncating_transaction_log|here]] for details on how to truncate transaction log (e.g. perform a transaction log backup manually). |
| </WRAP> | </WRAP> | ||
| Line 393: | Line 386: | ||
| <WRAP indent> | <WRAP indent> | ||
| + | |||
| + | === Process === | ||
| + | |||
| + | {{public:ahsay_wiki_module_microsoft_sql_requirement_12.png?640}} | ||
| + | |||
| === Temporary Folder Requirement === | === Temporary Folder Requirement === | ||
| Line 424: | Line 422: | ||
| By utilizing the conventional spooling method, it could take a long time to back up the database and the speed is subject to various factors, including database size, network transfer speed, backup frequency, etc. | By utilizing the conventional spooling method, it could take a long time to back up the database and the speed is subject to various factors, including database size, network transfer speed, backup frequency, etc. | ||
| </WRAP> | </WRAP> | ||
| + | </WRAP> | ||
| + | |||
| + | ---- | ||
| + | |||
| + | ===== Truncating Transaction Log ===== | ||
| + | The instructions below only apply for database with full recovery model. | ||
| + | |||
| + | Since AhsayOBM v8 utilizes VSS-based backup, which does not support log backup ([[https://technet.microsoft.com/en-us/library/cc966520.aspx]]), transaction log of database in full / bulk-logging recovery model may eventually fill up all disk space available on the volume. | ||
| + | |||
| + | Below are steps to perform a log backup in the SQL Server Management Studio. For further details on this topic, refer to this URL: [[https://msdn.microsoft.com/en-us/library/ms179478.aspx]] | ||
| + | |||
| + | - 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?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?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?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?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?640}} | ||
| + | - 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?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?640}} | ||
| </WRAP> | </WRAP> | ||
public/microsoft_sql_database.txt · Last modified: 2022/11/28 11:06 by kirk.lim