Sidebar
public:microsoft_sql_database
Table of Contents
Microsoft SQL Database Backup Set
Last modified: 2019/08/26 (Note: Content written for AhsayCBS v7+v8, and may generally apply to latest product release)
AhsayOBM allows you to back up databases in your Microsoft SQL Server with the MS SQL Server Backup Module, which provides you with a set of tools to protect your MS SQL Server, whether in VSS backup mode or ODBC backup mode.
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.
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.
For MS SQL server backup sets which are upgraded from v6, the default backup mode will be ODBC.
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.
(Diagram from Microsoft)
Process
Temporary Folder Requirement
Location for temporary folder
The temporary directory folder is used by AhsayOBM for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
Temporary folder capacity
With VSS-based backup, the disk space of the temporary folder required for storing the VSS image is significantly smaller than using the ODBC spooling backup method. As the extra space is not required to hold the full database.
It is recommended that the temporary directory should have at least free disk space of 50% of the total database size. The rationale behind this recommended free disk space is the default in-file delta ratio settings is 50%, therefore AhsayOBM could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
Pros
Fast and minimal interruption
The database snapshot capture process is fast and can take place on a running server, as you may continue to work when the snapshot capturing is taking place, there may be another process that holds your input in some memory section until the snapshot capture is completed. That said, the whole snapshot capture is fast, so there is no need for you to stop working and it causes minimal interruption to your business operation.
Significantly lesser disk burden
VSS Snapshot typically requires much less additional disk space than clones which is the traditional backup method by spooling database into the temporary folder. Oftentimes, the capacity of the database to back up is huge and therefore the temporary folder would overload with the equal or even larger disk space if traditional backup method is used. By utilizing the VSS technology, it helps your system greatly reduce disk capacity burden and promote optimized performance.
Cons
No Transaction Log Backup
MS SQL does not support transaction log backup when VSS is used, therefore, transaction log backup will have to be done manually.
Workaround is time consuming
In order to truncate the transaction logs, you have to either change the Recovery model to Simple or perform a manual log truncation, which could be time consuming.
Transaction Log Handling
VSS based backup no longer requires backup of the transaction log files, however for databases configured in either full or bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server. https://technet.microsoft.com/en-us/library/cc966520.aspx
To prevent this from occurring, it is recommended to change the recovery model of database selected for backup to simple recovery model. Refer to the following steps for details:
- In SQL Server Management Studio, expand Databases, select a user database, or expand System Databases and select a system database.
- Right-click the corresponding database, then click Properties to open the Database Properties dialog box.
- In the Select a page pane, click Options.
- The current recovery model is displayed in the Recovery model list box. Modify the recovery model by selecting Simple from the model list.
Only modify the recovery model of a live database during low activities hour. It is also recommended to perform a full backup before changing the recovery model.
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).
ODBC Mode
By using the ODBC mode for MS SQL backup, database files are spooled to a temporary directory before being uploaded to the backup destination.
Process
Temporary Folder Requirement
Location for temporary folder
The temporary directory folder is used by AhsayOBM for storing; the database files, incremental/differential delta files, and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows and or database.
Temporary folder capacity
ODBC backup requires a significantly larger disk space of temporary folder as it need to store the database files spooled during the backup process. It is recommended that the temporary directory have disk space of at least 150% of the total database size. For each database backup, AhsayOBM will spool the database files to the temporary directory before they are uploaded to the backup destination. Also, additional space is required for in-file delta generation the default in-file delta ratio settings is 50%, therefore AhsayOBM could generate incremental or differential delta file(s) of up to 50% of the total database size. The actual disk space required depends on various factors, including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
Pros
Support Automated Transaction Logs Backup
Schedule backup of transaction log can be configured so that the transaction logs can be backed up periodically and the transaction logs are truncated automatically after each backup job.
Support Point in Time Recovery
The ability to restore to a point in time for all of your transaction log backups.
Support Backup of High Transaction Databases
For databases which supports a high number of transaction which may require frequent backups. Transaction log backups at regular intervals are more suitable and less resource intensive than VSS based backups, i.e. transaction log backup every 60 minutes, 30 minutes, 15 minutes etc depending on the database transaction volume.
Pros
Large disk space required
Since the database files will be spooled to a temporary folder before uploading to backup destination, investment on hard disk could be high if your MS SQL database size is large.
Slower backup process
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.
Requirements
You are strongly recommended to configure or check all the requirements below before you proceed with the MS SQL server backup and restoration. AhsayOBM supports 2 backup modes when creating a backup set for MS SQL server, VSS mode and ODBC mode.
VSS Backup Mode
The 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.
Hardware Requirement
Refer to the following article for the list of hardware requirements for AhsayOBM: FAQ: Ahsay Hardware Requirement List (HRL) for version 8.1 or above
Software Requirement
Refer to the following article for the list of compatible operating systems and application versions: FAQ: Ahsay Software Compatibility List (SCL) for version 8.1 or above
AhsayOBM Installation
Make sure the latest version of AhsayOBM has been installed on the MS SQL server.
AhsayOBM Add-On Module Configuration
Make sure the Microsoft SQL Server feature has been enabled as an add-on module in your AhsayOBM user account. Contact your backup service provider for more details.
Backup Quota Requirement
Make sure that your AhsayOBM user account has sufficient storage quota assigned to accommodate the storage of MS SQL Server backup set and retention policy.
Continuous Backup Module
The continuous backup add-on module is required if you would like to enable the continuous backup feature.
Java Heap Size
The default Java heap size setting on AhsayOBM is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
User Account Privileges
Make sure the operating system account that performs the backup and restore has sufficient permission to access both SQL server and VSS.
Temporary Directory Folder
- The temporary directory folder is used by AhsayOBM for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder to be set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database.
- It is recommended that the temporary directory folder should have at least free disk space of 50% of the total database size because the default Delta ratio is 50%. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
- The SQL Windows service must have read and write permission to the temporary directory.
SQL Server VSS Writer
Make sure the SqlServerWriter has been installed and running on the SQL server, and the writer state is Stable. This can be verified by running the “vssadmin list writers” command in the Windows Command Prompt.
If you do not find the SqlServerWriter in the result, make sure the SQL Server VSS Writer has been started by following the instructions in Windows Services section below.
Example:
C:\Users\Administrator>vssadmin list writers
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001-2013 Microsoft Corp.
Writer name: 'Task Scheduler Writer'
Writer Id: {d61d61c8-d73a-4eee-8cdd-f6f9786b7124}
Writer Instance Id: {1bddd48e-5052-49db-9b07-b96f96727e6b}
State: [1] Stable
Last error: No error
Writer name: 'VSS Metadata Store Writer'
Writer Id: {75dfb225-e2e4-4d39-9ac9-ffaff65ddf06}
Writer Instance Id: {088e7a7d-09a8-4cc6-a609-ad90e75ddc93}
State: [1] Stable
Last error: No error
Writer name: 'Performance Counters Writer'
Writer Id: {0bada1de-01a9-4625-8278-69e735f39dd2}
Writer Instance Id: {f0086dda-9efc-47c5-8eb6-a944c3d09381}
State: [1] Stable
Last error: No error
Writer name: 'SqlServerWriter'
Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
Writer Instance Id: {3de4f842-4d57-4198-9949-3b3f8c2629dc}
State: [1] Stable
Last error: No error
Writer name: 'System Writer'
Writer Id: {e8132975-6f93-4464-a53e-1050253ae220}
Writer Instance Id: {32d2fccc-624f-4baa-beb3-17b27fcae9ee}
State: [1] Stable
Last error: No error
Writer name: 'ASR Writer'
Writer Id: {be000cbe-11fe-4426-9c58-531aa6355fc4}
Writer Instance Id: {e8580fb0-b51f-40ab-91bf-4eff5107c4d1}
State: [1] Stable
Last error: No error
Writer name: 'WMI Writer'
Writer Id: {a6ad56c2-b509-4e6c-bb19-49d8f43532f0}
Writer Instance Id: {de1b6322-1d96-4f85-adbf-05cb517322ea}
State: [1] Stable
Last error: No error
Writer name: 'BITS Writer'
Writer Id: {4969d978-be47-48b0-b100-f328f07ac1e0}
Writer Instance Id: {a623b49f-a3d4-42d2-af9a-4e924fb31262}
State: [1] Stable
Last error: No error
Writer name: 'Registry Writer'
Writer Id: {afbab4a2-367d-4d15-a586-71dbb18f8485}
Writer Instance Id: {cc6b42f1-ebd0-429f-b3d3-e860905d40d3}
State: [1] Stable
Last error: No error
Writer name: 'Shadow Copy Optimization Writer'
Writer Id: {4dc3bdd4-ab48-4d07-adb0-3bee2926fd7f}
Writer Instance Id: {957ff981-d54f-4a1f-8798-bd9bd76396bd}
State: [1] Stable
Last error: No error
Writer name: 'COM+ REGDB Writer'
Writer Id: {542da469-d3e1-473c-9f4f-7847f01fc64f}
Writer Instance Id: {801fea63-6bfc-406d-9a40-4ad5af484773}
State: [1] Stable
Last error: No error
MS SQL Server Volumes
MS SQL Server volumes must use a file system which supports the use of VSS snapshot, for example NTFS.
Windows Services
Ensure that the following services have been enabled in the Windows Services menu.
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 VSS Writer
2. SQL Server Services
3. Volume Shadow Copy
MS SQL Server Registry
Make sure the MS SQL entry is present in the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL”. To access this path, type “regedit” in the command prompt to launch the Registry Editor.
Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
MS SQL Recovery Model
VSS backup mode does not support backup of transaction log files, but for databases configured in either Full or Bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server. Refer to: https://technet.microsoft.com/en-us/library/cc966520.aspx
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.
ODBC Backup Mode
By using the ODBC mode for MS SQL backup, databases files are spooled to a temporary directory before being uploaded to the backup destination.
Hardware Requirement
Refer to the following article for the list of hardware requirements for AhsayOBM: FAQ: Ahsay Hardware Requirement List (HRL) for version 8.1 or above
Software Requirement
Refer to the following article for the list of compatible operating systems and application versions: FAQ: Ahsay Software Compatibility List (SCL) for version 8.1 or above
AhsayOBM Installation
Make sure the latest version of AhsayOBM has been installed on the MS SQL server.
AhsayOBM Add-On Module Configuration
Make sure the Microsoft SQL Server feature has been enabled as an add-on module in your AhsayOBM user account. Contact your backup service provider for more details.
Backup Quota Requirement
Make sure that your AhsayOBM user account has sufficient storage quota assigned to accommodate the storage of MS SQL Server backup set and retention policy.
Continuous Backup Module
The continuous backup add-on module is required if you would like to enable the continuous backup feature.
Java Heap Size
The default Java heap size setting on AhsayOBM is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
Temporary Directory Folder
- The temporary directory folder is used by AhsayOBM for storing the database files, incremental/differential delta files and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive.
- The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database. If the temporary directory folder is located on a network drive, make sure the login account has sufficient permission to access the network resources.
- Please refer to the following URL for more details:
https://support.microsoft.com/en-us/help/2926557/sql-server-vdi-backup-and-restore-operations-require-sysadmin-privileg
https://technet.microsoft.com/en-us/library/cc966520.aspx
- It is recommended that the temporary directory folder should have at least free disk space of 150% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
- The SQL Windows service must have read and write permission to the temporary directory.
Windows Services
Ensure that the following services have been enabled in the Windows Services menu. 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
2. Volume Shadow Copy
MS SQL Server Registry
Make sure the MS SQL entry is present in the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL”.
To access this path, type “regedit” in the command prompt to launch the Registry Editor.
Note: Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
Maximum Worker Thread
For SQL instance with large number of database (more than 500 databases), consider to increase the “Maximum Worker Thread” setting. Refer to the article below for further details. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option
MS SQL Recovery Model
ODBC backup mode supports transaction log backup for database with Full recovery model.
1. For database with Simple recovery mode, only full database and differential database backups can be performed. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server
2. To perform a transaction log backup, please change the recovery model of corresponding databases from Simple to Full. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server
Considerations for System Databases Backup and Restore
Refer to the following tables for considerations for backup and restoration of system databases.
Backup
SQL server maintains a set of system level database which are essential for the operation of the server instance. Several of the system databases must be backed up after every significant update, they include:
- master
- model
- msdb
- distribution (for SQL database with replication enabled only)
This table summarizes all of the system databases.
| 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. 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. |
| 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. |
Restore
| 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 |
Best Practice and Recommendation
The following are some best practice and recommendation we strongly recommend you to follow before you start any MS SQL Server backup and restore.
- 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.
Limitations
Standalone Environment Only
AhsayOBM does not support backup of MS SQL server in cluster environment, only standalone environment is supported.
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
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.
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.
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.
- Expand the menu tree and select the desired database you would like to back up.
- Right click the database name, then go to Tasks > Back Up. The Back Up Database dialog box shows.
- In the Source section, confirm the database name, then select Transaction Log in the Backup type drop-down menu.
- Select Disk or URL as the destination of the backup, then click Add to select a destination path.
- After selecting the destination path, click OK twice to proceed.
- 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
- Click OK to start the transaction log backup when you are done with all the necessary settings in the Back Up Database dialog box.
Documentation
Issues
public/microsoft_sql_database.txt · Last modified: 2022/11/28 11:06 by kirk.lim